Google Sheets script - slow running

Question

I have the following code to copy the last row (effectively used as a template) and paste it a user-defined number of times into the sheet, effectively inserting new rows:

function InsertNewRows() {
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const sheet = ss.getActiveSheet();

   var result = SpreadsheetApp.getUi().prompt('How many rows to enter?');
   var numRows = result.getResponseText();
   if (!numRows) {
     SpreadsheetApp.getActive().toast("No value entered");
     return;
   }
   for (i=1; i<=numRows; i=i+1) {
     sheet.insertRowAfter(sheet.getLastRow()-1);
     let sourceRange = sheet.getRange(sheet.getLastRow(),1,1,getLastCol());
     sourceRange.copyTo(sheet.getRange(sheet.getLastRow()-1, 1));
   }   
   SpreadsheetApp.getActive().toast(numRows+" row(s) added.","NEW ROWS ADDED");
};

The creation of a new row and the copy of the last row (basically everything inside the for loop) run very slowly. It's not a major issue since I don't expect to be adding a lot of rows, but can anyone shed light on ways it could be optimised?

Notes:

• getLastCol() is a function to get the last column within a specific range (as distinct from getLastColumn())
• In an earlier version of this, I had the let sourceRange = ... line outside the for loop, but the contents of the cells were not being pasted properly, I think because the value of getLastRow() changed after I performed sheet.insertRowAfter

Answer 1

You can speed up execution by using batching to reduce the number of API calls.

The following assumes that you need to copy values, formats and formulas:

• First add rows in one go with Sheet.insertRowsAfter(), which also copies formats from the row above.

• Then use Range.setValues() to fill the new rows with a 2D array that contains the values and formulas, repeating what's in the source row numRows times. Use Range.getFormulasR1C1() to get formulas in a syntax that observes absolute and relative references.

Here's one way to do that:

function addRows() {
  const numRows = Number(SpreadsheetApp.getUi().prompt('How many rows to add?').getResponseText());
  if (!numRows) return;
  addRows_(numRows);
}

function addRows_(numRows = 1, sheet = SpreadsheetApp.getActiveSheet()) {
  const [rowEnd, columnEnd] = [sheet.getLastRow(), sheet.getLastColumn()];
  const sourceRange = sheet.getRange(rowEnd, 1, 1, columnEnd);
  const values = sourceRange.getValues().flat();
  const formulas = sourceRange.getFormulasR1C1().flat()
    .map((formula, index) => formula || values[index]);
  const fill = new Array(numRows).fill(null).map(_ => formulas);
  sheet.insertRowsAfter(rowEnd, numRows);
  sourceRange.offset(1, 0, numRows, columnEnd).setValues(fill); // also adds formulas
}

In the event you're only copying values and formats, but not formulas, you can use the Range.copyValuesToRange() and Range.copyFormatToRange() methods, like this:

  // ...
  const sourceRange = sheet.getRange(rowEnd, 1, 1, columnEnd);
  sheet.insertRowsAfter(rowEnd, numRows);
  sourceRange.copyValuesToRange(sheet, 1, columnEnd, rowEnd + 1, rowEnd + numRows);
  sourceRange.copyFormatToRange(sheet, 1, columnEnd, rowEnd + 1, rowEnd + numRows);
}

See Number, Range.getFormulasR1C1(), Range.copyValuesToRange() and Range.copyFormatToRange().