D
D
Den Mokrinsky2020-12-02 19:33:05
Google Apps Script
Den Mokrinsky, 2020-12-02 19:33:05

How to get around overwriting rows with a large number of calls to a script that creates new rows in a Google spreadsheet?

There is a script that creates new rows with data in the table
....
var NewRow = sheet.getLastRow()+1;
...
sheet.getRange("A"+NewRow).setValue(date);
....

I call the script after user registration through the form on the site and transfer each new registration to the Google spreadsheet. Faced with the fact that with a high frequency of registrations in a short time, the script does not have time to detect that one line has already been added, and instead of several entries, it creates only one, i.e. overwrites the last one. There is no possibility to queue outside the script.

Perhaps there is some alternative way to write data to new lines?

Answer the question

In order to leave comments, you need to log in

3 answer(s)
M
Maxim Stoyanov, 2020-12-02
@den_mok

Option 1 - Use a lockservice (more details in the google appsscript documentation)
Option 2 - First save the script cache data, and set up a trigger that pulls the data from the cache once a minute and puts it in the table.
Option 3 - Use sheet.append()
Option 4 - Save to database instead of table
Option 5 - After each setValue row insertion or before, use SpreadsheetApp.flush() (but this option is not very good on its own, its better use in conjunction with others)
---
Maxim Stoyanov (stomaks), Google Apps Script developer .
g-apps-script.com
stomaks.me

R
rPman, 2020-12-02
@rPman

Try to store an amount of records (more precisely the first empty line after the table) in the same table. So get the string you want to store the record in and increment the value in one string (not that it's atomic, but it will definitely reduce the chance of collisions a lot)

var seq=sheet.getRange("A1");
seq.setValue((newRowNum=seq.getValue())+1);

thanks to this, each instance of the running script will get its own line number from the very beginning,
or look for another place to store this sequence, in which there is an atomic operation seq_id=seq++

B
BasiC2k, 2020-12-02
@BasiC2k

I do not recommend using GS for highly loaded systems. I fiddled with this for a long time.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question