Silent Failure of Sheet.appendRow() Leads to Empty Rows at Random Intervals

Occasionally, adding values to a sheet row by row fails without any indication, and empty rows are created as a result. While the form is functional, the added values begin in column A, and I am looking to modify my code to specify the columns where the appended values are placed.

Question:

I’m attempting to retrieve several hundred rows of data from
google-spreadsheet
by utilizing Google-
apps-script
and the fetchUrl function. Then, I plan to add the collected data to my sheet using the sheet.appendRow method.

The process of retrieving and dividing data into arrays is satisfactory, however, adding them to a sheet one row at a time may sometimes fail without any indication, leading to blank rows.

Here’s a straightforward instance to replicate the issue.

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

The number of empty rows may vary between 5 to 10 at different times. Despite attempting to use Utilities.sleep(), it does not appear to resolve the issue.

Could someone suggest a solution to overcome this predicament?
Your assistance is greatly appreciated.


Solution 1:

One way to write to the sheet efficiently is by using batch writing.

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

It should be noted that if you anticipate that the sheet will not have enough rows, you have the option to include additional empty rows within the loop.

After receiving Michael’s helpful response, it is recommended to utilize

sheet.insertRowsAfter(sheet.getMaxRows(), target.length)

when inserting the required rows.


Solution 2:


Utilizing the appendRow function within a loop is unreliable and not considered as optimal methodology. Kindly refer to:

Here is a Google Product Forum link where you can find discussions about Docs.

for a discussion of this issue.


Solution 3:


I would like to make a brief remark regarding my use of the appendRow function. Specifically, I have been using this function within a loop to append a maximum of 30 rows at once. It is worth noting that I have been running this script on a daily basis for approximately one week.

While testing the script, I noticed missing rows after setting it to append around 100 rows. However, a simple manual refresh of the destination spreadsheet showed no
missing rows
present. Therefore, I concluded that the rows were appended and the issue may lie in their display.

Frequently Asked Questions