CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
David Ho
David Ho

Reputation: 81

Apps script inserts new row and values into a Google Sheet. However, it somehow skips rows if you submit too fast

I have a server side apps script that inserts a new row into a Google sheet, and then adds data to corresponding columns. Here is a short code snippet of what it does:

//check to see what is the last row of the sheet
var last_row = sheet.getLastRow();


//then, insert a new row after the last detected row
sheet.insertRowAfter(last_row);

//then, set current work row to last_row + 1, then do some adding data. 
//Here, we add a current_timedate Date object that was created earlier.
var current_row = last_row + 1;
var current_timedate_cell = 'A' + current_row;
sheet.getRange(current_timedate_cell).setValue(current_timedate);

This is all pretty standard stuff. However, I noticed if I call the function that does all this pretty quickly from the front end interface (I have another client side javascript / html interface that calls the server side javascript function with onClick method on a button), it starts to skip rows. There also seems to be some bizarre concurrency issues. Some rows actually have partial data inserted, but only for checkboxes that I'm adding. None of the other stuff gets added. I can't tell if there is mixing of data between rows since all of the data is repeated. So, I can't tell if row 2's column H is now in row 3's column H, etc... To be fair, I am inserting one LARGE cell per row up to the 50k character limit. So, each row is probably close to 50k. That could be causing these issues

Can anyone provide some insights? I know Google Sheets is not supposed to be a relational database with ACID integrity and concurrent performance. But, I thought it would do a better job of handling 50kb inserts.

I've tried SpreadsheetApp.flush(). I'm somewhat aware of the rate limitations, and perhaps that might be it. I'm sometimes doing the inserts at a rate of about 3 inserts / second. I'll go double check quota rate limits. But, 3 / second doesn't seem incredibly high.

Expected results: all data requested to be inserted is inserted Actual results: lots of skipped rows on faster insert rates

I'm doing all of this in a try / catch block, and so far I'm not getting any errors thrown. If there is an error thrown, I both log it in Logger and email it to myself via the MailApp.

Upvotes: 1

Views: 1305

Answers (1)

ross
ross

Reputation: 2774

Just going to add it as an answer instead of comment since it answered the question:

Use appendRow() instead of insertRowAfter() for this purpose.

Upvotes: 1

Related Questions