Service called too many times (Google Apps Script)

Question

I want to use Google Apps Script to create custom features for a spreadsheet. I made an extremely simple function:

function foo(){ return "bar"; }; 

The problem is that I need this feature in several hundred cells. When I insert the function =foo() into all of these cells, this function works in several cells, but in most cases I get this error: "Service is called too many times: spreadsheet. Try Utilities.sleep(1000) between calls."

[Screenshot here]

I think I don’t understand why this simple function is considered to be a call to spreadsheet services. I do not even request any data (except for the function itself). This is problem? And if so, is there a workaround? Custom functions can make Google Sheets infinitely more powerful, but this problem hinders the ability to use custom functions in multiple cells. Suggestions?

(PS - Using the Utilities.sleep() function, as suggested in the error message, does not help at all when all cells call their functions at the same time, it only slows down the speed at which individual cells re-call the function.)

Answer 1

In the Optimization section of the Script Application Feature Guide:

Each time a user-defined function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your table contains dozens (or hundreds or thousands!) Of calls to user-defined functions, this process can be quite slow.

Therefore, if you plan to use a custom function several times for a large range of data, consider changing the function so that it accepts the range as an input signal in the form of a two-dimensional array, and then returns a two-dimensional array that can overflow into the corresponding cells.

To do this, pass in an input representing the size of the array you want to return. When you start to execute your function, check if the input parameter is an array with input.map . If so, you can call a function for each item and return the entire collection.

So, in your case, like this:

 function foo(){ return "bar"; }; 

You can update the function as follows:

 function foo(input){ if (input.map) { // Test whether input is an array. return input.map(foo); // Recurse over array if so. } else { // do actual function work here return "bar"; } }; 

And then name it like this:

Answer 2

By calling a function in a spreadsheet, you call the Spreadsheet service, asking it to go back and forth to the server to run the results of your function. As a result, you have earned a couple of hundred requests in a very short period of time.

One job might be to add your function to multiple cells at a time. Of course, when you open the sheet again, you are likely to encounter the same problem.

Depending on what your function is trying to execute, it may be worth using the built-in spreadsheet functions. There is a lot of power. Writing a function that acts on a range of values, rather than one cell, may be another, better, option. It can be launched using a custom menu item or using the script manager.

Remember that batch actions are your best friend when it comes to working with spreadsheets.