Service Invoked Too Many Times (Google Apps Script)

Question

I want to use Google Apps Script to make custom functions for a spreadsheet. I've made an extremely simple function:

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

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

[Screenshot here]

I guess I don't understand why this function, simple as it is, is considered an invocation of the Spreadsheet Services. I'm not even requesting any data (except for the function itself). Is that the problem? And if so, is there a workaround? Custom functions could make Google Spreadsheets infinitely more powerful, but this problem hamstrings the possibility of using a custom function in multiple cells. Suggestions?

(P.S. -- Using the Utilities.sleep() function as suggested by the error message doesn't help at all when all of the cells call their functions simultaneously; it only slows the rate at which individual cells repeatedly call the function.)

Answer 1

According to the Optimization section on the Apps Script Function Guide:

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

Consequently, if you plan to use a custom function multiple times on a large range of data, consider modifying the function so that it accepts a range as input in the form of a two-dimensional array, then returns a two-dimensional array that can overflow into the appropriate cells.

To do this, pass in an input that represents the size of the array you'd like to return. When you start executing your function check if the input parameter is an array with input.map. If it is, you can call the the function on each item and return that entire collection.

So in your case like this:

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

You can update the function like this:

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 call it like this:

Answer 2

By calling the function in the spreadsheet, you are invoking the Spreadsheet service by asking it to go round-trip to the server to run the results of your function. As a result, you have make a couple hundred requests in a very short period of time.

One work around might be to add your function a few cells at a time. Of course, when you subsequently open the sheet again, you will probably run into the same problem.

Depending on what your function is trying to accomplish, it might be worth using the built in spreadsheet functions. There is a lot of power there. Writing a function that acts on a range of values instead of a single cell might be another, better, option. It could be triggered through a custom menu item, or by using the script manager.

Keep in mind, batch actions are your best friend when it comes to working with spreadsheets.