Appending multiple rows to Spreadsheet - Google Apps Script

Question

I wish to append multiple rows to a google sheet via GAS whilst being considerate of performance and undesired possiblities.

To achieve this for a single row, I would use appendRow as this tackles problems with intervening mutations and completes all actions in a single function.

Simple Example:

var sheet= SpreadsheetApp.openById(ssId).getSheetByName(sheetName);
sheet.appendRow(["foo", "bar", "foobar"]);

Of course to extend this to multiple rows, I could simply loop over this function for each row, though GAS best practices advises against such practices.

Attempts to use appendRow to add multiple rows via a 2D array were unsuccessful and led to the API using references to the secondary arrays as the values going into the row.

I therefore ask, is there a way to append multiple rows to a spreadsheet that still tackles the intervening mutuability that appendRow does and avoids looping where possible?

Answer 1

You can use the Range.setValues() method which will set the values at once, and a script lock (or another lock, depends on your use case) to prevent other instances of the script from doing appends at the same time. You just need to get the good range (with array length) and the good position (with sheet.getLastRow() + 1 method). Here is an example:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var example = [[1,2,3],[4,5,6],[7,8,9]];

LockService.getScriptLock().waitLock(60000);
sheet
  .getRange(
    sheet.getLastRow() + 1,
    1,
    example.length,
    example[0].length
  )
  .setValues(example);

Caveat: This does not protect against humans or other scripts.