Custom functions look like spreadsheet superpowers until one volatile formula starts refetching an API on every edit. Use them for pure calculation; move side effects (UrlFetch, writes, multi-sheet reads) behind a menu, button, or onEdit you control. Here's the checklist we use before shipping another =MYHELPER() into a client workbook.
TL;DR
- Custom functions recalculate when inputs change — and sometimes when Sheets feels like it.
- No
UrlFetchApp, no writes viaSpreadsheetApp, no services that need authorization beyond simple spreadsheet access inside a custom function. - Prefer a custom menu / button / time-driven trigger when the work has side effects or talks to the network.
- Keep custom functions pure: same inputs → same output, fast, deterministic.
- If the sheet "spins" after every keystroke, audit custom formulas first — not your Wi‑Fi.
Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.
What a custom function actually is
A custom function is just a Apps Script function you call from a cell like a formula:
/**
* @param {number} price
* @param {number} taxRate
* @return {number}
* @customfunction
*/
function ADDTAX(price, taxRate) {
return Number(price) * (1 + Number(taxRate));
}
Sheets treats it like =SUM(): it re-runs when dependent cells change. That is the whole product pitch — and the trap. You didn't schedule a job; you hung work off the recalculation graph.
When a custom formula is the right tool
Use a custom function when:
- The logic is pure math or string shaping (tax, slugify, simple lookups you already passed in as ranges)
- Inputs are cell values / ranges, not "go fetch Slack"
- Runtime is milliseconds, not "wait for an API"
- You're okay with Sheets deciding when it runs
Good fits: =NORMALIZEPHONE(A2), =SCORELEAD(B2:E2), small array helpers that would be painful as nested Sheets formulas.
When you should reach for a menu, button, or onEdit instead
Reach for a custom menu, drawing/button, checkbox + onEdit, or time-driven trigger when any of these are true:
| Smell | Why a formula is wrong |
|---|---|
Calls UrlFetchApp |
Network + quotas + unpredictable recalc = thrash |
Writes with SpreadsheetApp / Range.setValue |
Custom functions can't (and shouldn't) mutate the sheet |
| Needs OAuth beyond simple Sheets | Custom functions run with limited services |
| Must run "once when I say so" | Menus/buttons are explicit; formulas are ambient |
| Touches many sheets / large ranges for side effects | Recalc cost multiplies across open editors |
If the user should decide when it runs, it is not a formula. It is a command.
The failure mode: UrlFetch inside =MYAPI()
We've seen this movie. Someone builds:
/**
* @customfunction
*/
function FXRATE(from, to) {
var url = 'https://api.example.com/rates?from=' + from + '&to=' + to;
var res = UrlFetchApp.fetch(url); // looks clever…
var data = JSON.parse(res.getContentText());
return data.rate;
}
Then they fill a column with =FXRATE("USD","EUR") for 400 rows. Suddenly:
- Every edit nearby retriggers work (or feels like it)
- You burn UrlFetch quota on recalculation noise
- Two people with the sheet open amplify the pain
- Debugging becomes "why is my sheet loading?" instead of "is the rate wrong?"
Even when Sheets caches some custom-function results, you don't control the cache. Volatility, volatile neighbors (NOW(), RAND(), TODAY()), and structural changes will surprise you.
SpreadsheetApp inside custom functions
Reading the active spreadsheet in limited ways sometimes "works" in demos. Writing almost never does — and shouldn't. Custom functions are calculated in a context that is hostile to side effects on purpose.
If your helper needs to:
- Append a row
- Update another tab
- Toast the UI
- Grab Script Properties secrets for an API
…that helper belongs behind onOpen → custom menu, a button assigned to a function, or an installable onEdit / time trigger — not =DOALLTHEWORK(A2).
Volatility & neighbors that wake the monster
Custom functions don't live alone. Pair them with volatile Sheets functions and you get ambient thrash:
NOW(),TODAY(),RAND(),RANDBETWEEN()- Whole-column refs (
A:A) when you meantA2:A1000 - Array formulas that spill into regions your custom function also reads
Practical rule: pass tight ranges and primitive inputs. Don't make the custom function go hunting across the workbook for context it could receive as arguments.
Pattern A — pure custom function (keep)
/**
* Title-case a string for display labels.
*
* @param {string} text
* @return {string}
* @customfunction
*/
function TITLECASE(text) {
text = String(text || '');
return text.replace(/\w\S*/g, function (word) {
return word.charAt(0).toUpperCase() + word.slice(1).toLowerCase();
});
}
Fast. Deterministic. No services. Paste =TITLECASE(A2) freely.
Pattern B — menu command for side effects (prefer)
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Ops')
.addItem('Refresh FX rates', 'refreshFxRates')
.addToUi();
}
function refreshFxRates() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rates');
var res = UrlFetchApp.fetch('https://api.example.com/rates', {
muteHttpExceptions: true
});
if (res.getResponseCode() >= 400) {
throw new Error('FX API error: ' + res.getContentText().substring(0, 200));
}
var data = JSON.parse(res.getContentText());
// write once, on purpose
sheet.getRange('B2').setValue(data.USD_EUR);
sheet.getRange('B3').setValue(data.USD_GBP);
SpreadsheetApp.getActiveSpreadsheet().toast('Rates updated', 'Ops', 5);
}
Same business outcome, zero recalc thrash. Pair with retryUrlFetch if the API is flaky.
Pattern C — checkbox / onEdit when "run on this row" is the UX
function onEdit(e) {
var sh = e.range.getSheet();
if (sh.getName() !== 'Queue') return;
if (e.range.getColumn() !== 1) return; // column A = "Run?" checkbox
if (e.value !== 'TRUE') return;
var row = e.range.getRow();
processQueueRow_(sh, row);
e.range.setValue(false); // reset so it can run again
}
function processQueueRow_(sheet, row) {
var payload = sheet.getRange(row, 2, 1, 3).getValues()[0];
// side effects here — UrlFetch, append elsewhere, email, etc.
Logger.log(payload);
}
The user opts in per row. You still avoid a sheet full of live network formulas.
Decision checklist (print this)
Before you ship =MYTHING():
- Does it need the network? → Menu / trigger / onEdit. Not a custom function.
- Does it write anywhere? → Same — command, not formula.
- Is it pure and fast? → Custom function is fine.
- Will someone fill a whole column with it? → Re-check cost × rows × editors.
- Any volatile neighbors? → Isolate or remove them.
- Could a built-in Sheets formula do it? → Prefer built-ins; less code to own.
If you hesitate on (1) or (2), don't rationalize — move it out of the formula bar.
Soft upgrade path for existing thrash
Already stuck with =FXRATE() everywhere?
- Freeze values (copy → paste special → values) for historical rows
- Add a Refresh menu that writes a rates table once
- Point formulas at the local table (
VLOOKUP/XLOOKUP) instead of the network - Delete the network custom function so it can't come back via autocomplete
That migration is boring and it works. Boring is the brand.
Keep helpers handy
If you're collecting these patterns across client files, NitroGAS keeps pasteable snippets in the Apps Script editor — Co-Pilot's there when you need to adapt a menu vs formula split. Free extension; Co-Pilot optional. The decision checklist above stands on its own either way.
Closing checklist
- Custom functions are pure, fast, and argument-driven
- No UrlFetch / writes / secret reads inside
@customfunction - Side-effect work lives on a menu, button, trigger, or onEdit
- Whole-column network formulas were rewritten to a refresh + lookup table
- Volatile neighbors aren't waking expensive helpers
Custom functions are great spices. They make terrible entrees. Keep the thrash out of the recalculation graph — your future self (and every open editor) will thank you.
Happy Coding!
