I have set up a spreadsheet bounded script as web app which processes get requests. I want to prevent the spreadsheet from overwriting rows, which is why I'm using the LockService to prevent this. I'm using the getScriptLock() method for this.
The problem is that when the web app is receiving concurrent requests the LockService seems to fail. I have set up my minimal reproducible example like this:
function doGet(e) {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName("Sheet1");
const randomNumber = Math.round(Math.random() * 100);
const lock = LockService.getScriptLock();
const success = lock.tryLock(10000);
if (success) {
sheet.getRange(sheet.getLastRow() + 1, 1).setValue(randomNumber);
}
SpreadsheetApp.flush();
lock.releaseLock();
return ContentService.createTextOutput("Done");
}
I started with a blank spreadsheet. It should store a random number for each request in a new row in the first column. To make sure it handles multiple requests at the same time well, I set up a spreadsheet with the web app url 20 times, appended by a parameter with an increasing value. You can open those urls at the same time by selecting them all and pressing left alt + enter on your keyboard. Now the concurrent requests are made.
The problem I have is that the cells are being overwritten. I have created a video to show the behaviour: https://www.youtube.com/watch?v=G9BiktDnhic
What am I doing wrong? For some reason running SpreadsheetApp.flush() one line above the getScriptLock() call seems to solve the issue. But... why?
It appeared to be a bug in Google Apps Scripts. The bug has been fixed. The issue can be found here: https://issuetracker.google.com/issues/193051316.
