The doPost function is executed twice (google apps script)

Question

I hope for your help, since the problem is non-standard for me and I could not solve it after many attempts.

Problem

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

Attempt to solve

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

Answer 1

Please try to do flush() before setValue

Answer 2

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