Sheet.appendRow () sporadically fails and results in an empty string

Question

Using google-apps-script from google spreadsheet, I am trying to extract several hundred rows of data using fetchUrl and paste them into my sheet using sheet.appendRow.

Getting data and dividing into arrays is fine. But when you add them to lines, line by line sometimes occurs without errors and leads to empty lines.

Here is a pretty 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 lines, from time to time, becomes empty. The Utilities.sleep () insert also does not work.

Does anyone have any ideas to get rid of this situation? Thank you in advance.

Answer 1

You can try using a batch record on a 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); } 

Please note: if you think the sheet will be too "short", you can also add empty lines in the loop.

EDIT: after Michael's answer, it would be much better to add the necessary lines using sheet.insertRowsAfter(sheet.getMaxRows(), target.length)

Answer 2

Using appendRow inside a loop is flaky, not the best. Please look:

http://productforums.google.com/forum/#!search/appendrow/docs/y0E9PB_VTJw/cHQznHh1WLIJ

to discuss this issue.

Answer 3

Just a small comment: I use appendRow in a loop, adding perhaps up to 30 lines at a time. The script is used daily for about a week.

The interesting part is that during the testing of the script, when I set it to add about 100 lines, I also saw that the lines were missing. But after a simple manual update of the destination table, I did not see the missing rows. So, I realized that the lines were indeed added, but this error? lies on their display.