I hope for your help, since the problem is non-standard for me and I could not solve it after many attempts.
I have a standard function for receiving POST requests and she it works stably on small volumes, sample code:
let sheet = SpreadSheet.getSheetByName("name")
let dateTime = Utilities.formatDate(new Date(), "GMT+3", "dd.MM.yyy HH:mm:ss") // Moscow time
<...>
function doPost(e) {
sheet.insertRowBefore(2) // adds a line before the second one
sheet.getRange(2, 1).setValue(e.parameter.par1)
sheet.getRange(2, 2).setValue(dateTime)
sheet.getRange(2, 3).setValue(e.parameter.par2)
sheet.getRange(2, 4).setValue(e.parameter.par3)
sheet.getRange(2, 5).setValue(e.parameter.par4)
}
In the table it looks like this: Example
In essence, the function must be executed synchronously and work stably, but when the function receives a lot of requests (about 140 in 1-10 seconds), it can create two rows at once and write data twice to the same second row. As a result, for 140 queries, there are ~4 empty rows in the table, the data from which was lost
This is how an empty string added by mistake looks like: Example
To prevent two scripts from running simultaneously, I used the LockService function from google apps script, my code began to look like this:
let sheet = SpreadSheet.getSheetByName("name")
let dateTime = Utilities.formatDate(new Date(), "GMT+3", "dd.MM.yyy HH:mm:ss") // Moscow time
var lock = LockService.getScriptLock() // LockService
<...>
function doPost(e) {
if (lock.tryLock(30000)) { // waiting for a queue 30 seconds
sheet.insertRowBefore(2) // adds a line before the second one
sheet.getRange(2, 1).setValue(e.parameter.par1)
sheet.getRange(2, 2).setValue(dateTime)
sheet.getRange(2, 3).setValue(e.parameter.par2)
sheet.getRange(2, 4).setValue(e.parameter.par3)
sheet.getRange(2, 5).setValue(e.parameter.par4)
lock.releaseLock() // unblocking
}
}
I tried different queue waiting times, but it didn't lead to success. The function continued to leave empty lines
I ask for your help, as I have been puzzling over this problem for a long time. What to do? I will be grateful for any information
In addition to flush() I'd recommend to replace this:
// 5 calls (get/set) to server
sheet.getRange(2, 1).setValue(e.parameter.par1)
sheet.getRange(2, 2).setValue(dateTime)
sheet.getRange(2, 3).setValue(e.parameter.par2)
sheet.getRange(2, 4).setValue(e.parameter.par3)
sheet.getRange(2, 5).setValue(e.parameter.par4)
with this:
// 1 call (get/set) to the server
sheet.getRange(2,1,2,5).setValues([
e.parameter.par1,
dateTime,
e.parameter.par2,
e.parameter.par3,
e.parameter.par4
])
Best Practices: Batch operations