Why does Ctrl+Alt+F9 work but the formula never updates on its own?

Ctrl+Alt+F9 forces a full recalculate of all formulas, bypassing the cache. Without it, Sheets only reruns a custom function when its argument values change. If your function reads external data without that data appearing as an argument, Sheets has no way to know a refresh is needed.

Can I use NOW() directly as the dummy argument without wrapping it?

You can, but bare NOW() updates on every sheet interaction, which generates a new execution every time any cell is edited or the sheet is opened. INT(NOW()) coarsens it to once per day; FLOOR(NOW(), 1/24) gives hourly granularity. Pick the coarsest interval that still satisfies your freshness requirement.

Does the dummy argument affect my 30-minute daily execution quota?

Yes. Every cache miss triggers a real execution, and executions count against the per-spreadsheet quota (6 minutes per execution, 30 minutes per day on consumer accounts; higher on Workspace). A bare NOW() argument with a busy spreadsheet can exhaust that quota in an afternoon. The trigger-plus-refresh-cell pattern gives you direct control over how often you burn quota.

My function needs to read from an external API. Is there a better pattern than a custom function?

For external API calls, a time-driven trigger that writes results into a sheet range and then reads that range in the formula is almost always better. Custom functions have a 30-second timeout per call and cannot use authorized services. A trigger runs with full authorization, has a 6-minute timeout, and you control the schedule exactly.

// Sheets · Apps Script

Force a custom function to recalculate in Google Sheets.

Custom functions in Google Sheets cache their results and only re-run when their arguments change. Learn how to break that cache with a dummy NOW() argument or a time-driven trigger that writes a refresh token.

“I wrote a custom Apps Script function but it keeps returning a stale value even after the underlying data it reads changes.”

The script

copy · paste · trigger

refreshToken.gs

Apps Script

// Writes a timestamp to a named cell so volatile custom functions recalculate.
// Wire a time-driven trigger to runRefreshToken() every N minutes.

const REFRESH_CELL = 'A1'; // cell holding the refresh token
const REFRESH_SHEET = 'Config';

function runRefreshToken() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(REFRESH_SHEET);
  const cell = sheet.getRange(REFRESH_CELL);
  cell.setValue(new Date().getTime());
}

// In your custom function signature, accept the token but never use it:
// =MYFUNC(A2, Config!A1)
function myFunc(data, _refreshToken) {
  // _refreshToken is intentionally ignored; its change forces recalculation
  return doExpensiveWork(data);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why the cache exists and why it bites you

Google Sheets caches the return value of every custom function keyed against its exact arguments. If you call =MYFUNC(A2) and A2 does not change, Sheets returns the cached result without ever running your script again. That is by design: custom functions count against a per-spreadsheet execution quota (30 seconds per call, 30 minutes per day on the free tier), and Sheets protects that budget aggressively.

The cache becomes a problem the moment your function reads something that is not an argument — a URL, a named range, a value fetched from UrlFetchApp, or the current time. You change the external thing, nothing in the cell formula changes, Sheets never fires, and you stare at yesterday's number.

The first time I hit this I spent an hour debugging the script before realizing the script was never running at all. Ctrl+Alt+F9 (recalculate all formulas) forces it once, but that is a manual step, not a solution.

The dummy-argument pattern for on-demand refresh

The simplest fix is to pass a volatile argument that changes when you want a refresh. =NOW() is the obvious choice: wrap it in INT() to limit noise, so =MYFUNC(A2, INT(NOW())) changes once per day without thrashing on every display update.

Your function receives the number but ignores it. The only purpose it serves is to make Sheets think the inputs changed, which clears the cache and triggers a real execution. Keep the parameter name prefixed with an underscore (like _refreshToken) to signal to readers that it is intentional dead code, not a mistake.

The downside: NOW() recalculates whenever the sheet is opened or any other volatile formula fires, which can generate more executions than you intended. If your function calls an external API, this adds up fast. Use INT(NOW()) or FLOOR(NOW(), 1/24) to coarsen the granularity to days or hours respectively.

Automating refresh with a time-driven trigger

For production use, the trigger approach is cleaner. A time-driven trigger calls runRefreshToken() on a schedule (every 5 minutes, every hour, whatever fits). That function writes the current epoch timestamp into a fixed cell on a Config sheet. Every custom function that needs to be volatile takes that cell as an ignored trailing argument.

Set up the trigger in the Apps Script editor under Triggers (the clock icon), or do it in code with ScriptApp.newTrigger('runRefreshToken').timeBased().everyMinutes(10).create(). Either way you get a predictable, quota-friendly refresh cadence instead of the unpredictable bursts that NOW() can cause.

One practical note: the Config sheet cell needs to exist before the first trigger fires, or getRange() will succeed but the custom function will receive an empty value. Pre-populate it manually once, or add a guard at the top of runRefreshToken() that initializes the cell to 0 if it is blank.

What still will not work

Custom functions cannot call services that require user authorization, including most SpreadsheetApp write methods, MailApp, and Calendar. If your function tries to call an authorized service to fetch data, it will throw an error about missing permissions. Move that logic into a regular function called by a trigger, write the result to the sheet, and read the cell from your formula instead.

There is also no way to programmatically invalidate the cache for a specific custom function without changing its arguments or triggering a full recalculate. The dummy-argument pattern is not a workaround for a missing API — it is the documented approach.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does Ctrl+Alt+F9 work but the formula never updates on its own?: Ctrl+Alt+F9 forces a full recalculate of all formulas, bypassing the cache. Without it, Sheets only reruns a custom function when its argument values change. If your function reads external data without that data appearing as an argument, Sheets has no way to know a refresh is needed.
Can I use NOW() directly as the dummy argument without wrapping it?: You can, but bare NOW() updates on every sheet interaction, which generates a new execution every time any cell is edited or the sheet is opened. INT(NOW()) coarsens it to once per day; FLOOR(NOW(), 1/24) gives hourly granularity. Pick the coarsest interval that still satisfies your freshness requirement.
Does the dummy argument affect my 30-minute daily execution quota?: Yes. Every cache miss triggers a real execution, and executions count against the per-spreadsheet quota (6 minutes per execution, 30 minutes per day on consumer accounts; higher on Workspace). A bare NOW() argument with a busy spreadsheet can exhaust that quota in an afternoon. The trigger-plus-refresh-cell pattern gives you direct control over how often you burn quota.
My function needs to read from an external API. Is there a better pattern than a custom function?: For external API calls, a time-driven trigger that writes results into a sheet range and then reads that range in the formula is almost always better. Custom functions have a 30-second timeout per call and cannot use authorized services. A trigger runs with full authorization, has a 6-minute timeout, and you control the schedule exactly.

// one good script a week