Google sheets - Error 2

[Andrew Hammond]

Using the Google Sheets API I am able to update sheets repeatedly, but on a limited number of occasions I receive an Error 2 - End of File when writing a row of data.

I am using SetRangeRow to select the row then passing the data with SEP characters on a Write. The data being written is modest, about 6 columns, no large text.

On rerunning the program the error will not occur, or sometimes occur on a totally different row, it has never yet occurred on the same row, but they're not that frequent.

Setting a WAIT between rows reduces the frequency of the error significantly.

The Google connection is via a 450Mb VPLS circuit, so a fast connection.

On a large sheet (2900 rows) some rows appeared as a blank row, until the WAIT statement was introduced. Running with the WAIT in place allowed the data to appear. The correct number of rows had been created, they were just empty, until the slower process filled them in.

Is there a better way of doing large bulk uploads than row by row or handling the error condition ?

It feels as though it may relate to the way Google receives the data.

[Devon Austen]

If you are creating a new Google Spreadsheet and populating it with data you could optimize it by creating a simple CSV file with the data and use the UploadSpreadsheet() method. This will allow it to happen in one operation.

That won't work for updating an existing spreadsheet though and in that case you can use the Write() method but there is no reason you can't update all of your data in a single write. The Write() is not restricted to a single row but can write any arbitrary range of data.

cell_values$="Row A Data 1"+SEP+"Row A data 2"+SEP+"Row A Data 3"+SEP+"Row B Data 1"+SEP+"Row B data 2"+SEP+"Row B Data 3"+SEP+"Row C Data 1"+SEP+"Row C data 2"+SEP+"Row C Data 3"+SEP
cells$="A1:C3"
gSheets'Write(cell_values$,cells$)