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.

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:

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)):

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:

Curious what the balance of your bank accounts might be on any given day of the month? We’ve built a simple sheet to give you an estimate. Here we share the steps to get this Projected Balances Google Sheet up and running in your Tiller spreadsheets.

The Tiller Budget spreadsheet template brings your spending into focus for the current month, but what about reviewing spending across groups for previous months? You can easily add this customized view of your spending with a few simple steps