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?

  • When asking help with an error, please include the textual error message. In general questions about Google Sheets should include some sample data for input and the expected result, in the case of questions about custom function, also should include the code of the custom function if it's not too long, otherwise create a minimal complet example. You should have searched thoroughly this site, the Google Sheets section in Google Apps Script mini site developers.google.com/apps-script share what you found and why it didn't meet your needs.
    – Rubén
    Feb 28 at 18:41

2 Answers 2


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,

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

you might use

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.


  • Would probably be good to point out that this is Utilities.sleep(milliseconds); sleep by itself is a syntax error.
    – jhwblender
    Feb 28 at 16:37
  • @jhwblender Thanks for your feedback. The sleep method is not the medular part of this answer, actually it's better to avoid to use it in custom functions. I will see how Tom improve it later.
    – Rubén
    Feb 28 at 17:33
  • @jhwblender answer updated.
    – Rubén
    Feb 28 at 18:34

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) {
  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.

  • The OP problem occurs with custom functions used in formulas. It's not clear how this answer address the OP case.
    – Rubén
    Feb 28 at 17:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.