Google Sheets Script to append values to another sheet

Question

I have the script below that gets me very close. I am hoping to copy "values" from "Sheet1 A1:AH5" to "Sheet5" adding 5 rows each time (and continue to append 5 more rows at the bottom of Sheet5 each time the script is triggered).

Any help is greatly appreciated.

(The script below pastes to same sheet and only copies the bottom of the first sheet instead of a range)

function copyRows(numRows = 5) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getActiveSheet();
  const lRow = sheet.getLastRow();
  const lCol = sheet.getLastColumn();
  const maxRow = sheet.getMaxRows();
  if (lRow === maxRow) sheet.appendRow(['']);
  sheet.getRange(lRow - numRows + 1, 1, numRows, lCol)
    .copyTo(sheet.getRange(lRow + 1, 1));
}

Answer 1

I believe your goal is as follows.

• You want to copy the range "A1:AH5" of "Sheet1" to the last row of "Sheet5".
• After the values were copied, you want to add 5 new rows to the bottom of "Sheet5".

It seems that your script uses the same sheet. So, in order to achieve your goal, how about the following sample script?

Sample script:

function copyRows(numRows = 5) {
  const ss = SpreadsheetApp.getActive();
  const srcSheet = ss.getSheetByName("Sheet1");
  const srcRange = srcSheet.getRange("A1:AH5");
  const dstSheet = ss.getSheetByName("Sheet5");
  const lastRow = dstSheet.getLastRow();
  const dstRange = dstSheet.getRange(lastRow + 1, 1);
  srcRange.copyTo(dstRange, {contentsOnly: true});
  dstSheet.insertRows(lastRow + 6, numRows);
}

• If you are not required to insert new rows, please remove dstSheet.insertRows(lastRow + 6, numRows);.

References:

• copyTo(destination, options)
• insertRows(rowIndex, numRows)