21

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?

Improve this question

3 Answers 3

Reset to default
27

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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?
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
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.
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.
Try using the Lock Service API, with it you can prevent concurrent access and avoid "intervening mutations"
|
13

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);

Improve this answer

1 Comment

Not an atomic operation.
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.

Improve this answer

4 Comments

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.
Lock Service. You bloody genius
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 ;)
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 ? )

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.