Google Sheet - Copy Rows from Multiple Sheets into a Single Summary Sheet

Question

I'm trying to maintain separate sheets that aggregate into a single summary sheet in one google spreadsheet while maintaining formatting. I have a code that will copy the contents of the separate sheets into the summary, but the second sheet overwrites the first in the summary sheet. Here's what I have -

function importTable2() {
  // Source 1
  var sourceSheet1 = SpreadsheetApp.openById("ID").getSheetByName("1");
  var sourceRange1 = sourceSheet1.getDataRange();

  // Source 2
  var sourceSheet2 = SpreadsheetApp.openById("ID").getSheetByName("2");
  var sourceRange2 = sourceSheet2.getDataRange();  

  // Destination
  var destSS = SpreadsheetApp.openById("ID");
  var destSheet = destSS.getSheetByName("Summary");
  var destRange = destSheet.getDataRange();
  destSheet.clear();

  // Copy 1
  var copiedsheet = sourceRange1.getSheet().copyTo(destSS);
  copiedsheet.getRange(sourceRange1.getA1Notation()).copyTo(destRange);
  copiedsheet.getRange(sourceRange1.getA1Notation()).copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
  destSS.deleteSheet(copiedsheet);
  
    // Copy 2
  var copiedsheet2 = sourceRange2.getSheet().copyTo(destSS);
  copiedsheet2.getRange(sourceRange2.getA1Notation()).copyTo(destRange);
  copiedsheet2.getRange(sourceRange2.getA1Notation()).copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
  destSS.deleteSheet(copiedsheet2);

}

I think it's because my destination range is the same, but I can't seem to figure out how to get a second destination range (for sheet 2's data) that will be right below the initial destination range (from sheet 1's data) as the data from sheet 1 could have 10 rows today but 20 rows tomorrow.

Any thoughts on how to get source 2 to drop into the destination sheet under source 1 data?

You can accumulate the each range into an array [][] and give the array to destination sheet — user11982798

ADW ADW · Accepted Answer · 2020-01-10T05:25:43

Try resetting the value of destRange in the "// Copy 2" part of the script:

    // Copy 2
  var copiedsheet2 = sourceSheet2.copyTo(destSS);
  destRange = destSheet.getRange(destSheet.getLastRow() + 1, 1, sourceRange2.getLastRow(), sourceRange2.getLastColumn());
  copiedsheet2.getRange(sourceRange2.getA1Notation()).copyTo(destRange);
  copiedsheet2.getRange(sourceRange2.getA1Notation()).copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
  destSS.deleteSheet(copiedsheet2);

Google Sheet - Copy Rows from Multiple Sheets into a Single Summary Sheet

1 Answers