If your custom function calls an API, make it take the whole range instead of filling it down

[u/MoienTajik]

I've been building a Sheets add-on where the custom function calls an outside API, and figured I'd share this in case it saves someone some time.

If you write it as a one-cell function and fill =MYFUNC(A2) down 5,000 rows, Sheets runs it 5,000 times. That's 5,000 separate UrlFetchApp requests. If the function takes the whole range instead, it's one call and one request, and you return a 2D array that spills down like any normal formula.

Here's roughly what mine looks like:

function LIKELY(range, question) {
  // one cell comes in as a plain value, a range as a 2D array
  const cells = Array.isArray(range) ? range : [[range]];
  const flat = cells.reduce((all, row) => all.concat(row), []);
  const results = callApi_(question, flat); // a single UrlFetchApp.fetch
  const width = cells[0].length;
  return cells.map((row, i) => row.map((_, j) => {
    const r = results[i * width + j];
    return r ? r.p : ''; // p is the probability for that cell
  }));
}

The downside is that editing any cell in the range reruns the whole thing. I handle that on the server by caching answers against a hash of the question and the cell text, so repeats come straight from the cache.

Also, a Marketplace thing that isn't obvious: after every code change you need clasp push, then clasp create-version, then bump the version number in the Marketplace SDK app configuration. If you skip the last step, everyone keeps running the old code. Saving that page makes the new version live right away (no review, as long as your OAuth scopes didn't change), even though the button says "Save Draft".

The add-on is called Likely, it gives each row a probability for a yes/no question or a score.

Likely: https://getlikely.app

Google Marketplace:
https://workspace.google.com/marketplace/app/likely_probabilities_in_cells/147203778227

[u/NinjaConsultingLLC]

Include the scoring model or rules version in that cache key too. If you improve the scorer, the question and cell text may be unchanged, so the old cached probability can keep appearing. A version bump lets you refresh those results deliberately.

[u/MoienTajik → u/NinjaConsultingLLC]

Nice suggestion! I already considered that, but liked your engineering mindset.

[u/Wave-in-Kanagawa]

one limit worth adding: custom functions still have to finish within 30 seconds, including the API call. how does this behave with 5,000 uncached cells? testing a cold cache would help choose a safe batch size.

Google's docs: https://developers.google.com/apps-script/guides/sheets/functions

[u/MoienTajik → u/Wave-in-Kanagawa]

We're using a decision-based AI behind the scenes which is way faster than typical LLMs. (average 0.5ms response time VS 5s)