Tuesday 7 November 2023

Control Form submissions with Script Lock

The following Google Apps Script is one example of how the Lock Service can be used to prevent concurrent running of code. Here we have a Google Form that can be submitted by users at any point, the code then takes some of those details and appends them to another Google Sheet row. In normal circumstances this will happen relatively quickly and without clashes, but what if multiple people submit the Form at the same time!?

The likelihood is that whilst the Form data will still be collected correctly in the response sheet, the append Sheet will either overwrite data as it tries to keep up or miss it completely. By using the Lock Service we can allow each Form submission time to complete its bit of code before the next one starts.

Lock Service code snippet
Lock Service code snippet

The Code

In this example we make use of 'Utilities.sleep()' simply so that we can slow down the code enough to be able to make multiple Form submissions and test the Lock Service at our own pace.

There are 3 types of Locks and in this example we will be using 'getScriptLock()' to prevent any user from concurrently running a section of code:

var lock = LockService.getScriptLock();

Now we need to try and acquire the Lock so we know we have permission to run our section of code and ensure it is not in use already. We include a timeout here (10 seconds in this example) to prevent it from perpetually waiting:


We then test to see if it has been acquired (boolean) before running our main code snippet:

if (lock.hasLock()) { ...

At the end we need to release the Lock for the next user:


This code does not currently handle what should happen if the Lock is not acquired within the 10 second timeout. You may decide to have it send an email at this point to inform you of an issue, etc.


You will need to create a 'form submit' trigger in order to connect the Apps Script code with the Google Form.

Control Form submissions with Script Lock folder download (please use 'File' > 'Make a copy' for your own version of each item).

No comments:

Post a Comment