Prevent consecutive clicks of Google Sheets Image Buttons in with Lock Service and Google Apps Script

Ever seen an overzealous user madly clicking on an image button in Google Sheets to run an automation script? It’s maddening, frustrating and most importantly, may cause errors in your beautifully crafted code.

In this short tutorial, we explore how to use Google Apps Script’s Lock Service to prevent users from executing your code from your image button while the original instance of the code is still running.

Grab a copy of the starter sheet below to play along.

To the Starter Sheet

The  Video Tutorial

Setup

In our starter sheet, we have a basic button that, when clicked:

1. Retrieves the current count from the cache using Cache Service.
2. Generates a toast (That’s a small message box down the bottom right of the sheet) when the counter starts at zero.
3. Displays the count from the counter in cell C16.
4. Waits for 10 seconds to simulate a whole bunch of very awesome code going on in the background before updating the counter by one.
5. Stores the count back in the cache for 15 seconds.

Here’s the code:

Go ahead and add the code to your starter sheet and go crazy clicking the button. What’s occurring?

Yeap, the script tries to run each time you click the button, but the cache hasn’t been updated yet to store the new values.

This would simulate a user attempting to run a process multiple times but the process has yet to complete. However, it relies on the result of the previous process to accurately run the next one. Not ideal.

Another scenario may be that you want to minimise unnecessary clicks. Imagine that when the user clicks a button, the script appends a new piece of data to a Google Sheet. Perhaps they only wanted to click the button once but they got a little click-happy (we’ve all been there) and did a double click instead.

You can simulate this in the code above by moving the Utilities.sleep() method to below the cacheServ.put method.

Go ahead and give it a try. You will see the counter increasing but a bunch of executions stack on top of each other on the Sheet UI.

Move the sleep method back to above the couner++ line.

Let’s dive into a solution.

Limiting consecutive executions with Lock Service

The Google Apps Script Class Lock Service allows us to prevent simultaneous access to certain parts of our code.

Lock Service allows us to lock a part of a script under 3 circumstances:

1. Document Lock (getDocumentLock): This prevents a user of the current document from running the desired section of code when the code is already being run.
2. Script Lock (getScriptLock): This prevents all users from running the desired section of code when the code is already being run.
3. User Lock (getUserLock): This prevents the same user from running the desired section of code when they are already running that code.

For our example, we could have selected User Lock in the circumstance where we did not want them to madly click the image button, but it wouldn’t have saved us from other users who may also be trying to execute the code from the same button.

We could, however, have used the document lock or the script lock in our example as we are running the code within the one document. Let’s settle on Script Lock.

Building the Lock on the script

Get the lock class

First, we call the Google Apps Script Lock Service  requesting the scriptLock() method.

Try Lock or Wait lock?

• Try Lock: tryLock(timeoutInMillis) – When the user executes the code, the script will make several attempts to run until the timeout period has expired. The method will return a boolean, true if it can successfully run the code or a false if it cannot.
  This is useful if you want to carry out some other process if the current one is being occupied and the lock period expires. Say the lock period expires and you save the data inputs in a cache and update it later when the process is not so busy.
  Alternatively, you can call a return on the function and gracefully complete the execution of the script.
• Wait Lock: wailtLock(timeoutInMillis) – This method differs from Try Lock in that rather than returning a boolean response, it throws an error if the timeout period is exceeded and the script is not freed up to be run.

We don’t want to freak our users out with an error, so we are going to use Try Lock in our example.

So the next question we need  to ask is, ‘How long do we want to set our time out?’

Well, in the scenario where we just don’t want to process overenthusiastic clicks, then we should set the timeout to zero milliseconds.

On the other hand, if we are trying to wait for a process to conclude first then we might want to just delay the lock attempt for a little over the average time it takes to run the script.

In the end, being an image button on a Google Sheet, it is probably best to just set the Try Lock to zero, add a warning that there is an execution already underway and have them attempt the button again a little later.

Below out last lock code add the following:

const success = lock.tryLock(0)

If the code is running, warn the user and don’t run the code

We can now check our success variable to see if it is false. If it is, then we want to send a toast to the user’s UI and complete the script. We can do this by returning a Spreadsheet App toast() message. This method can take a single string as an argument, our warning message.

Add the following code below the success variable.

Ending the lock

The lock will end naturally when the script execution is complete.

However, if you want a concurrent user  to run the script outside of the locked region use can use the releaseLock() method.

We will set the lock to encompass the rest of the code. So at the bottom of our function, we will add the following:

Run the code

Go ahead and run the code from the button in the UI. Notice the “locked!” toast message appears down the bottom right of the screen after consecutive clicks while the initial script is still running.

Our number only updates after the previous execution is complete.

You will also see that the script executions complete faster providing less of a script backlog.

I would love to hear how you would use this in your own project. Let me know in the comments below.

If you want to see lock service in a larger project using a WebApp and Google Sheets, check out this tutorial:

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

You can also learn how to remove a Google Sheets button after it has been clicked here.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

The Full Code with Google Apps Script Lock Service