20

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?

dbr
  • 661
  • 1
  • 8
  • 21

3 Answers3

24

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.

Alec Mev
  • 4,663
  • 4
  • 30
  • 44
Pierre-Marie Richard
  • 1,914
  • 2
  • 19
  • 25
  • 1
    Does this create new rows where necessary? Whilst I'm aware of the existence of all the above methods, I had myself under the impression that sheet.getRange() could get a range if it existed? – dbr Jun 22 '17 at 09:41
  • 2
    that's right, the sheet.getRange() method is working on existing range. In this case, you can add some rows with [Sheet.insertRows(rowIndex, numRows) method](https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertrowsrowindex-numrows) – Pierre-Marie Richard Jun 22 '17 at 09:45
  • 1
    In which case, your answer sadly does not satisfy my criteria. This method can lead to mutations to the sheet in between the row creation and data insertion. (I.e: somebody inserting data). Overcoming this is a key bonus of `appendRow`. – dbr Jun 22 '17 at 09:46
  • 1
    In your case, I think that none of method except appendrow() will fulfill you request. Your only solution is to loop over the function, which is, I agree, sad. – Pierre-Marie Richard Jun 22 '17 at 11:55
  • 1
    Deeply upsetting. Looping it is then. – dbr Jun 22 '17 at 11:57
  • 2
    Try using the Lock Service API, with it you can prevent concurrent access and avoid "intervening mutations" – TheAddonDepot Jun 22 '17 at 12:36
  • 1
    This was exactly what I needed. There's no native "appendRows" so you have to insert the # of rows you need, get that range of cells, and set their values to your array. Thanks! – Shikima Jan 17 '18 at 02:28
11

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);
Colin Jameson
  • 119
  • 1
  • 4
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.

TheAddonDepot
  • 8,408
  • 2
  • 20
  • 30
  • 1
    Sadly, you have misunderstood the question somewhat also. Whilst the bulk row insert methods have their place, they are not suitable here as they provide a method to insert the new rows, but then require a seperate call to then fill the values. This leaves it open to intervening mutations. As for the Advanced Sheet Service, I had not considered that so I shall have a look. – dbr Jun 22 '17 at 11:44
  • 3
    Lock Service. You bloody genius – dbr Jun 22 '17 at 12:39
  • Don't thank me yet. Test to make sure it works for your use case. If it does make sure to mark this as the preferred answer ;) – TheAddonDepot Jun 22 '17 at 12:42
  • will LockService API prevent the spreadSheet from being tampered with during the duration of the lock? I do not seem to understand what is being meant as `lock the block of code` secondly, should you lock it with a tentative value as duration e.g 30 seconds ? or should you rather lock it for the duration of the entire operation ( and how will you know how long to lock it ? ) – Edwin O. Nov 22 '17 at 12:04