I have a google form that is linked to a spreadsheet. On the form side I added an onFormSubmit From form trigger.
The problem is that there are several other triggers that are fired on the spreadsheet side from onSubmit sending data to a webhook and then information is relayed to the last row of data.. (Triggers Zapier, webhook etc)
By the time all of the data is fully calculated and relayed back to the last row another submission from the form could come in during this process causing the functions to input the relayed data to last row from the form submission.
I need to delay the form responses for X amount of time to allow for data to finish calculating from the previous form submission.
I have tried using LockService and Utilities.sleep(20000), but it does not seem to prevent form submissions during the defined waitLock timeframe.
function onFormSubmit(e)
{
try
{
var lock = LockService.getPublicLock();
// Wait for up to 30 seconds for other processes to finish.
lock.waitLock(30000);
Utilities.sleep(20000); // 20 second
var formResponses = e.response.getItemResponses();
var testInput = formResponses[0].getResponse();
//append NEW FORM URL to MAIN SPREADSHEET
var sh = SpreadsheetApp.openById("1jkvSWC1ew4mt1sEy6XX2vsc_5norWHrbOYQt5hLhwGA");
var lastRow = sh.getLastRow();//gets the last row of entered data
lastRow += 1;
sh.getRange('D'+lastRow).setValue(testInput);
// Release the lock so that other processes can continue.
lock.releaseLock();
}
catch (error)
{
// If there's an error, show the error message
return error.toString();
}
}
Expected results: Delay a form response being added / submitted to the linked spreadsheet for X amount of time.
Actual results: Form receives a submission and the response is added to a new row on the sheet regardless of the lock being used. What is being delay is the additional information being added to the lastRow. But, what I'm looking for is to queue / delay submissions from the form.