google-apps-script

Google Sheet pull and appending data


I am trying to get a script made that will pull data from one google sheet to another and append it to the bottom of a second in the correct columns. I am having issues because the data on the source sheet is not in a single column or row range.

my current script is:

    function appendLPSales() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    
    // Collect the data
    const sourceSheet = ss.getSheetByName("Source Data");
    const sourceRange = sourceSheet.getRange(C4,C5,E4,E5,C6,E6,C7,E7,C8,C9,E9,D11,D13);
    const sourceVals = sourceRange.getValues()
    
    // Append the data.
    // Input order: Date,Customer/Site,# of Tanks,Tank Size/LBS,Amount Paid,Start Reading,End Reading,Gallons,Values Match,Discrepancy note,Filled by,Cashier,Delivery
    const destinationsheet = ss.getSheetByName("Destination");
    destinationsheet.appendRow(sourceVals)
    
    sourceRange.clearContent();
    
    };

Solution

  • Use a RangeList, like this:

    function appendLPSales() {
      const ss = SpreadsheetApp.getActive();
      const sourceRangeList = ss.getSheetByName('Source Data')
        .getRangeList(['C4', 'C5', 'E4', 'E5', 'C6', 'E6', 'C7', 'E7', 'C8', 'C9', 'E9', 'D11', 'D13']);
      const values = sourceRangeList.getRanges().map(range => range.getValue());
      ss.getSheetByName('Destination').appendRow(values);
      sourceRangeList.clearContent();
    }
    

    See RangeList.