google-sheetsgoogle-apps-scripturlfetch

Copy certain cells over to another sheet when the script is running


I have the below script. It works perfectly. This script runs a sales agreement for the sale of something. When the sales advisor clicks the email icon and sends this to the customer. I would like certain cells (E.G A9, B21, C6 and D11) to appear in a row on a separate sheet. In this instance I would like it to pull the deposit amount the customer has paid and how they paid it. I have attached an example of the sales agreement and copy of the current script. I have seen multiple ways of doing it through 'on edit' but would like for it to run within this script if at all possible...? The other sheet is called 'payments taken' so ideally 4 cells on 1 row will appear on a new line of 'payments taken' when the script is run and creating more rows every time a sales agreement is run. Any assistance would be amazing.

https://script.google.com/u/0/home/projects/1JOK_EmJWS87365jg8Zn5p19EG5Z6NqC65uwoWx4_54Czra4CONxhvI0N/edit

https://docs.google.com/spreadsheets/d/1LmNntSNCDJYcVIu2SB9zCcdgDq4uyFsAc0bIOkmqRY4/edit?gid=1602154006#gid=1602154006

function Email220() {

var ssID = SpreadsheetApp.getActiveSpreadsheet().getId; 
  
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - SmithJohn"); 
  
  var fileName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - SmithJohn").getRange("A19").getDisplayValue();
  
  
  var folder = DriveApp.getFoldersByName("Sales Agreements").next(); 
  
  var Blob = sheetName.getParent().getBlob().getAs('application/pdf'); 
  
  folder.createFile(Blob).setName(fileName); 

var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

//var email = Session.getUser().getEmail();

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - SmithJohn").getRange("F6");

var emailAddress = emailRange.getValue();

var subject = "S/A - SmithJohn";

var body = ("Thank you for your business.");

var backgroundArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - SmithJohn").getRange("A1:K").getBackgrounds();
  var checkForYellow = backgroundArray.filter(x => x.includes('#ffff00')).length;
  if (checkForYellow) {
    console.log("Script will not run due to cells in yellow.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Script will not run due to cells in yellow."); //toast will be visible in sheets ui
  }
  else {
    console.log("Running the script.");
    SpreadsheetApp.getActiveSpreadsheet().toast("Running script."); //toast will be visible in sheets ui

var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
var shID = getSheetID("S/A - SmithJohn") //Get Sheet ID of sheet name "Master"
var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID+"&gid="+shID;

var result = UrlFetchApp.fetch(url , requestData);  
var contents = result.getContent();

var bcc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - SmithJohn").getRange("F6").getDisplayValues().flat().join(",");var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - SmithJohn").getRange("A19").getDisplayValue();
MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)], bcc });

var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();

  var email_ID1 = "vikci@smith.com";
 
  var subject = "Hello Sales Agreement.";

  var body = ("Attached is your Sales Agreement. \n \Thank you for your business");


  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
  var shID = getSheetID("Xero Invoice - Johns") //Get Sheet ID of sheet name "Xero Invoice - Johns"
  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=csv&id="+ssID+"&gid="+shID;

  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  MailApp.sendEmail (email_ID1, subject ,body, {attachments:[{fileName:sheetName+".csv", content:contents, mimeType:"application//csv"}]});
  }
};
function getSheetID(name){
var ss = SpreadsheetApp.getActive().getSheetByName(name)
var sheetID = ss.getSheetId().toString() 
return sheetID
}

Solution

  • Copy cells to different sheet

    In order to meet your requirements, I have crafted a solution that may help you get in the right direction. Please note that this has a limitation and there are faster ways to get this done, but since you have mentioned that the values may still vary and still not final (Comment via Staging Ground). I based my solution on the header of your current destination sheet, and used this solution for you to easily modify the right ranges for your specifications.

    Added code:

    function copyData() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sourceSheet = ss.getSheetByName("S/A - Vicki");
     var destSheet = ss.getSheetByName("Payments Taken");
    
    
     var dateTaken = sourceSheet.getRange("G39").getValue();
     var paymentMethod = sourceSheet.getRange("H39").getValue();
     var salesAdvisor = sourceSheet.getRange("F4").getValue();
     var customerName = sourceSheet.getRange("C11").getValue();
     var stockNumber = sourceSheet.getRange("A21").getValue();
     var model = sourceSheet.getRange("F21").getValue();
     var year = sourceSheet.getRange("C21").getValue();
     var location = sourceSheet.getRange("D24").getValue();
    
    
     destSheet.appendRow([dateTaken, paymentMethod, salesAdvisor, customerName, stockNumber, model, year, location]);
    }
    

    Important: Please input the line copyData() inside your dedicated function (E.g Email220()) where you want it to be called and run.

    Sample Implementation:

    function Email220() {
      copyData(); ....
    

    References: