You can’t deliberately delay the execution of an entire Google Sheet using a cell function. Most of the in-built formula functions on you can use in a cell execute very quickly. Some of them, such as IMPORT functions, can take a little time to return data. But these, like any Apps Script function you can write will run in parallel and won’t delay the rest of the sheet whilst they are running.
Why is this? Well, if Google Sheets allowed you to use a WAIT() function in your formulas, you’d end up with users wondering why their sheet has frozen, closing it, complaining, and submitting bug requests. You get the idea. We expect things to work quickly these days.
Just imagine, you enter =WAIT(500) (500 seconds) into cell A1 and copy it across to 50 other cells. It would block the sheet for 25,000 seconds. Is that what you wanted?
There are, however, methods of making Apps Script functions wait using locks and JavaScript utility methods. But your Apps Script functions will always run in parallel for performance reasons. And you also need to be careful that you don’t delay your scripts for over 6 minutes. That’s as much runtime as you’re allowed on both Workspace and Free Gmail accounts.
Cell calculation order
Let’s look at how a sheet executes. If you can’t see the image below please open our example sheet which shows a demonstration of cell calculation order.
On the first tab, we use the NOW() function (which returns the current date and time) across a range of 400 cells to see how they execute. We formatted the cells to show the millisecond portion of the time returned and used conditional formatting to highlight cells where it changed.
We know that the NOW function will calculate for every change made to a sheet. So we made one change and, as you can see from the results, 400 cells calculated in 25ms and the times show the order of calculation to be column by column.
On the second tab tried the same test using an Apps script function that returned the current time that contained a 500ms sleep delay. Apps Script functions don’t execute on sheet change, so we copied our function to all 400 cells at the same time.
The Apps Script functions in 400 cells, all taking 500ms each should have taken 200 seconds to run, but they finished in 29.5 seconds and at seemingly random times. This shows that they all ran in parallel.
You can’t delay a sheet. Everything that might take time executes as its own parallel process.
How to delay a script function
If you want to create your own sleep function that you can use within a cell in Google Sheets, you need to use Apps Script and the Utilities library. Inside is a simple sleep method that takes one parameter denoting the number milliseconds to pause execution for.
Don’t forget, this will only sleep inside the current instance of that function. If you call if from 100 cells, 100 functions all running in parallel will all sleep together. Well… together-ish, as you can see from out test above above. All 100 functions won’t start or finish at exactly the same time.
function sleep(ms) {
Utilities.sleep(ms);
}Code language: JavaScript (javascript)Using wait lock to delay functions
Let’s say you’ve written an Apps Script function that calls into the Amazon API to get some product data. You have a column of product numbers (ASIN codes) and a second column that uses your function to call into the API using the ASIN to return the data you want.
If you list 500 ASINs in your sheet, you’ll immediately have 500 API calls all executing at once. Most good APIs will throttle your usage to stop you from overloading their servers. They may even block you for a period.
So how do you stop your Apps Script function from abusing your API limits?
That’s where the lock service comes in. Here’s a brief example of how you’d use a lock.
function my_api_call() {
//get a lock that prevents any user from concurrently running this section of code
var lock = LockService.getScriptLock();
//wait for up to 30 seconds for other processes to finish.
lock.waitLock(30000);
//call the api
//.....
//release the lock so that other processes can continue.
lock.releaseLock();
}Code language: JavaScript (javascript)If you copied my_api_call() to 10 cells, only one of them would actually call your api. The others would wait up to 30 seconds for their chance. When the first one had released the lock, the next would jump in and secure it. And, if any of them waited longer than 30 seconds they’d throw an exception.
Locks are available at the user, script and document level.
Don’t forget, you can’t set your wait time too high and delay your script for over 6 minutes. Otherwise it will fail. 6 minutes is as much runtime as you’re allowed on both Workspace and Free Gmail accounts.
So locks would work well for delaying small numbers of requests to a fast responding API and where you know everything is likely to finish within 6 minutes. But what if you need to delay things even longer?
Using trigger timers to run scripts periodically
If you have Apps Script that needs to get a lot of information from an external service and it’s likely to take you more than your allotted script runtime, you need to consider batching up the requests and triggering them using a timer.
Timers are available with the Apps Script editor and can be setup to trigger the execution of an Apps Script function on a range of different schedules. Monthly, weekly, daily, hourly, and minute time driven schedules are available.
I’ve used this technique myself to process hundreds of API calls to Google Analytics within a Google Sheet. I setup a second sheet tab to keep track of my apps script variables (such as batch size, last row processed). My api calls function then read the data from that sheet, made a certain number of calls and updated the sheet with its progress. I triggered this to run every hour.
This allowed me to get a lot of data from the Google Analytics API without over using my workspace script runtime allocation or my Google Analytics data limits!