Every Apps Script project eventually grows “settings.” The trouble isn’t storing them — it’s storing them on the wrong shelf. API tokens end up in a visible tab. A 50-row client lookup ends up JSON-stuffed into one property. Exchange rates get cached for a week and nobody knows why totals are stale.
Three shelves cover almost everything:
| Shelf | Best for | Lifetime | Who edits |
|---|---|---|---|
| CacheService | Expensive lookups, API responses, “good for 10 minutes” | Minutes → up to 6 hours (TTL) | Script only |
| Script / Document Properties | Small secrets, ids, feature flags, environment switches | Until you change it | You (developer) |
| Config sheet | Tables humans edit: client → folder, thresholds, email lists | Until someone edits the sheet | Ops / non-devs |
TL;DR
- Cache anything you can safely recompute; set a TTL; never store the only copy of truth there
- Properties for small secrets and flags — tokens, sheet ids,
dryRun=true. We already went deep on this in Script Properties for bootstrapper config — use that for the how-to; this guide is about choosing - Config sheet for tables people should change without opening the script editor
- Anti-pattern alarm: secrets on a sheet, huge tables in properties, business data in cache
- Soft CTA: ask Co-Pilot to annotate why each value lives where it does
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.
CacheService — short-lived and disposable
Cache is a speed bump remover. Use it when:
- The value is expensive to get (UrlFetch, big
getValues, slow API) - It’s safe to recompute if the cache is empty or evicted
- A little staleness is OK (you pick the TTL)
function getRatesCached_() {
var cache = CacheService.getScriptCache();
var hit = cache.get('fxRates');
if (hit) return JSON.parse(hit);
var rates = fetchRatesFromApi_(); // expensive
cache.put('fxRates', JSON.stringify(rates), 600); // 10 minutes
return rates;
}
Not for: secrets you can’t regenerate, “the” customer list, anything you’d be sad to lose. Cache can be evicted early; entries have size limits (keep values small; about 100KB per entry is the ceiling).
Script / Document Properties — small, private-ish, stable
Properties are where environment knobs live:
- API tokens and webhook URLs (see the Script Properties guide for helpers)
- Target spreadsheet / folder ids
- Feature flags:
enableEmails,dryRun - Per-document settings (Document Properties) for bound scripts cloned per client
Not for: big tables, frequently changing counters (write limits), anything ops should edit without you.
Config sheet — tables humans own
A Config (or Settings) tab is ideal when:
- Non-developers need to change values (thresholds, notify emails, client → folder map)
- The data is tabular (many rows, several columns)
- You want visibility: filters, comments, “who changed this?” via sheet history
function readConfigTable_() {
var rows = SpreadsheetApp.getActive().getSheetByName('Config').getDataRange().getValues();
var headers = rows.shift();
return rows.map(function (r) {
var o = {};
headers.forEach(function (h, i) { o[h] = r[i]; });
return o;
});
}
Cache the parsed table if you read it on every edit — then you’re using two shelves on purpose: sheet = source of truth, cache = speed.
Not for: secrets. Anyone with edit access can see and copy them.
Decision table (pin this)
| Question | Shelf |
|---|---|
| Will a teammate edit it without the script editor? | Config sheet |
| Is it a secret or an id you’d rotate? | Properties |
| Is it expensive to compute and OK if it expires? | Cache |
| Is it a 200-row lookup table? | Config sheet (+ optional cache) |
Is it dryRun / stagingMode? |
Properties |
| Is it an API response you hit 50 times per run? | Cache |
Anti-patterns we keep seeing
- Token in Config!B2 — visible, copyable, travels with every copy of the file. Move to Properties.
- JSON megablob in one property — hard to diff, easy to clobber. Split keys, or move tables to a sheet.
- Cache as a database — “it’s been in cache for weeks” until it isn’t. Cache is not durable storage.
- Human-editable table in Properties — ops can’t see or change it; you become the bottleneck.
- No TTL thinking — caching exchange rates for 6 hours on a pricing sheet that updates hourly.
Soft Co-Pilot note
NitroGAS keeps cache and properties helpers a search away in the editor. Paste your config block and ask Co-Pilot to annotate why each value lives where it does — “cache: recomputable FX”, “property: webhook secret”, “sheet: ops-owned thresholds.” Future you (and the next contractor) will thank you. Free core; Co-Pilot is a pass for when you want the annotations fast.
Closing checklist
- No secrets on a visible sheet
- Every cached value has a TTL and a recompute path
- Human-edited tables live on a Config sheet
- Properties hold small ids / flags / tokens only
- A short comment next to each value says which shelf and why
Happy Coding!
