sheet.appendRow() sporadically fails silently and

Question

Using google-apps-script from google-spreadsheet, I'm trying to fetch some hundreds of rows of data using fetchUrl and paste them into my sheet using sheet.appendRow.

Fetching data and splitting to arrays are OK. But appending them to a sheet row by row ocasionally fails without any errors and resulting in empty rows.

Here's quite simple example to recreate the problem.

function appendTest() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[2]);
  for (var i = 0; i <= 100; i++) {
      sheet.appendRow([i, 1, 2, 3, 4, 5]);
  }
}

About 5 or 10 rows, differs time to time, become empty. Inserting Utilities.sleep() seems to be not working, either.

Does anybody have any idea to get rid of this situation? Thank you in advance.

Answer 1

Just a small comment: I use appendRow in a loop, appending maybe up to 30 rows at a time. The script has been used daily for about a week now.

The interesting part is that during testing of the script, when I set it to append about 100 rows, then I too saw rows missing. But after a simple manual refresh of the destination spreadsheet, I saw no missing rows. So I took it that the rows were in fact appended, but that the bug? lies in their display.

Answer 2

Using appendRow inside a loop is flaky and not best practice. Please see:

https://productforums.google.com/forum/#!topic/docs/y0E9PB_VTJw

for a discussion of this issue.

Answer 3

You could try to use batch writing to the sheet like this

function appendTest() {
  var target = new Array()
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[2]);
  for (var i = 0; i <= 100; i++) {
      target.push(['some data','in some columns','bla bla']);
  }
sheet.getRange(sSheet.getLastRow()+1,1,target.length,target[0].length).setValues(target);
}

Note that if you think the sheet will be too 'short' you could append empty rows in the loop as well.

EDIT : following Michael pertinent answer, it would be a much better idea to add the necessary rows using sheet.insertRowsAfter(sheet.getMaxRows(), target.length)