“Service invoked too many times in a short time” in Google Sheets custom formula

Adding a few lines of code to Google Sheets custom formulas can give callers a method to avoid “Service invoked too many times in a short time” errors.  

For instance, the custom formula that gave the errors in the figure above was implemented in this very simple Google Apps Script function that computes the inverse of the input value in the provided cell, and copy-and-pasted into the 6,000 cells in Column B. Yuck.

function TillerInverse(input) {
 if(input) return 1/input;
}

For more information on custom formulas in Google Sheets, see https://developers.google.com/apps-script/guides/sheets/functions.

To turn this existing simple Google Sheets custom formula that accepts a single cell value into one that automatically also accepts and handles a range, you can add a few lines of code to check for the presence of a ‘map’ function on the input, and if present, use map() to call the custom function recursively:

function TillerInverse(input) {
 if (typeof input.map === “function”) {
   // It’s a range — treat it as such
return input.map(TillerInverse);
}  
 If (input) return 1/input;
}

Detailed information on map() can be found in the JavaScript here. For our purposes, any input that has map() should be treated like a range, rather than a discrete value.

This improved TillerInverse() will still work if passed a single cell (i.e. TillerInverse(A5981)), as it originally did, and will also work if passed an entire column (i.e. TillerInverse(A2:A)) . Instead of invoking the wrath of the Google Quota checker for calling our custom function 6,000 times by copy-and-pasting it down column B, callers can now make a single call, in cell B2. The rest of column B in fact must be empty so the function has room to fill in the results. It’s also lightning-fast – we very quickly get the results we are after:

This technique also works when the caller passes in a two-dimensional range (i.e. TillerInverse(A2:C4)):

You can copy this sheet and play around with it (and the attached Google Apps Script) by clicking here, or by opening https://docs.google.com/spreadsheets/d/1dTrApUGCd0er7fwg_Texm3iOkWyqwuAOzggwaNxgZ3M/view#gid=0 and making a copy.

I removed a few thousand of the original calls that demonstrate the initial error, since it makes the sheet SSSSLLLOOOOWWWWW — and also I kept picturing this poster hanging up somewhere on the Google campus: