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.

Answer 2

I would make sure to also make a row & column variable for this snippet of code. Sometimes GAS will throw an error if you try to add the object.length directly in the getrange function. i.e.

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

sheet.getRange(sheet.getLastRow()+1, 1, row, column).setValues(example);

Answer 3

You should check out the official documentation for the Sheet Class, there are a number of bulk row insert methods listed, one of which may fit your criteria. If that proves insufficient you may want to look into the Advanced Sheet Service.

EDIT

In response to your comment, I don't believe there is a way to add rows and data in bulk as a purely atomic operation. But there is a way to address your concerns regarding intervening mutations, via the LockService API. This service allows you to prevent concurrent access to a block of code, so for your use case you can leverage the bulk insert methods to create new rows and populate them without having to worry about mutations.