How to set up delays in Google Apps Script? [closed]

Question

I'm using the following method to encode URLs in Google Sheets:

How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?

It works. The only problem is – when I apply it to a sheet with 6000 entries – it gives ERRORs. As far as I understand this is due to too many requests. One of the help messages suggested trying to set in delays.

How can I do this?

Answer 1

To set up a delay on the execution of a Google Apps Script use the method sleep(milliseconds) of Class Utilities.

I'm intentionaly not including an example of using this method here because this question is about custom function (a Google Apps Script function used in a Google Shees formula), keep reading.

The above will work between code sentences and might be helpful in "regular scripts" to reduce the rate to call a method, i.e. when using UrlFetchApp.fetch(url), deleting rows, among other cases but not when the problem occurs when using a formula paterns that is repeated thousands of times (i.e. when writing a formula in A1, an then doing fill down or copy paste to copy the formula to whole column)

It's very likely that you should try to convert your custom function from one that returns a single value into one that is able to get and return an array of values. See Optimization.

Note: If a custom function like Vidar S. Rambal's answer to the referred question by the OP, it doesn't make sense as the it only calls a method.

/**
 * @customfunction
 */
function encode(value) {
  return encodeURIComponent(value);
}

Instead of repeating the same formula multiple times,

	A
1	=encode(A1)
2	=encode(A2)

you might use

	A
1	=encode(A1:A2)

by using the following declaration for the custom function:

/**
 * @customfunction
 */
function encode(input) {
  return Array.isArray(input)
     ? input.map(row => row.map(cell => encodeURIComponent(cell))) 
     : encodeURIComponent(input);
}

but when the formulas are not contigous, then it might be better to use a "regular" script called from a custom menu or by using a trigger.

Related

• Code invoked too many times

Answer 2

Not the most elegant solution but I used the script cache as a mutex of sort to prevent too much executions in parallel. I'm sure some must execute at the same time but few enough to not be a problem for me.

// Entry point of the function
var cache = CacheService.getScriptCache();
var last = cache.get('running');
while (last  != null) {
  Utilities.sleep(500);
  last = cache.get('running');
}
cache.put('running', '_', 1); // This data will be cached for just 1 second

If the functions wait too much due to the long queue they will timeout.