DEV Community: Hayrullah Kar

The ledger asks the model to show its work before it counts the money

Hayrullah Kar — Sun, 06 Sep 2026 12:48:29 +0000

This is a submission for Weekend Challenge: Generosity Edition

What I Built

A donation ledger for a group too small to buy software. It is a Google Sheet,
some Apps Script, and one public page a donor can open.

The group I had in mind is the kind that exists on every street: a neighbourhood
fund, a school parents' group, a committee that collects for winter coats.
Money arrives over WhatsApp and leaves in cash, and somebody keeps it in a
notebook. The arithmetic is not the hard part. The hard part arrives three
months later when a donor asks where their money went, and answering needs the
notebook, the person holding it, and an afternoon.

Software for this exists and is priced for organisations with a finance team.
So the ledger stays in a spreadsheet a volunteer already knows how to open, and
the only thing added is what a spreadsheet cannot do alone: read messy human
messages, refuse to trust its own reading, and publish the page that answers
the question before it is asked.

Demo

The public page a donor opens →

That page is the deployed page, byte for byte, with one line changed: where the
Apps Script version writes <?= data ?>, the demo fetches the same JSON from a
file so you can read it without a Google account. The JSON is produced by
running the sample month through the same recordEntry() and publicView() the
real script uses, so if the ledger rules change, the demo changes with them or
the build fails.

The sample month deliberately includes the things that go wrong: a receipt two
volunteers forwarded, a donation typed with one zero too many and later
corrected, and a reading the checks refused to trust.

What the model read, and whether it was allowed to count →

The ledger page shows the result. This one shows the part worth showing. Pick any
of six real donation messages and it highlights the exact characters Gemini says
it read the amount from, lists the three checks with their outcomes, and says why
the row was posted or held. It is fed by a recorded run, not by a description of
one, down to how many requests had to be retried after a 503.

Code

MageSheet / open-donation-ledger

A transparent donation ledger for small charities: Google Sheets + Apps Script + Gemini, with a public page donors can open.

Open Donation Ledger

A donation ledger for a group too small to buy software: a Google Sheet, some Apps Script, and one public page a donor can open. Donations arrive as WhatsApp messages and photographs of bank receipts, Gemini reads them, and two checks decide whether a reading is trustworthy enough to count.

Built for the DEV Weekend Challenge: Generosity Edition.

Live demo of the public page →

The problem this is actually about

A neighbourhood fund, a school parents' group, a mosque committee: money comes in over WhatsApp and goes out in cash, and somebody keeps it in a notebook The arithmetic is not the hard part. The hard part arrives three months later when a donor asks where their money went and nobody can answer without the notebook, the person who holds it, and an afternoon.

Software exists for this. It is priced for organisations with a…

View on GitHub

How I Built It

The part I actually care about

Real messages look like this:

abi 250 tl yolladım montlara yazın
Sending 40 EUR for the winter fund, please keep me anonymous
bugün 1.250,50 TL havale ettim, dekontu sonra atarım

Gemini reads those well. That was never the problem. The problem is that a
wrong number looks exactly like a right one, and this number ends up on a
public page next to a stranger's name. "Mostly accurate" is a fine standard for
a summary and a terrible one for a ledger.

So the model is not trusted on its own. It has to show its work.

For a text message, the schema requires an amount_quote: the exact characters
the model read the amount from, copied out of the message. Then the code checks
that this substring actually occurs in the message, and that its digits are the
amount that was returned. A model that quotes something it invented fails the
first check. A model that silently converted a currency or moved a decimal point
fails the second.

function verifyQuote(sourceText, quote, amount) {
  if (!quote) return { ok: false, reason: 'model quoted nothing' };

  var haystack = String(sourceText).toLowerCase().replace(/\s+/g, ' ');
  var needle = String(quote).toLowerCase().replace(/\s+/g, ' ').trim();
  if (haystack.indexOf(needle) === -1) {
    return { ok: false, reason: 'quote "' + quote + '" does not occur in the message' };
  }

  var quoted = parseQuotedNumber(needle);
  if (!isFinite(quoted)) return { ok: false, reason: 'quote carries no number' };
  if (Math.abs(quoted - Number(amount)) > 0.005) {
    return { ok: false, reason: 'quote says ' + quoted + ' but amount says ' + amount };
  }

  return { ok: true, reason: '' };
}

A receipt photograph has no source text to check a quote against, so it gets a
different check: the image is read twice with two differently framed prompts,
and the readings have to agree on both amount and currency. One prompt asks for
the transferred amount. The other asks the model to read every number on the
receipt and then say which one is the transfer. A blurry or cropped page tends
to produce two different answers, and two different answers means a person
looks at it.

Nothing that fails a check is discarded. It is recorded with
status: needs-review, so the money is not lost, it just does not count until
someone has checked it. The public page says how many rows are waiting rather
than quietly leaving them out.

What it actually did

Run against gemini-3.6-flash, temperature 0, structured output, on six
messages:

  message : abi 250 tl yolladım montlara yazın
  read    : 250 TRY
  quote   : "250 tl"
  verdict : POSTED

  message : bugün 1.250,50 TL havale ettim, dekontu sonra atarım
  read    : 1250.5 TRY
  quote   : "1.250,50 TL"
  verdict : POSTED

  message : yarın bir miktar göndereceğim inşallah
  read    : 0 TRY
  quote   : ""
  verdict : NEEDS REVIEW - model quoted nothing

  message : I gave 100 dollars last week, that is about 3400 lira right?
  read    : 100 USD
  quote   : "100 dollars"
  verdict : POSTED

5/6 posted straight to the ledger, 1 held for a human.

Two of those are the interesting ones. "yarın bir miktar göndereceğim" is a
promise, not a donation; it produced no quote and went to review instead of
becoming a zero-lira row. And the last message names two numbers, 100 dollars
and a 3400 lira conversion the sender did in their head. The ledger got 100 USD,
which is the money that actually moved.

That run also took a 503 high demand on its first call and recovered on the
retry, which is why both the local script and the Apps Script side wrap the call
in backoff. A shared endpoint answering "not now" is normal traffic, not a
failure to extract.

Nothing is edited after the fact

There is no update path in Ledger.gs. A mistake is fixed by appending a
correction row that names the row it corrects, and the totals net the two. Both
rows stay on the record, because a ledger whose history can change quietly is
exactly what the donor was worried about.

Three more rules the code enforces rather than trusts:

A resent message is recorded once. The idempotency key comes from the transport's own message id, so a receipt three volunteers forwarded is one row, and the third volunteer is not told something went wrong.
A donor who did not ask to be named is Anonymous on the page, even though the ledger still knows who they are. A name printed on a bank receipt is not consent to publish it.
One fund holding two currencies is never added into one number. 590 TRY and 40 EUR is not 630 of anything.

That last rule is there because it was a bug. The demo page is built out of the
same functions the tests exercise, and building it printed a fund total of 630
where 590 TRY and 40 EUR had been summed. It would have looked completely
plausible on the page.

Testing Apps Script without Apps Script

Apps Script has no modules: every file shares one global scope, which is why
Extract.gs can call a function defined in Ledger.gs. Requiring the files
separately in node would hide that, so the test harness evaluates them into a
single vm context instead, and the tests exercise the same globals the
deployed script does.

Side effects go through a deps object that is a sheet in production and an
array in the tests, so all 27 tests run with no network, no spreadsheet and no
API key. The live model check is a separate script you run by hand, because
whether the model behaves is a different question from whether the ledger rules
hold.

What it is not

Not accounting software. Not a fraud control either: it checks that a reading
matches its source, which is a different question from whether the source is
honest. And not a security boundary, since anyone who can open the sheet can
read the donor names the public page hides.

Prize Categories

Best use of Google AI. Gemini does the reading, with structured output and a
response schema so the model returns the shape instead of a paragraph containing
the shape. The part I would defend is not the extraction, it is the design
around it: requiring a verbatim quote and verifying it against the source turns
"the model is usually right" into something a treasurer can act on, and the
two-pass agreement check does the same job for images, where no quote is
possible.

Every line of it runs in a spreadsheet a volunteer already has, which was the
point. The generous thing was never the software.

Ops Per Run, Not Message Volume, Decides Rent or Build

Hayrullah Kar — Sun, 06 Sep 2026 10:35:32 +0000

A WhatsApp order bot at 2,000 messages a month looks like an obvious candidate to own rather than rent. I priced one that way and the arithmetic said the opposite: at that volume the rented plan never paid back the build. The number that decided it was not the message count. It was how many billable operations a single message consumes — I had assumed one, and a real run consumed four.

Both of those numbers are measurable in an afternoon, and until you have them the rent-or-build argument is two people trading adjectives. Here is the instrumentation that produces them, the cost model they feed, and what the payback actually came out to.

Sticker prices decide nothing

The usual comparison lines up monthly fees. On any workflow that succeeds, the fee is the smallest term in the bill. Two numbers move it:

Runs per month — how often the trigger fires.
Billable operations per run — how many steps each run consumes.

The first one people know. The second one people guess, and they guess low, because a "task" on a per-operation platform is a step, not a message. Parsing the message is one. Appending the row is one. Sending the reply is one. The filter that decided whether to continue is one. A run that a filter stops halfway still consumed everything up to the filter, and a step that fails and retries bills for both attempts.

Four operations instead of one is a four-times bill at the same message volume. That is the whole difference between a build that pays back inside a year and one that never does, and it is measurable rather than arguable.

Step 1: count the operations in the run you already have

Wrap each billable step so the count comes from a real execution rather than from a diagram. Two details matter more than they look: the counter records the attempt, not the success, and the log is written in a finally block so a run that dies halfway still reports what it consumed.

// Wraps each billable step and records the attempt. A retry is a
// second billable operation on a per-operation platform, so it is
// pushed under its own name rather than folded into the first.
function makeStepCounter(ops) {
  return function step(name, fn) {
    ops.push(name);
    try {
      return fn();
    } catch (err) {
      ops.push(name + ':retry');
      return fn();
    }
  };
}

// The run itself. Note the finally: a run that a filter stops, or
// one that throws on the last step, was still billed for the steps
// it reached. Logging only completed runs undercounts the bill.
function runOrder(msg, deps) {
  var ops = [];
  var step = makeStepCounter(ops);
  try {
    var parsed = step('parse', function () {
      return deps.parseOrder(msg);
    });
    if (!parsed.isOrder) return null;
    step('append', function () {
      return deps.appendOrder(parsed);
    });
    step('reply', function () {
      return deps.sendReply(parsed);
    });
    return parsed;
  } finally {
    deps.logRun(ops);
  }
}

// Turn the logged runs into the two numbers the cost model needs.
// Use the mean, not the happy path: the filtered and failed runs
// are the ones that quietly move the average.
function usageFromLog(rows) {
  if (!rows.length) throw new Error('no runs logged yet');
  var total = 0;
  for (var i = 0; i < rows.length; i++) total += rows[i].ops;
  return { runs: rows.length, opsPerRun: total / rows.length };
}

function monthlyUsage(rows, daysObserved) {
  if (!(daysObserved > 0)) throw new Error('daysObserved must be > 0');
  var seen = usageFromLog(rows);
  return {
    runs: Math.round((seen.runs / daysObserved) * 30),
    opsPerRun: seen.opsPerRun
  };
}

In Apps Script, deps.logRun is a one-line appendRow onto a RunLog sheet — timestamp, ops.length, and ops.join('|') so you can see afterwards which step was the expensive one. Run it for a week on the real traffic. A week of real messages beats any estimate, because the filtered runs and the retries show up in it and they never show up in an estimate.

Step 2: the three shapes a bill can take

Rented automation bills in one of two shapes, and owned code bills in a third. The shape, not the price, is what makes the numbers move differently as you grow.

// Fill these from your own invoice. The numbers below are
// placeholders chosen to show the shapes, not quotes from any
// vendor, and every platform changes its plans -- read your bill.
var PLANS = {
  perOperation: { kind: 'op',  fee: 29, included: 10000, over: 0.002 },
  perExecution: { kind: 'run', fee: 24, included: 5000,  over: 0.004 },
  owned:        { kind: 'own', maintain: 40, build: 1800 }
};

// Recurring monthly cost, deliberately excluding model tokens.
// You pay the same tokens whichever side you land on, so leaving
// them in flattens the difference you are trying to measure.
function monthlyCost(plan, usage) {
  if (plan.kind === 'own') return plan.maintain;
  var billable = plan.kind === 'op'
    ? usage.runs * usage.opsPerRun
    : usage.runs;
  var extra = Math.max(0, billable - plan.included);
  return plan.fee + extra * plan.over;
}

The kind field is the whole point. On a per-operation plan, opsPerRun multiplies straight into the bill. On an execution-billed plan it does not appear at all — a run with twelve steps costs the same as a run with one. Two platforms with near-identical sticker prices produce completely different curves on the same workflow, and which one is cheaper flips depending on how chatty your run is.

The owned side is a flat maintenance figure plus a one-time build. Leaving the maintenance line at zero is the single most common way these comparisons get faked. Someone patches the code when an API changes; put a number on that person's time or the payback you compute is fiction.

Step 3: the payback, and what it came out to

// Whole months until the build pays for itself. Returns null when
// renting is simply cheaper -- that is a real answer, not an error
// case, and at low volume it is the usual one.
function paybackMonths(rented, owned, usage) {
  var saved = monthlyCost(rented, usage) - monthlyCost(owned, usage);
  if (saved <= 0) return null;
  return Math.ceil(owned.build / saved);
}

Running that over the measured usage, against the placeholder per-operation plan above:

Runs / month	Ops per run	Billable ops	Rent	Own	Payback
2,000	4	8,000	$29	$40	never
5,000	4	20,000	$49	$40	200 months
10,000	4	40,000	$89	$40	37 months
20,000	4	80,000	$169	$40	14 months
20,000	2	40,000	$89	$40	37 months
20,000	1	20,000	$49	$40	200 months

Two things fall out of that table, and neither is what the rent-or-build pitch usually claims.

The first is that at the volume most small businesses actually run — the 2,000-messages-a-month order bot — the rented plan wins outright. The included pool swallows the whole workflow and the subscription is less than the maintenance. There is no payback to compute, because there is nothing being saved.

The second is the interesting one. Compare the last three rows: same 20,000 runs a month, payback moves from 14 months to 200 months purely on operations per run. The message volume, the number everyone quotes first, is held constant across all three. The number that actually decided it is the one nobody measures.

Pitfalls

Counting messages instead of steps. The most expensive mistake here is silent: you divide the message count into the included pool, conclude you will never hit overage, and discover the multiplier on the invoice. Count from a run, not from the trigger.
Logging only successful runs. Filtered and crashed runs consumed operations too. Without the finally, they vanish from your log and your average lands low.
Forgetting that retries bill twice. A flaky third-party endpoint that retries once on a tenth of your runs adds ten percent to the operation count. If you are already handling retries with backoff — and you should be, see UrlFetchApp quotas, retries and backoff — then instrument the retry path, because your bill counts what your backoff loop does.
Leaving tokens in the comparison. Both sides pay the model provider the same tokens for the same prompts. Including them in the rent-or-build math shrinks a real difference into noise. Exclude them from the comparison, then add them back to the budget, where they belong.
A zero maintenance line on the owned side. It makes every payback look good. Price the hour or two a month somebody spends on it, or you have not made a comparison at all.
Comparing against a plan you have not read. Whether filters, routers and error handlers are billable operations varies by platform and by plan. That single detail moves opsPerRun more than anything in your own code does.

The one thing worth taking away

Instrument the workflow for a week before you argue about it. Ops per run is the term that decides rent-or-build, it is off by a factor of four when you guess it, and it costs an afternoon to measure properly. If the measurement says rent, rent — that is what a decision procedure is for.

The full decision framework this sits inside, including the cases where a rented platform is the right answer regardless of the arithmetic, is on the MageSheet blog.

One callModel() for Gemini, OpenAI and Claude in Apps Script

Hayrullah Kar — Tue, 01 Sep 2026 15:54:32 +0000

A lead-classification job I built ran every inbound row through the flagship model because that was the model already wired up. 60,000 calls a month, about 800 input and 60 output tokens each. On claude-opus-5 list rates that arithmetic comes to $330 a month. The same calls on gemini-2.5-flash-lite come to $6.24. The prompt didn't change, the sheet didn't change, the accuracy on that particular task didn't measurably change — only which model answered.

Getting from $330 to $6.24 means one call site can reach more than one provider. The usual framing is that this is easy, because from Apps Script all three are the same shape: a UrlFetchApp.fetch() to a REST endpoint with your prompt in the body and your key in a header. That's true at the wire level. At the code level three specific things differ, and each one fails in a way that doesn't raise.

Here's the router I ended up with, and the three places it has to know the difference.

What actually differs between the three

Not the transport. These:

Where the system prompt goes. Gemini takes it as a top-level systemInstruction object. OpenAI takes it as a message with role: "system" at the front of messages. Claude takes it as a top-level system string, and a system message inside messages is a different feature with different rules. Send a Gemini-shaped body to OpenAI and the system prompt is silently absent — no error, just a model that ignores instructions you're certain you sent.

Where the key goes. Gemini reads x-goog-api-key. OpenAI reads Authorization: Bearer. Claude reads x-api-key and additionally requires an anthropic-version header that the other two have no equivalent for.

Whether the output cap is optional. generationConfig.maxOutputTokens and max_completion_tokens are both optional. Claude's max_tokens is required — a helper that treats it as optional works against two providers and returns a 400 against the third.

The money layer

One object holds the routing vocabulary and the prices, so a route can never name a model the cost function doesn't know:

// One registry: model id -> provider + list price per 1M tokens,
// in USD. These are published list rates read in September 2026;
// re-check them before you put a number in a business case.
// This object is the only place you edit when a price moves.
var MODELS = {
  'gemini-2.5-flash-lite': { p: 'gemini', in: 0.10, out: 0.40 },
  'gemini-3.1-pro':        { p: 'gemini', in: 2.00, out: 12.00 },
  'gpt-5.6-luna':          { p: 'openai', in: 0.20, out: 1.20 },
  'gpt-5.6-sol':           { p: 'openai', in: 5.00, out: 30.00 },
  'claude-haiku-4-5':      { p: 'claude', in: 1.00, out: 5.00 },
  'claude-opus-5':         { p: 'claude', in: 5.00, out: 25.00 }
};

// Task -> model id. It returns the id, not a label like 'cheap',
// so the string that routes is the same string that keys MODELS.
function pickModel(task) {
  if (task === 'classify' || task === 'tag') {
    return 'gemini-2.5-flash-lite';
  }
  if (task === 'extract') return 'gpt-5.6-luna';
  if (task === 'reply') return 'claude-haiku-4-5';
  if (task === 'audit') return 'claude-opus-5';
  return 'gemini-2.5-flash-lite';
}

// Cache reads bill at 0.1x input and Claude cache writes at
// 1.25x. Summing all three input counters at the base rate
// charges a warm route ten times what its reads actually cost.
function costUsd(model, u) {
  var e = MODELS[model];
  if (!e) throw new Error('Unknown model: ' + model);
  var m = 1000000;
  return (u.inTok / m) * e.in +
         (u.cacheWrite / m) * e.in * 1.25 +
         (u.cacheRead / m) * e.in * 0.10 +
         (u.outTok / m) * e.out;
}

pickModel returning a model id rather than a label is deliberate, and it's a bug I have already paid for once: a router that returns 'cheap' and maps it to an id one layer down puts the routing vocabulary and the pricing vocabulary in two places, and they drift. Returning the id makes a mismatch impossible to write, and a one-line test over every task catches it if someone adds a route later. I hit the same class of failure with a model-id-versus-label mismatch in Claude prompt caching, where the consequence was caching silently switching itself off.

The prices above are list rates for the models I actually route to, published as of September 2026. Providers move them — Google marks its current Flash rates as effective through the end of 2026, and OpenAI's top-tier rate has a promotional discount with a stated end date — so treat the table as a value you maintain, not a constant.

The request layer

buildRequest is the only function that knows any provider's request shape, and it takes the key as an argument instead of reading PropertiesService itself. That one choice is what makes the whole layer testable outside Apps Script:

var ENDPOINTS = {
  gemini: 'https://generativelanguage.googleapis.com/v1beta/models/',
  openai: 'https://api.openai.com/v1/chat/completions',
  claude: 'https://api.anthropic.com/v1/messages'
};

function buildRequest(model, systemText, userText, key, maxTokens) {
  var entry = MODELS[model];
  if (!entry) throw new Error('Unknown model: ' + model);

  if (entry.p === 'gemini') {
    return {
      url: ENDPOINTS.gemini + model + ':generateContent',
      headers: { 'x-goog-api-key': key },
      payload: {
        systemInstruction: { parts: [{ text: systemText }] },
        contents: [
          { role: 'user', parts: [{ text: userText }] }
        ],
        generationConfig: { maxOutputTokens: maxTokens }
      }
    };
  }

  if (entry.p === 'openai') {
    return {
      url: ENDPOINTS.openai,
      headers: { Authorization: 'Bearer ' + key },
      payload: {
        model: model,
        messages: [
          { role: 'system', content: systemText },
          { role: 'user', content: userText }
        ],
        max_completion_tokens: maxTokens
      }
    };
  }

  return {
    url: ENDPOINTS.claude,
    headers: {
      'x-api-key': key,
      'anthropic-version': '2023-06-01'
    },
    payload: {
      model: model,
      system: systemText,
      messages: [{ role: 'user', content: userText }],
      max_tokens: maxTokens
    }
  };
}

Note that the Gemini branch never puts model in the payload — it goes in the URL path. A body that carries model to Gemini is not an error there either; the field is simply ignored, which is why a copy-pasted OpenAI body appears to work right up until you notice every response came from whichever model the URL named.

The response layer

Reading the answer back is where the three shapes diverge hardest, and where the guards matter more than the happy path:

function readText(model, json) {
  var p = MODELS[model].p;

  if (p === 'gemini') {
    var cand = (json.candidates || [])[0];
    if (!cand) return '';
    // content.parts is absent, not empty, when the turn stopped
    // on SAFETY or MAX_TOKENS. parts[0].text would throw here.
    var parts = (cand.content || {}).parts || [];
    return parts.map(function (x) {
      return x.text || '';
    }).join('');
  }

  if (p === 'openai') {
    var msg = ((json.choices || [])[0] || {}).message || {};
    // content is null on a refusal or a tool call, not ''.
    return msg.content || '';
  }

  // Claude returns an array of blocks and block 0 is not always
  // the text one, so find by type instead of indexing.
  var blocks = json.content || [];
  var out = '';
  for (var i = 0; i < blocks.length; i++) {
    if (blocks[i].type === 'text') out += blocks[i].text;
  }
  return out;
}

// Normalizes three usage shapes into one billable row. Cached
// input is kept apart because it does not bill at the same rate.
function readUsage(model, json) {
  var p = MODELS[model].p;
  var u = json.usageMetadata || json.usage || {};

  if (p === 'gemini') {
    return {
      inTok: u.promptTokenCount || 0,
      // Thinking tokens bill as output but are reported in their
      // own field, outside candidatesTokenCount.
      outTok: (u.candidatesTokenCount || 0) +
              (u.thoughtsTokenCount || 0),
      cacheWrite: 0,
      cacheRead: u.cachedContentTokenCount || 0
    };
  }

  if (p === 'openai') {
    var det = u.prompt_tokens_details || {};
    var cached = det.cached_tokens || 0;
    return {
      inTok: (u.prompt_tokens || 0) - cached,
      outTok: u.completion_tokens || 0,
      cacheWrite: 0,
      cacheRead: cached
    };
  }

  return {
    inTok: u.input_tokens || 0,
    outTok: u.output_tokens || 0,
    cacheWrite: u.cache_creation_input_tokens || 0,
    cacheRead: u.cache_read_input_tokens || 0
  };
}

function callModel(task, systemText, userText) {
  var model = pickModel(task);
  var provider = MODELS[model].p;
  var key = PropertiesService.getScriptProperties()
    .getProperty('KEY_' + provider.toUpperCase());
  var req = buildRequest(model, systemText, userText, key, 1024);

  var res = UrlFetchApp.fetch(req.url, {
    method: 'post',
    contentType: 'application/json',
    headers: req.headers,
    payload: JSON.stringify(req.payload),
    muteHttpExceptions: true
  });

  var code = res.getResponseCode();
  var body = res.getContentText();
  if (code !== 200) {
    throw new Error(provider + ' ' + code + ': ' +
                    body.slice(0, 200));
  }

  var json = JSON.parse(body);
  var usage = readUsage(model, json);
  SpreadsheetApp.getActive().getSheetByName('AI Ledger')
    .appendRow([new Date(), task, model, provider,
                usage.inTok, usage.outTok,
                costUsd(model, usage)]);
  return readText(model, json);
}

The three usage branches are the point of the whole exercise. Without them you have a routing story and no way to check it; with them every call writes a dated row naming the task, the model, the token counts and the dollar figure, and the routing claim becomes something you read off a sheet instead of something you assert.

prompt_tokens on the OpenAI side is a total that already includes cached tokens, so subtracting cached_tokens out of it is not a correction, it's what stops the same tokens being billed twice in the ledger — once at full rate and once at the cache rate.

Pitfalls

muteHttpExceptions is what makes an HTTP error readable. Without it UrlFetchApp.fetch throws on a 429 and the exception text is a generic failure message, not the provider's JSON body explaining which quota you hit. With it you read the code, and the provider's own error text goes into your log.

The cheap model is not cheap on every task. Routing pays because the small model is genuinely adequate for classification and tagging. It is not a free swap for extraction against a messy document or for anything with a tool loop, where a worse model costs more in retries than the flagship cost in tokens. Route by task and measure the accuracy on that task, not by price alone.

Caches don't survive a provider switch, and they don't survive a model switch either. Prompt caches are model-scoped. A router that sends the same system prompt to claude-haiku-4-5 on one call and claude-opus-5 on the next pays a cold write on each escalation, on the whole prefix. That's a real cost the routing table can hide from you if you only look at per-token rates.

Gemini's thinking tokens are in their own counter. They bill as output but they are not inside candidatesTokenCount. A ledger that reads only candidatesTokenCount under-reports a reasoning-heavy Gemini route, and under-reporting is worse than not measuring — it produces a number you trust.

The 6-minute execution ceiling applies to the whole loop, not per call. A router in a batch job over thousands of rows still has to chunk and resume across triggers, and a cheaper model doesn't buy you more wall clock. That constraint is unchanged by anything here.

I ran the pure logic in Node before shipping it. pickModel, buildRequest, readText, readUsage and costUsd contain no UrlFetchApp, no PropertiesService and no SpreadsheetApp, which is what made them testable at all — 22 test groups, including one that asserts every task pickModel can return has a key in MODELS, one that a cache-read-only call bills exactly a tenth of the same tokens read cold, and the two that pin the $6.24 and $330 figures at the top of this article to the registry rather than to my arithmetic.

The takeaway

Model routing is not a prompt change, it's a boundary: one function that owns every provider difference, one registry that owns every price, and a ledger row per call so the saving is measured instead of assumed. Build those three and switching a task to a cheaper model is a one-line edit to pickModel.

The full category map — which workflow to automate first, how the same pattern runs a WhatsApp CRM, invoice extraction and nightly analysis off one Apps Script project — is on the MageSheet blog.

Built by the MageSheet team.

AppSheet's 2,500-Row Ceiling: Audit Your Sheet Before You Pick a Plan

Hayrullah Kar — Mon, 31 Aug 2026 16:57:38 +0000

A 25-person operations team was quoted a jump from Core to Enterprise Plus — $250 a month to $500 — and nobody in the room could say which limit they had hit. Their biggest table held about 1,900 rows, comfortably under the 2,500-row ceiling everyone had memorised. The trigger was the tab count: the single spreadsheet behind the app had grown to twelve tabs, and every tab the app reads is a table.

The seat price is the number people negotiate. The shape of your spreadsheet is the number that decides which row of the price list you are standing on, and it is the one nobody measures.

Why the pricing page can't answer the question

Here are the published 2026 plans. Treat these as the vendor's list at the time of writing — limits and prices move, so check them against your own account before you commit a budget.

Plan	Price	Tables	Rows per table
Free (build & test)	$0, up to 10 test users	—	—
Starter	$5/user/mo	5	2,500
Core	$10/user/mo	10	2,500
Enterprise Plus	$20/user/mo	200	200,000
Publisher Pro	$50/mo per app, flat	—	—

Read that as a seat model and you multiply headcount by price. But which row you land on is set by two ceilings — how many tables the app reads, and how many rows sit in the largest one — and neither is visible from the Sheets UI. Notice that Starter and Core share the same 2,500-row ceiling. Crossing it doesn't move you one tier, it moves you two.

(Whether to use AppSheet at all is a different question, and I've written that one up separately: AppSheet vs Apps Script vs PowerApps. This post assumes you've picked AppSheet and want to know what it will cost as the data grows.)

Put the plans in code, not in a slide

The plan list is data, so make it data. Sorted by price, the first plan that fits is the cheapest one that fits.

// AppSheet plan ceilings as published for 2026. Vendor limits move -
// verify against your own account before committing a budget.
const PLANS = [
  { id: 'starter', usd: 5,  tables: 5,   rows: 2500 },
  { id: 'core',    usd: 10, tables: 10,  rows: 2500 },
  { id: 'plus',    usd: 20, tables: 200, rows: 200000 }
];

// Cheapest plan that fits a shape, or null when nothing does.
// PLANS is price-ascending, so the first match is the answer.
function planFor(shape) {
  for (let i = 0; i < PLANS.length; i++) {
    const p = PLANS[i];
    if (shape.tables <= p.tables && shape.maxRows <= p.rows) return p;
  }
  return null;
}

// Seats are the whole bill: no volume break in the published tiers.
function monthlyCost(plan, seats) {
  if (!plan) return null;
  return plan.usd * seats;
}

Run the twelve-tab team through it and the quote explains itself: planFor({ tables: 12, maxRows: 1900 }) skips Starter and Core on table count alone and returns plus. At 25 seats that is $500 a month against Core's $250 — an extra $3,000 a year bought by two tabs.

Count the rows AppSheet will actually carry

The obvious way to count a tab is getLastRow(). It is the wrong number. It returns the last row holding content in any column, so one note typed into column Z, one stale formula returning an empty string, one row of leftover formatting, and your 120-row table reports thousands.

Count instead from a key column — a field every real record has, like an id or a created-at stamp — and report both numbers, because the gap between them is the thing to go clean up.

// Rows AppSheet will carry for one tab, next to what the used range
// claims. keyCol is the 1-based column every real record fills in.
function tableStats(sheet, keyCol) {
  const used = sheet.getLastRow();
  const name = sheet.getName();
  if (used < 2) return { name: name, rows: 0, usedRows: 0 };
  const col = sheet.getRange(2, keyCol, used - 1, 1).getValues();
  let rows = 0;
  for (let i = 0; i < col.length; i++) {
    const v = col[i][0];
    if (v !== '' && v !== null && v !== undefined) rows++;
  }
  return { name: name, rows: rows, usedRows: used - 1 };
}

// Every tab the app reads counts as its own table, however many
// spreadsheets they happen to live in.
function auditBook(ss, keyCol) {
  const out = [];
  const sheets = ss.getSheets();
  for (let i = 0; i < sheets.length; i++) {
    out.push(tableStats(sheets[i], keyCol || 1));
  }
  return out;
}

// The two numbers a plan is chosen on: table count, biggest table.
function shapeOf(stats) {
  let max = 0;
  for (let i = 0; i < stats.length; i++) {
    if (stats[i].rows > max) max = stats[i].rows;
  }
  return { tables: stats.length, maxRows: max };
}

Both subtractions matter. used - 1 and starting the range at row 2 drop the header, which is not a record and should never be billed as one.

Work out the date you cross the ceiling

Knowing you fit today is half the answer. The other half is the date you stop fitting, because that date is when the bill changes.

const DAY = 86400000;
const MIN_HISTORY_DAYS = 14;

// Rows added per CALENDAR day, measured across the span the data
// covers - not across the days that happen to carry rows. A weekday
// only table has about five dated days a week, so dividing by dated
// days overstates the rate by roughly a third.
function rowsPerDay(dates) {
  if (dates.length < 2) return null;
  let lo = Infinity, hi = -Infinity;
  for (let i = 0; i < dates.length; i++) {
    const t = dates[i].getTime();
    if (t < lo) lo = t;
    if (t > hi) hi = t;
  }
  const span = (hi - lo) / DAY;
  if (span < MIN_HISTORY_DAYS) return null;  // too short to trust
  return dates.length / span;
}

// First day the table passes a ceiling. Floor, never round: a budget
// deadline must not land after the wall does.
function daysToCeiling(rows, perDay, ceiling) {
  if (!perDay || perDay <= 0) return null;
  if (rows >= ceiling) return 0;
  return Math.floor((ceiling - rows) / perDay);
}

rowsPerDay returns null rather than a number it cannot stand behind. Two rows a day apart is not a growth rate, and a function that answers anyway will hand you a crossing date next week.

Pitfalls

getLastRow() is not your row count. In my tests a tab with 120 real records but a stray value at row 4,000 reports usedRows: 3999. Feed the honest 120 to planFor and you get Starter; feed the used range and you get Enterprise Plus — a four-times-per-seat difference caused by one forgotten cell.

The header row buys an upgrade you don't need. A tab holding exactly 2,500 records returns getLastRow() === 2501. Compare that raw number to the ceiling and planFor jumps from Starter to Enterprise Plus — $15 more per user per month, for a row that is a column title.

Dividing by dated days, not calendar days. Twenty weekday rows spanning 25 calendar days is 0.8 rows/day. Divide by the 20 days that carry rows and you get 1.05 — 32% too fast. Starting from 2,000 rows, the honest rate puts the 2,500 ceiling 625 days out; the inflated one says 475. You would budget the upgrade five months before you needed it.

Rounding the crossing date up. At 2,499 rows and 2 rows a day, rounding says one day and flooring says zero. Zero is right: you cross today. A crossing date is a deadline, and a deadline that arrives after the wall is worse than no deadline.

Wrap-up

Two numbers decide your AppSheet plan, and neither is your headcount: how many tabs the app reads, and how many real rows sit in the biggest one. Measure both today, project the second one forward on calendar days, and you will know the month your bill changes before the vendor tells you.

The full 2026 plan breakdown — free-tier limits, Publisher Pro's flat per-app option, and the per-seat math at scale — is on the MageSheet blog.

Built by the MageSheet team.

When a Commission Rate Changes, Last Quarter's Payouts Shouldn't

Hayrullah Kar — Sat, 22 Aug 2026 16:36:46 +0000

A rep opened her March statement in August and the total had moved by $312. Nobody had edited the ledger. Finance had raised her rate in the Rules tab in July, the nightly recompute walked the full transaction history, and every March sale was suddenly paid at July's rate.

Both numbers were arithmetically correct. That is exactly what made it impossible to argue with, and impossible to trust. Once a rep learns that last quarter's number can move, every future number is provisional.

I have written up the two layers around this one already: the attribution engine that decides whose sale it was, and the tiered calculator that turns a sale into an amount. This post is the layer that keeps both honest over time: rules that carry dates, ledger entries whose id comes from the facts, and a log that only ever grows.

Why one rate per rep breaks

The usual Rules tab has one row per rep: rep_id, pct, maybe a quota. That table answers "what is Ayse's rate", but a payout question is never that. It is "what was Ayse's rate on 10 March, when this charge settled".

A single-row rules tab cannot answer it, so every read silently substitutes today's answer. Three failures follow from that one gap:

A rate change repriced quarters that were already paid. The recompute is doing what you told it to.
Someone copied a plan row instead of closing the old one, both rows match, and a first-match lookup picks whichever sorted first. No error, no warning.
A sale dated on the switchover day lands on the wrong side of it, because the sheet hands you a local-midnight Date and your comparison string parses as UTC midnight.

The fix is not more careful editing. It is making the rate a fact with a date range around it, and making the write path unable to overwrite what it already wrote.

1. Rules carry dates, and the lookup carries the sale date

The Rules tab gets two more columns, and rows are never edited in place. Changing a rate means closing the current version and adding a new one:

rule_id	rep_id	effective_from	effective_to	pct
R-2025	ayse	2025-01-01	2026-03-01	0.08
R-26Q1	ayse	2026-03-01	2026-07-01	0.10
R-26Q3	ayse	2026-07-01		0.12
R-mert	mert	2026-01-01		0.05

The ranges are half-open: effective_from is included, effective_to is not. That is what lets one version end and the next begin on the same date without a sale on that day matching both or neither. An empty effective_to means the version is still current.

/**
 * Midnight for a sheet Date or a yyyy-mm-dd string, in one
 * calendar space. Sheets hands you a local-midnight Date;
 * new Date('2026-03-01') is UTC midnight. Mixing them shifts
 * a sale across a rule boundary.
 */
function toDay(value) {
  if (value instanceof Date) {
    return Date.UTC(
      value.getFullYear(), value.getMonth(), value.getDate()
    );
  }
  const p = String(value).slice(0, 10).split('-');
  return Date.UTC(Number(p[0]), Number(p[1]) - 1, Number(p[2]));
}

/**
 * The rule version in force on the sale date. Versions are
 * half-open: [effectiveFrom, effectiveTo). An empty
 * effectiveTo means the version is still current.
 */
function ruleForDate(repId, saleDate, rules) {
  const at = toDay(saleDate);
  const hits = rules.filter(function (r) {
    if (r.repId !== repId) return false;
    const from = toDay(r.effectiveFrom);
    const to = r.effectiveTo ? toDay(r.effectiveTo) : null;
    return at >= from && (to === null || at < to);
  });
  if (hits.length === 0) return null;
  if (hits.length > 1) {
    throw new Error(
      'Overlapping rules for ' + repId + ' on ' +
      new Date(at).toISOString().slice(0, 10) + ': ' +
      hits.map(function (r) { return r.ruleId; }).join(', ')
    );
  }
  return hits[0];
}

Two decisions in there are worth stating out loud.

ruleForDate throws on overlap instead of returning the first hit. A duplicated plan row is a data error, and the moment it resolves quietly you get a payout that nobody can reproduce. Failing the run costs you one morning; a silent first-match costs you a dispute you cannot answer.

toDay exists because a date in Apps Script arrives in two shapes. Read the sale date from the sheet and you get a Date at local midnight. Read the rule boundary from a yyyy-mm-dd string and new Date() gives you UTC midnight. In a UTC+3 sheet those are three hours apart, which is enough to push a 1 March sale back into February's version. Both go through toDay before anything is compared.

With that table, ruleForDate('ayse', '2026-02-28', RULES) returns R-2025 at 8 percent and ruleForDate('ayse', '2026-03-01', RULES) returns R-26Q1 at 10 percent. A $10,000 March sale is worth $1,000 in March and it is still worth $1,000 when the job re-runs in August, when Ayse's current rate is 12 percent.

2. The entry id comes from the facts, not from a counter

Knowing the right rate is half of it. The other half is a writer that can run twice without paying twice, because it will: a trigger overlaps its own previous run, a webhook retries, someone clicks the menu item again after a timeout.

The trick is to stop treating the entry id as a sequence number and start deriving it from the three facts that define the entry: which transaction, which rep, which rule version.

function round2(n) {
  return Math.round(n * 100) / 100;
}

/**
 * One ledger entry for one credit row. entryId is derived from
 * the facts, so a re-run rebuilds the same id and the writer
 * can skip it instead of paying twice.
 */
function entryFor(txn, credit, rule, commission, computedAt) {
  return {
    entryId: [txn.txnId, credit.repId, rule.ruleId].join('|'),
    txnId: txn.txnId,
    repId: credit.repId,
    saleDate: txn.date,
    amount: round2(txn.amount * credit.creditPct),
    commission: round2(commission),
    ruleId: rule.ruleId,
    reversesEntryId: '',
    computedAt: computedAt
  };
}

/**
 * Append-only writer. Returns the rows to append and the ids
 * it skipped; it never edits or deletes an existing row.
 */
function postEntries(candidates, existingIds) {
  const seen = {};
  existingIds.forEach(function (id) { seen[id] = true; });
  const toAppend = [], skipped = [];
  candidates.forEach(function (e) {
    if (seen[e.entryId]) { skipped.push(e.entryId); return; }
    seen[e.entryId] = true;
    toAppend.push(e);
  });
  return { toAppend: toAppend, skipped: skipped };
}

postEntries takes the ids already in the Ledger tab and returns two lists, so the caller does one read and one append. In Apps Script that matters: pull the id column once with getRange(...).getValues(), hand it in, and write the result in a single setValues call rather than one appendRow per entry. Wrap the read-decide-write sequence in LockService.getScriptLock() so two overlapping triggers cannot both decide the id is new.

The derived id also gives you the behaviour you actually want when a rule genuinely changes. A correction re-run with a different rule version produces ch_1|ayse|R-26Q3, which is not ch_1|ayse|R-26Q1, so it appends as a new row and the original stays exactly where it was. The ledger shows both, with rule_id and computed_at on each, and the difference between them is a visible correction instead of a number that moved.

The split case falls out of the same shape. A deal credited 70 percent to the AE and 30 percent to the BDR is two credit rows, so it is two candidate entries with two different ids, each priced against that rep's own rule version. On the March sale above: Ayse gets $7,000 of credited revenue at 10 percent, which is $700, and Mert gets $3,000 at his 5 percent, which is $150.

3. A clawback is a row, not an edit

When the refund webhook arrives, the temptation is to find the original ledger row and zero it out. Do that and the March statement you already sent no longer matches the March statement in the sheet, which is the same trust problem as the rate change wearing different clothes.

/**
 * A clawback is a new negative row dated to the refund, not an
 * edit of the original. Returns null outside the window.
 */
function reversalFor(entry, refundDate, windowDays) {
  const days =
    (toDay(refundDate) - toDay(entry.saleDate)) / 86400000;
  if (days > windowDays) return null;
  return {
    entryId: entry.entryId + '|rev',
    txnId: entry.txnId,
    repId: entry.repId,
    saleDate: entry.saleDate,
    amount: -entry.amount,
    commission: -entry.commission,
    ruleId: entry.ruleId,
    reversesEntryId: entry.entryId,
    computedAt: refundDate
  };
}

/**
 * A rep's statement for one payout period: every row whose
 * computedAt falls inside it, positives and reversals netted.
 */
function statementFor(repId, from, to, ledger) {
  const start = toDay(from), end = toDay(to);
  const lines = ledger.filter(function (e) {
    const at = toDay(e.computedAt);
    return e.repId === repId && at >= start && at < end;
  });
  const total = lines.reduce(function (sum, e) {
    return sum + e.commission;
  }, 0);
  return { repId: repId, lines: lines, total: round2(total) };
}

The window is measured from the sale date, not from the day the entry was computed, because the policy a rep agreed to is about their sale. The reversal is dated to the refund, so it lands in the period where the money actually moves. And it keeps the original ruleId: you are undoing a specific priced entry, not repricing it at today's rate.

Carry the March example through. Ayse's ledger holds ch_1|ayse|R-26Q1 at $700 computed on 11 March. The customer refunds on 5 April, inside a 90-day window, so a second row appends: ch_1|ayse|R-26Q1|rev at -$700, dated 5 April. March still pays $700. April carries -$700. Across the year the two rows net to zero, and every statement you have already sent still reproduces from the ledger.

Because the reversal id is derived the same way, running the refund handler twice appends one row, not two.

What the tests caught

I ran the pure logic under Node with a fake calendar before any of it touched a sheet: 50 assertions across toDay, ruleForDate, entryFor, postEntries, reversalFor and statementFor. Four things only showed up there.

The date-shape bug is real and quiet. Comparing a sheet Date directly against new Date('2026-03-01') puts a 1 March sale in February's version in any sheet east of UTC. It never throws; it just pays 8 percent where the plan says 10.

Overlapping rule versions resolved to a plausible answer. Before the throw, a duplicated row returned R-26Q1 while R-dup matched the same day just as well. The test that pins this asserts an exception, not a value.

The clawback boundary is off by one in the direction nobody checks. With a 90-day window on a 10 March sale, 8 June is inside and 9 June is outside. Write it as days > windowDays returning null, and test both days, or you will discover which side you chose during a dispute.

Rounding belongs at entry time. 1234.56 * 0.07 is 86.4192, and summing raw floats across a quarter drifts from what each line shows. Every entry rounds to cents when it is created, so a statement total is the sum of the numbers the rep can see.

Pitfalls

Never key an entry on its row number. Rows shift when someone sorts the tab, and a re-run then reconciles against the wrong row. The id has to survive a sort.
Never backdate a rule version to fix a payout. Add a correcting entry instead. A backdated version rewrites every statement that quoted it, which is the failure this whole design exists to prevent.
Close the old version on the day the new one starts. Leaving effective_to empty on both is how you get the overlap exception, usually on the first run after a plan change.
A rule version change is not a bug fix. If the old rate was genuinely wrong, the visible correction row is the honest artifact. Two rows and a reason beat one row that quietly moved.
Batch the writes. Apps Script gives you six minutes per execution, and one appendRow per entry burns it on a backfill. Read once, append once, and have the trigger resume from the last processed transaction.
Give each rep a view of their own rows only. A ledger everyone can read stops being a payout tool and becomes salary politics.
Know the ceiling. This holds comfortably to roughly 15 reps and a few thousand transactions a quarter. Past that the ledger wants a real database, and the honest answer is to say so before you build it.

Wrap-up

A commission system earns trust by being reproducible, not by being clever. Three properties get you there: rate rows carry the dates they were in force, the lookup asks with the sale date, and the writer derives its id from the facts so the log can only grow. Once those hold, a re-run is a no-op, a refund is a row, and a statement you sent in March still reproduces in August.

The production version, with Stripe and Gumroad ingestion, the per-rep tokenized view, and the payroll export, is written up on the MageSheet blog.

Built by the MageSheet team.

Apps Script Session Auth: Your Cache Expires Before Your Session Does

Hayrullah Kar — Tue, 18 Aug 2026 17:28:07 +0000

A user signs in at 09:05, works through the morning, and at 14:40 a click comes back with "your session has expired". They sign in again and the app behaves perfectly. The next day it happens at 11:20 instead, to somebody else, on a different screen.

Nothing in the execution log is red. The session policy in the code says thirty days.

It was never thirty days. CacheService.put() documents a maximum expiration of 21600 seconds, and the same class is documented as a best-effort store whose entries can be dropped before they expire. A session that exists only in the cache has a ceiling of six hours and no floor at all.

The cache is a fast path, not a session store

Apps Script gives you no login system, so you build one, and every guide reaches the same shape: issue a token, put a record in CacheService, check it on the next call. The shape is right. The storage assumption is what fails, and it fails on documented behaviour rather than on a bug.

CacheService property	Documented behaviour	What it means for a session
Maximum expiration	21600 seconds, six hours	A larger number does not buy a longer login
Eviction	entries can be dropped before expiry	A live session can vanish mid-afternoon
Value size	100 KB per key	Fine for a session record, not for the user's data
Key length	250 characters	A hash fits, a serialised object does not

Read those together and the conclusion is narrow: the cache can tell you a session is valid, but it can never tell you a session is gone. A missing entry means expired, revoked, evicted, or the cache was flushed, and those four need different answers.

So the record lives in two places with two different jobs. A Sessions sheet is the record of truth: durable, slow, one read per cache miss. The cache is the fast path in front of it, holding the same record for as long as the platform will hold anything. Cache miss becomes the normal case rather than the error case, and the code is written for it.

The first block is the plumbing: hashing, keys, and the two stores.

/**
 * Session auth for an Apps Script web app.
 * The cache is the fast path. The Sheet row is the record.
 * Nothing here ever stores the raw token.
 */

var SESSION = {
  sheet: 'Sessions',
  users: 'Users',
  idleMinutes: 30,     // sliding window
  absoluteHours: 12,   // hard cap, never extended
  keyPrefix: 'ses:'
};

// CacheService.put documents 21600 seconds (6 hours) as the
// maximum expiration. A longer number is not a longer session.
var CACHE_MAX_SECONDS = 21600;

/**
 * Utilities.computeDigest returns SIGNED bytes (-128..127).
 * Without the & 0xFF, every byte over 127 renders as negative
 * hex and the key stops matching the one you stored.
 */
function toHex_(bytes) {
  var out = '';
  for (var i = 0; i < bytes.length; i++) {
    var b = bytes[i] & 0xFF;
    out += (b < 16 ? '0' : '') + b.toString(16);
  }
  return out;
}

function sha256Hex_(text) {
  return toHex_(Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    String(text), Utilities.Charset.UTF_8));
}

/** The store only ever sees the hash, never the token. */
function sessionKey_(token) {
  return SESSION.keyPrefix + sha256Hex_(token);
}

/**
 * Script cache, not user cache: a web app deployed to
 * "Anyone" has no signed-in user to key a user cache on.
 */
function cache_() {
  return CacheService.getScriptCache();
}

function readCache_(key) {
  var raw = cache_().get(key);
  if (!raw) return null;
  var rec = JSON.parse(raw);
  rec.fromCache = true;
  return rec;
}

function writeCache_(rec, ttlSeconds) {
  if (ttlSeconds <= 0) return;
  cache_().put(rec.key, JSON.stringify({
    key: rec.key, email: rec.email, role: rec.role,
    issuedAt: rec.issuedAt, lastSeenAt: rec.lastSeenAt,
    rowLastSeenAt: rec.rowLastSeenAt
  }), ttlSeconds);
}

function sheet_(name) {
  var ss = SpreadsheetApp.getActive();
  return ss ? ss.getSheetByName(name) : null;
}

/**
 * Sessions columns:
 * A key | B email | C role | D issuedAt | E lastSeenAt
 * Epoch milliseconds, written to a plain-text column so a
 * long number is never reformatted into a date.
 */
function findSessionRowIndex_(key) {
  var sh = sheet_(SESSION.sheet);
  if (!sh || sh.getLastRow() < 2) return 0;
  var hit = sh.getRange(2, 1, sh.getLastRow() - 1, 1)
    .createTextFinder(key).matchEntireCell(true).findNext();
  return hit ? hit.getRow() : 0;
}

function readSessionRow_(key) {
  var i = findSessionRowIndex_(key);
  if (!i) return null;
  var row = sheet_(SESSION.sheet)
    .getRange(i, 1, 1, 5).getValues()[0];
  return {
    key: String(row[0]),
    email: String(row[1]),
    role: String(row[2]),
    issuedAt: Number(row[3]),
    lastSeenAt: Number(row[4]),
    rowLastSeenAt: Number(row[4]),
    rowIndex: i,
    fromCache: false
  };
}

Two decisions in there are worth naming. The store holds sha256Hex_(token) and never the token, so a person with read access to the Sheet has a list of hashes rather than a list of working logins. And it is the script cache, not the user cache: a web app deployed to "Anyone" has no signed-in Google identity to key a user cache on, so getUserCache() in that deployment is not the isolation it looks like.

The expiry rule is one function with no service calls in it

Three questions decide whether a session is alive, and the order they are asked in is part of the answer.

Was it revoked? A "log out everywhere" button stamps a revoke instant on the user, and every session issued before that instant is dead. The comparison is against issuedAt, not lastSeenAt. Compare against lastSeenAt and the sessions that survive your revocation are exactly the active ones: the attacker clicking every few seconds keeps a fresh lastSeenAt, sails past the check, and the button you pressed logged out only the idle tabs.

Is it past the absolute cap? Twelve hours from issue, regardless of activity. This is the one an attacker with a stolen token cannot extend by using it.

Is it past the idle window? Thirty minutes since the last request, refreshed on every request.

Written as a pure function, all three are testable without touching a Google service, which is most of what makes this code auditable at all.

/**
 * The whole expiry decision, with no service calls in it.
 * revokedAt is compared against issuedAt, not lastSeenAt:
 * a session touched after the revoke instant must still die.
 */
function decideSession_(rec, now, revokedAt) {
  if (!rec) return { state: 'unknown' };
  var idleMs = SESSION.idleMinutes * 60 * 1000;
  var absMs = SESSION.absoluteHours * 60 * 60 * 1000;
  if (!(rec.issuedAt > 0) || !(rec.lastSeenAt > 0)) {
    return { state: 'unknown' };
  }
  if (revokedAt > 0 && rec.issuedAt <= revokedAt) {
    return { state: 'revoked' };
  }
  if (now - rec.issuedAt >= absMs) {
    return { state: 'expired-absolute' };
  }
  if (now - rec.lastSeenAt >= idleMs) {
    return { state: 'expired-idle' };
  }
  return { state: 'ok', email: rec.email, role: rec.role };
}

/**
 * How long the session may still live, in seconds, clamped to
 * what the cache will actually hold. A 12-hour session gets a
 * 6-hour entry and rehydrates from the row afterwards.
 */
function cacheTtlSeconds_(rec, now) {
  var idleMs = SESSION.idleMinutes * 60 * 1000;
  var absMs = SESSION.absoluteHours * 60 * 60 * 1000;
  var leftAbsolute = rec.issuedAt + absMs - now;
  var ms = Math.min(idleMs, leftAbsolute);
  if (ms <= 0) return 0;
  return Math.min(Math.ceil(ms / 1000), CACHE_MAX_SECONDS);
}

/**
 * Writing lastSeenAt on every request turns one click into
 * one Sheet write. Refresh the row only once it has drifted
 * more than half the idle window behind.
 */
function needsRowTouch_(rowLastSeenAt, now) {
  var halfIdleMs = SESSION.idleMinutes * 30 * 1000;
  return (now - Number(rowLastSeenAt || 0)) >= halfIdleMs;
}

/**
 * Cache miss is the normal case, not the error case: entries
 * are evicted before their TTL and the ceiling is six hours.
 */
function validateSession_(token, now) {
  if (!token) return { state: 'unknown' };
  var key = sessionKey_(token);
  var rec = readCache_(key) || readSessionRow_(key);
  if (!rec) return { state: 'unknown' };
  var verdict = decideSession_(rec, now, revokedAtFor_(rec.email));
  if (verdict.state !== 'ok') {
    dropSession_(rec);
    return verdict;
  }
  rec.lastSeenAt = now;
  if (needsRowTouch_(rec.rowLastSeenAt, now)) {
    if (touchSessionRow_(rec, now)) rec.rowLastSeenAt = now;
  }
  writeCache_(rec, cacheTtlSeconds_(rec, now));
  return verdict;
}

/**
 * Users columns: A email | B role | C revokedAt
 * Cached briefly, so "log out everywhere" takes effect within
 * that window rather than on the next request.
 */
var REVOKE_TTL_SECONDS = 60;

function revokedAtFor_(email) {
  var addr = String(email || '').trim().toLowerCase();
  if (!addr) return 0;
  var key = 'rev:' + addr;
  var hit = cache_().get(key);
  if (hit !== null && hit !== undefined) return Number(hit);
  var sh = sheet_(SESSION.users);
  var value = 0;
  if (sh && sh.getLastRow() > 1) {
    var found = sh.getRange(2, 1, sh.getLastRow() - 1, 1)
      .createTextFinder(addr).matchEntireCell(true).findNext();
    if (found) {
      value = Number(sh.getRange(found.getRow(), 3).getValue()) || 0;
    }
  }
  cache_().put(key, String(value), REVOKE_TTL_SECONDS);
  return value;
}

cacheTtlSeconds_ is where the six-hour ceiling stops being a surprise. The session policy stays whatever you wrote it to be, twelve hours here, and the cache entry asks for the smallest of three numbers: the idle window, whatever is left of the absolute cap, and 21600. A twelve-hour session gets a six-hour entry, then a row read, then another entry. The policy and the storage stop being the same number, which is the whole fix.

needsRowTouch_ is the counterweight. Refreshing lastSeenAt in the Sheet on every request turns one click into one write, and a web app has 30 seconds to answer. Refreshing it never is worse: after an eviction, the row is the only thing left, and a row that is 25 minutes stale reads as an idle timeout for a user who was active four seconds ago. Half the idle window is the compromise, and it is the reason a continuously active session survives eviction in the tests below.

The gate in front of every server function

google.script.run has no request object and no headers. Every value the server sees is an argument, so the token is an argument, and the only thing that keeps it from being optional is that nothing runs before the gate.

var ROLE_RANK = { viewer: 1, staff: 2, admin: 3 };

/** An unrecognised role is not a role. Blank is not admin. */
function normalizeRole_(role) {
  var r = String(role || '').trim().toLowerCase();
  return ROLE_RANK[r] ? r : '';
}

function canAccess_(role, minimumRole) {
  var have = ROLE_RANK[normalizeRole_(role)] || 0;
  var need = ROLE_RANK[normalizeRole_(minimumRole)] || 0;
  if (!have || !need) return false;
  return have >= need;
}

function issueSession_(email, role, now) {
  var token = Utilities.getUuid() + Utilities.getUuid();
  var rec = {
    key: sessionKey_(token),
    email: String(email).trim().toLowerCase(),
    role: normalizeRole_(role),
    issuedAt: now,
    lastSeenAt: now,
    rowLastSeenAt: now
  };
  if (!rec.role) throw new Error('Unknown role for ' + rec.email);
  sheet_(SESSION.sheet).appendRow([rec.key, rec.email, rec.role,
    rec.issuedAt, rec.lastSeenAt]);
  writeCache_(rec, cacheTtlSeconds_(rec, now));
  return { token: token, role: rec.role };
}

/**
 * A record served from cache carries no row index, so the row
 * is located by key on the rare pass that needs it.
 */
function touchSessionRow_(rec, now) {
  var i = rec.rowIndex || findSessionRowIndex_(rec.key);
  if (!i) return false;
  rec.rowIndex = i;
  sheet_(SESSION.sheet).getRange(i, 5).setValue(now);
  return true;
}

function dropSession_(rec) {
  cache_().remove(rec.key);
  var i = rec.rowIndex || findSessionRowIndex_(rec.key);
  if (i) sheet_(SESSION.sheet).deleteRow(i);
}

/**
 * Every server function the page can call goes through this.
 * google.script.run sends no headers, so the token travels as
 * an argument like any other value.
 */
function guarded_(token, minimumRole, fn) {
  var now = Date.now();
  var v = validateSession_(token, now);
  if (v.state !== 'ok') return { ok: false, reason: v.state };
  if (!canAccess_(v.role, minimumRole)) {
    return { ok: false, reason: 'forbidden' };
  }
  return { ok: true, data: fn(v) };
}

function listOrders(token) {
  return guarded_(token, 'staff', function (session) {
    return ordersFor_(session.email);
  });
}

/**
 * Log out everywhere. The revoke instant is stamped on the
 * user, so every session issued before it dies, this one
 * included.
 */
function logOutEverywhere(token) {
  return guarded_(token, 'viewer', function (session) {
    var sh = sheet_(SESSION.users);
    var found = sh.getRange(2, 1, sh.getLastRow() - 1, 1)
      .createTextFinder(session.email)
      .matchEntireCell(true).findNext();
    if (!found) return { revoked: false };
    sh.getRange(found.getRow(), 3).setValue(Date.now());
    cache_().remove('rev:' + session.email);
    return { revoked: true };
  });
}

canAccess_ denies on both sides. An unrecognised role gives zero rank, and so does an unrecognised requirement, so a typo in the gate closes the door instead of opening it. That matters more than it sounds: the role comes out of a spreadsheet cell that a human edits, and Administrator, ADMIN, and an empty cell are all things that cell will eventually contain.

The refusal is typed rather than boolean. unknown means log in again, expired-idle means log in again but say why, revoked means the account was signed out elsewhere, and forbidden means the login is fine and the role is not. A single false sends all four to the login screen, including the one where the user is already signed in and merely lacks permission.

What breaks in practice

Signed bytes from computeDigest. Apps Script returns the digest as bytes in the range -128 to 127. Convert them with b.toString(16) and every byte over 127 comes out as something like -6f, so the key is stable but wrong: wrong length, wrong alphabet, and different from what any other tool computes for the same token. b & 0xFF is the fix, and it is worth the assertion that pins the output to a known SHA-256 hex string.

A 30-day number in a 6-hour argument. Passing 2592000 to put() does not create a 30-day entry, because the documented maximum is 21600 seconds. Clamping explicitly, as cacheTtlSeconds_ does, means the behaviour is decided by your code rather than by what the platform chooses to do with an out-of-range number.

getUserCache() in a public deployment. A web app deployed to run as you and be accessible by "Anyone" has no signed-in user to scope a user cache to. The script cache with a hashed key is the honest version of what the user cache looked like it was doing.

Sessions in localStorage. Any script that runs in the page can read it, so one XSS is every session on that machine. Keeping the token in a JavaScript variable for the lifetime of the tab costs the user a re-login when they refresh, and removes the exfiltration path entirely.

Tokens from Math.random(). It is not a cryptographic generator and its output is predictable given enough samples. Utilities.getUuid() is the platform's identifier generator; two of them concatenated, then hashed for storage, is a token that costs nothing to produce and nothing to store safely.

The Sessions sheet grows forever. Every login appends a row and only expired-on-access sessions get deleted, so a sheet that is never pruned turns createTextFinder into a full-column scan against a 30-second response budget. A daily trigger that deletes rows older than the absolute cap keeps it small, and that job is a good candidate for the chunked, self-rescheduling pattern once the table is large.

Revocation lags by the revoke cache TTL. Reading the Users sheet on every request is a second Sheet read per click, so the revoke instant is cached for 60 seconds. That is a deliberate trade: "log out everywhere" takes effect within a minute rather than instantly. Set REVOKE_TTL_SECONDS to 0 if a minute is not acceptable, and pay for it in reads.

Epoch milliseconds in a date-formatted column. A cell holding 1750000000000 will render as a date if the column is formatted for dates, and getValues() then hands back a Date rather than a number. Number() survives both, but formatting the timestamp columns as plain text keeps the sheet readable by the humans who will inevitably open it.

The pure logic here was checked with 113 assertions in Node against mocked Utilities, CacheService and SpreadsheetApp services, including a computeDigest mock that returns signed bytes so the hex conversion is tested rather than assumed. The window behaviour was driven with a fake clock: a session used every 20 minutes survives past three hours and dies at the twelve-hour cap on the first request after it, an evicted-but-active session rehydrates from its row, and an evicted session with a stale row expires. Apps Script services cannot run outside the platform, so the call sites themselves were reviewed by hand against their documented return types.

The takeaway

Sessions in Apps Script fail on storage, not on logic. The token, the sliding window and the role check are the easy half; the half that produces the 14:40 support ticket is treating a six-hour best-effort cache as though it were the place the session lives. Give the session a row, put the cache in front of it, and write the code so that a cache miss is Tuesday afternoon rather than an incident.

If your web app is public and has no login at all, the guards it needs are a different set, and the booking-page build covers those. The rest of the production picture, from the UI layer down to when to leave the platform, is in the complete guide to building on Apps Script.

Audit an Apps Script Handover: 5 Checks That Prove You Own the Code

Hayrullah Kar — Wed, 12 Aug 2026 17:02:51 +0000

Owning the source code and owning the running system are two different things in Apps Script, and from inside the editor they look identical. You can hold every line, in a file in your own Drive, with the developer removed from the sharing list — and still have a nightly sync that stops the day they close their Google account.

Nothing in the project tells you this is coming. The code is there. The schedule is not.

Reading the code cannot answer the ownership question

The usual handover checklist is a paperwork exercise: get the source, get ownership in writing, remove the developer's access. All three are worth doing. None of them test the thing that actually breaks.

Four of the five facts that decide whether a delivered Apps Script system survives the developer leaving are not in the source at all:

Fact	Where it lives	Visible in the editor
Who owns the Sheet and the data files	Drive permissions	no
Who created the script project	project metadata	no
Whose account serves the web app	the deployment's `executeAs`	no
Which triggers exist	per-user trigger table	only yours
Which credentials came with it	script properties	yes, if you look

The trigger row is the expensive one, and it is documented behaviour rather than a bug. ScriptApp.getProjectTriggers() "gets all installable triggers associated with the current project and current user." A time-driven trigger the developer installed under their own account belongs to their user inside your project. You open Triggers in your editor and the list is empty. The job still runs every night, as them, until the day it doesn't.

The web app has the same shape. Every deployment carries entryPoints[].webApp.entryPointConfig.executeAs, and the value USER_DEPLOYING means each request is served with the identity of whoever pressed Deploy. If that was the developer, your public order form is reading and writing your Sheet through their Google account, on their quota, under their audit log.

So instead of asking for a handover document, I run a script.

Check 1: who actually owns the files

Paste this into the delivered project and run it from your own account. Every check is read-only.

The interesting part is not getOwner(). It is what happens when getOwner() cannot answer, which turns out to be often.

/**
 * Handover audit for a delivered Apps Script project.
 * Read-only: it reports, it never edits the system it audits.
 * Run it from YOUR account, not the developer's.
 */

var AUDIT = {
  // Every Sheet / Doc / Drive file the delivered system uses.
  // Add the ids the developer listed, plus any id you find
  // hard-coded in the source.
  fileIds: [],
  // Were you sold anything that runs on a schedule?
  expectScheduled: true,
  // Were you sold a web app, form, or dashboard URL?
  expectWebApp: true,
  reportSheet: 'Handover Audit'
};

var BLOCKER = 'BLOCKER';
var WARN = 'WARN';
var OK = 'OK';

/**
 * Blank-safe compare. User.getEmail() returns a blank string
 * when policy hides the address, so '' must never equal ''.
 */
function sameUser_(a, b) {
  var x = String(a || '').trim().toLowerCase();
  var y = String(b || '').trim().toLowerCase();
  if (!x || !y) return false;
  return x === y;
}

/** Domain part of an email, or '' when there isn't one. */
function domainOf_(email) {
  var s = String(email || '').trim().toLowerCase();
  var at = s.lastIndexOf('@');
  if (at < 1 || at === s.length - 1) return '';
  return s.slice(at + 1);
}

var FREE_DOMAINS = ['gmail.com', 'googlemail.com', 'yahoo.com',
                    'outlook.com', 'hotmail.com', 'live.com',
                    'icloud.com', 'proton.me', 'protonmail.com'];

/**
 * Two people on gmail.com are not colleagues. Matching on a
 * free domain would file the developer under "your domain"
 * and downgrade a blocker to a warning.
 */
function isFreeDomain_(domain) {
  return FREE_DOMAINS.indexOf(String(domain || '').toLowerCase()) > -1;
}

/**
 * Where an owner sits relative to you.
 * hasOwner is false for shared-drive files: the Drive API
 * does not populate an owner there, so "no owner" is not
 * the same answer as "not yours".
 */
function classifyOwner_(ownerEmail, me, hasOwner) {
  if (!hasOwner) return 'no-owner';
  if (!String(me || '').trim()) return 'unknown-viewer';
  if (!String(ownerEmail || '').trim()) return 'unknown-owner';
  if (sameUser_(ownerEmail, me)) return 'you';
  var mine = domainOf_(me);
  var theirs = domainOf_(ownerEmail);
  if (mine && theirs && mine === theirs && !isFreeDomain_(mine)) {
    return 'your-domain';
  }
  return 'outside';
}

var OWNER_LEVEL = {
  'you': OK,
  'your-domain': WARN,
  'outside': BLOCKER,
  'no-owner': WARN,
  'unknown-owner': WARN,
  'unknown-viewer': WARN
};

function auditOwnership_(fileIds, me) {
  var out = [];
  for (var i = 0; i < fileIds.length; i++) {
    var id = fileIds[i];
    try {
      var file = DriveApp.getFileById(id);
      var owner = file.getOwner();
      var email = owner ? owner.getEmail() : '';
      var where = classifyOwner_(email, me, !!owner);
      out.push({
        check: 'Owner of "' + file.getName() + '"',
        level: OWNER_LEVEL[where],
        detail: where + (email ? ' (' + email + ')' : '')
      });
      out.push(editorsFinding_(file, me));
    } catch (err) {
      out.push({
        check: 'File ' + id,
        level: BLOCKER,
        detail: 'Cannot open: ' + err.message
      });
    }
  }
  return out;
}

/**
 * getEditors() returns an empty array when the running user
 * has no edit access, so an empty list is reported as
 * unreadable rather than as "nobody else has access".
 */
function editorsFinding_(file, me) {
  var editors = file.getEditors() || [];
  if (!editors.length) {
    return {
      check: 'Editors of "' + file.getName() + '"',
      level: WARN,
      detail: 'empty list: you may not have edit access'
    };
  }
  var others = [];
  for (var i = 0; i < editors.length; i++) {
    var e = editors[i].getEmail();
    if (!sameUser_(e, me)) others.push(e || '(hidden)');
  }
  return {
    check: 'Editors of "' + file.getName() + '"',
    level: others.length ? WARN : OK,
    detail: others.length ? others.join(', ') : 'only you'
  };
}

classifyOwner_ returns six different answers instead of a boolean, because "no owner", "I cannot read the owner", and "the owner is someone else" are three different problems with three different fixes. Collapsing them into isMine is how an audit ends up reporting a clean handover on a file nobody in your company can administer.

Checks 2 and 3: who created it, and whose account serves it

Neither of these facts is reachable from the Apps Script services. Both come from the Apps Script API, which the script can call using its own OAuth token:

GET https://script.googleapis.com/v1/projects/{scriptId} returns creator and lastModifyUser, each a user object with an email.
GET https://script.googleapis.com/v1/projects/{scriptId}/deployments returns every deployment, and each web app entry point carries access and executeAs.

/**
 * Reads the Apps Script API with the script's own token.
 * Returns a result object instead of throwing: the API is
 * off by default, and that is a finding, not a crash.
 */
function scriptApi_(path) {
  var url = 'https://script.googleapis.com/v1/projects/' + path;
  var res = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true,
    headers: {
      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
    }
  });
  var code = res.getResponseCode();
  var body = res.getContentText();
  if (code === 200) return { ok: true, data: JSON.parse(body) };
  return { ok: false, code: code, detail: body.slice(0, 200) };
}

function auditProject_(scriptId, me) {
  var res = scriptApi_(scriptId);
  if (!res.ok) {
    return [{
      check: 'Script project metadata',
      level: WARN,
      detail: 'HTTP ' + res.code + ' — enable the Apps Script ' +
              'API for your account, then re-run'
    }];
  }
  var p = res.data;
  var creator = (p.creator && p.creator.email) || '';
  var where = classifyOwner_(creator, me, !!creator);
  return [{
    check: 'Who created the script project',
    level: OWNER_LEVEL[where],
    detail: where + (creator ? ' (' + creator + ')' : '')
  }, {
    check: 'Script is bound to file',
    level: p.parentId ? OK : WARN,
    detail: p.parentId || 'standalone (no container file)'
  }];
}

/**
 * Pulls the web app entry point out of one deployment.
 * USER_DEPLOYING means every request runs as whoever
 * pressed Deploy — usually the developer.
 */
function webAppRisk_(deployment) {
  var eps = (deployment && deployment.entryPoints) || [];
  for (var i = 0; i < eps.length; i++) {
    var w = eps[i].webApp;
    if (!w) continue;
    var cfg = w.entryPointConfig || {};
    return {
      isWebApp: true,
      url: w.url || '',
      access: cfg.access || 'UNKNOWN_ACCESS',
      executeAs: cfg.executeAs || 'UNKNOWN_EXECUTE_AS',
      runsAsDeployer: cfg.executeAs === 'USER_DEPLOYING'
    };
  }
  return { isWebApp: false, runsAsDeployer: false };
}

function auditDeployments_(scriptId, expectWebApp) {
  var res = scriptApi_(scriptId + '/deployments');
  if (!res.ok) {
    return [{
      check: 'Deployments',
      level: WARN,
      detail: 'HTTP ' + res.code + ' — cannot read deployments'
    }];
  }
  var list = res.data.deployments || [];
  var out = [];
  var sawWebApp = false;
  for (var i = 0; i < list.length; i++) {
    var w = webAppRisk_(list[i]);
    if (!w.isWebApp) continue;
    sawWebApp = true;
    out.push({
      check: 'Web app runs as',
      level: w.runsAsDeployer ? BLOCKER : OK,
      detail: w.executeAs + ', access ' + w.access
    });
  }
  if (expectWebApp && !sawWebApp) {
    out.push({
      check: 'Web app deployment',
      level: BLOCKER,
      detail: 'none found in your copy of the project'
    });
  }
  return out;
}

webAppRisk_ walks all entry points rather than reading entryPoints[0]. A deployment can expose an execution API entry point and a web app entry point at once, and the order is not guaranteed, so indexing the first one silently reports "no web app" on a project that has one.

The zero-deployment case is a finding, not an empty result. If you were sold a form URL and your copy of the project has no web app deployment, the URL people are using belongs to a deployment in someone else's project.

Checks 4 and 5: the schedule you cannot see, and the keys you inherited

/**
 * getProjectTriggers() returns the triggers of the current
 * project AND the current user. A schedule the developer
 * installed under their own account is not in this list.
 */
function auditTriggers_(expectScheduled) {
  var triggers = ScriptApp.getProjectTriggers();
  if (!triggers.length) {
    return [{
      check: 'Scheduled triggers visible to you',
      level: expectScheduled ? BLOCKER : OK,
      detail: expectScheduled ?
        'none — a schedule exists somewhere else' : 'none'
    }];
  }
  var names = [];
  for (var i = 0; i < triggers.length; i++) {
    names.push(triggers[i].getHandlerFunction() + ' / ' +
               triggers[i].getEventType());
  }
  return [{
    check: 'Scheduled triggers visible to you',
    level: OK,
    detail: names.join('; ')
  }];
}

var SECRET_HINTS = ['key', 'token', 'secret', 'password',
                    'auth', 'credential', 'webhook',
                    'signature', 'passwd'];

/** Name-only test. The audit never reads a stored value. */
function looksLikeSecret_(name) {
  var n = String(name || '').toLowerCase();
  for (var i = 0; i < SECRET_HINTS.length; i++) {
    if (n.indexOf(SECRET_HINTS[i]) !== -1) return true;
  }
  return false;
}

function auditSecrets_() {
  var props = PropertiesService.getScriptProperties();
  var keys = props.getKeys() || [];
  var secrets = [];
  for (var i = 0; i < keys.length; i++) {
    if (looksLikeSecret_(keys[i])) secrets.push(keys[i]);
  }
  return [{
    check: 'Inherited credentials in script properties',
    level: secrets.length ? WARN : OK,
    detail: secrets.length ?
      secrets.join(', ') + ' — rotate these' : 'none by name'
  }];
}

function verdict_(findings) {
  var counts = { BLOCKER: 0, WARN: 0, OK: 0 };
  for (var i = 0; i < findings.length; i++) {
    var lvl = findings[i].level;
    if (counts[lvl] === undefined) counts[lvl] = 0;
    counts[lvl]++;
  }
  var status = 'CLEAR';
  if (counts.WARN > 0) status = 'ATTENTION';
  if (counts.BLOCKER > 0) status = 'NOT HANDED OVER';
  return {
    status: status,
    blockers: counts.BLOCKER,
    warnings: counts.WARN,
    passed: counts.OK
  };
}

/** Stops a value that starts with = from becoming a formula. */
function safeText_(v) {
  var s = (v === null || v === undefined) ? '' : String(v);
  return /^[=+\-@]/.test(s) ? "'" + s : s;
}

function writeReport_(rows, summary) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) {
    // Standalone project: there is no sheet to write into.
    Logger.log(JSON.stringify({ summary: summary, rows: rows }));
    return;
  }
  var sheet = ss.getSheetByName(AUDIT.reportSheet) ||
              ss.insertSheet(AUDIT.reportSheet);
  sheet.clear();
  var out = [['Check', 'Level', 'Detail']];
  for (var i = 0; i < rows.length; i++) {
    out.push([
      safeText_(rows[i].check),
      safeText_(rows[i].level),
      safeText_(rows[i].detail)
    ]);
  }
  out.push(['', '', '']);
  out.push(['VERDICT', summary.status,
            summary.blockers + ' blockers, ' +
            summary.warnings + ' warnings']);
  sheet.getRange(1, 1, out.length, 3).setValues(out);
  sheet.getRange(1, 1, 1, 3).setFontWeight('bold');
  sheet.setColumnWidth(1, 320);
  sheet.setColumnWidth(3, 480);
  SpreadsheetApp.flush();
}

function runHandoverAudit() {
  var me = Session.getEffectiveUser().getEmail();
  var scriptId = ScriptApp.getScriptId();
  var ids = AUDIT.fileIds.slice();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (ss && ids.indexOf(ss.getId()) === -1) ids.push(ss.getId());

  var findings = [];
  if (!String(me || '').trim()) {
    findings.push({
      check: 'Your identity',
      level: WARN,
      detail: 'blank email — run this from the Apps Script ' +
              'editor, not a trigger or custom function'
    });
  }
  findings = findings
    .concat(auditOwnership_(ids, me))
    .concat(auditProject_(scriptId, me))
    .concat(auditDeployments_(scriptId, AUDIT.expectWebApp))
    .concat(auditTriggers_(AUDIT.expectScheduled))
    .concat(auditSecrets_());

  var summary = verdict_(findings);
  writeReport_(findings, summary);
  return summary;
}

Set AUDIT.fileIds, AUDIT.expectScheduled and AUDIT.expectWebApp to match what you were told you bought, then run runHandoverAudit from the editor. NOT HANDED OVER means at least one part of the system answers to an account that is not yours.

auditTriggers_ cannot list a trigger it is not allowed to see, and it does not pretend to. It compares what you can see against what you were sold. Zero visible triggers plus a system that demonstrably runs on a schedule is the signal — the schedule exists, it just is not yours.

What breaks in practice

A blank email compares equal to a blank email. User.getEmail() returns a blank string when policy hides the address, and it does so in exactly the contexts where you might run this by accident: simple onOpen/onEdit triggers, custom functions, and web apps deployed to execute as the developer. A naive owner === me then reads '' === '' and reports that you own a file whose owner it could not read. That is why sameUser_ returns false when either side is blank, and why runHandoverAudit raises a warning about your own identity before anything else runs.

Shared-drive files have no owner at all. Drive's own documentation is explicit that the owner field "isn't populated for items in shared drives." A file on a shared drive is genuinely fine — the drive owns it, not a person — so treating a missing owner as "not yours" produces false blockers, and treating it as "yours" hides real ones. It gets its own outcome, no-owner, at warning level.

An empty editor list is not a clean file. getEditors() returns an empty array when the running user has no edit access. Read that as "nobody else has access" and the audit reports its best result on the file you have the least control over.

Free email domains are not a company. Matching on the domain part is how you catch a colleague, but two accounts on gmail.com share nothing. Without the isFreeDomain_ guard, a consumer-account handover files the developer under your-domain and quietly downgrades a blocker to a warning.

The Apps Script API is off until someone turns it on. Google's own guidance is that access must be granted from the Apps Script dashboard first, and that "an error results if you attempt to run an affected application without first granting the API access" — after authorization, not before it. Checks 2 and 3 therefore return a warning that names the HTTP status instead of throwing, so an audit that cannot reach the API still delivers checks 1, 4 and 5.

A standalone project has no sheet to write into. SpreadsheetApp.getActiveSpreadsheet() returns null outside a container-bound script, so the report falls back to the log rather than failing on the last line after doing all the work.

Two related limits are worth naming rather than re-solving here: a delivered job that outgrows its runtime needs chunking and self-rescheduling, and any integration that calls an external API needs quota-aware retries. Both have their own patterns — how the 6-minute limit is handled and the retry and backoff patterns — and a developer who cannot describe either is the reason to run this audit in the first place.

The pure logic here was checked with 117 assertions in Node, covering the branches above with mocked Drive, Script, UrlFetch and Properties services; the Apps Script services themselves cannot run outside the platform, so those call sites were reviewed by hand against the documented return types.

The takeaway

Ownership in Apps Script is not one fact, it is five, and four of them live outside the code you were handed. Run the checks before the final invoice, while the developer is still answering email and a USER_DEPLOYING web app is a five-minute redeploy instead of a rebuild.

The hiring side of this — engagement models, what belongs in writing, and the vetting questions no script can answer — is the companion guide on the MageSheet blog.

Magento Order Sync in Apps Script: Page-Based Pagination Drops Orders

Hayrullah Kar — Tue, 11 Aug 2026 19:25:36 +0000

Point a searchCriteria[currentPage] loop at a Magento store that is still taking orders, and the Sheet ends up disagreeing with the store. Some orders land twice, some never land at all. Nothing throws. The API returns 200 on every call, the script logs a clean finish, and the only way to find out is to count rows against the admin.

I hit this building an order sync, and the fix was not a retry or a bigger page size. It was giving up on page numbers entirely.

The offset is a row count, not a row

currentPage is an offset. currentPage=2 with pageSize=100 means "skip 100 rows, then take 100". That is only correct if the result set holds still while you walk it, and on a live store it does not.

Work through the arithmetic. Sort newest-first, request page 1, and read rows 1 to 100. Two orders are placed before your page-2 request. Every existing row is now two positions further down the list. The two rows you already read at the bottom of page 1 sit at positions 101 and 102, which is exactly where page 2 starts. You read them again. That is the duplicate.

The hole works the other way. Sort oldest-first on updated_at and run the same loop while statuses are changing. A row you already passed gets updated, jumps to the end of the sort, and vacates its slot. Everything behind it shifts one position toward the start, so the row that was at position 201 is now at 200 — inside the page you just finished. You never see it.

Both failures need the result set to move under you, which is the normal state of a production store. Neither raises an error, because from the API's side every request was valid.

Keyset pagination: make the position a value

Stop asking for an offset. Ask for "the next N orders after the last entity_id I actually wrote". Concurrent inserts cannot move that position, because it is a value in the data rather than a count of rows.

Here is the foundation: one fetch helper that checks the response code before parsing, and one query builder.

function magentoGet_(path) {
  const p = PropertiesService.getScriptProperties();
  const base = p.getProperty('MAGENTO_BASE_URL');
  const token = p.getProperty('MAGENTO_TOKEN');
  const res = UrlFetchApp.fetch(base + '/rest/V1/' + path, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true,
  });
  const code = res.getResponseCode();
  if (code === 429 || code >= 500) {
    throw new Error('retryable ' + code + ': ' + res.getContentText());
  }
  if (code >= 300) {
    throw new Error('magento ' + code + ': ' + res.getContentText());
  }
  return JSON.parse(res.getContentText());
}

function magentoQuery_(groups, opts) {
  const p = [];
  groups.forEach(function (filters, g) {
    filters.forEach(function (f, i) {
      const k = 'searchCriteria[filter_groups][' + g +
                '][filters][' + i + ']';
      p.push(k + '[field]=' + encodeURIComponent(f.field));
      p.push(k + '[value]=' + encodeURIComponent(f.value));
      p.push(k + '[condition_type]=' + encodeURIComponent(f.cond));
    });
  });
  if (opts.sortField) {
    p.push('searchCriteria[sortOrders][0][field]=' + opts.sortField);
    p.push('searchCriteria[sortOrders][0][direction]=' + opts.sortDir);
  }
  p.push('searchCriteria[pageSize]=' + opts.pageSize);
  p.push('searchCriteria[currentPage]=1');
  return p.join('&');
}

currentPage is pinned to 1 and never changes. That is the whole trick: every request asks for the first page of a different filter, not the next page of the same one.

The sync loop moves the cursor instead.

const PAGE_SIZE = 100;
const RUN_BUDGET_MS = 4.5 * 60 * 1000;

// A billing name starting with = + - or @ becomes a live formula
// the moment setValues writes it. Prefix it instead.
function safeText_(v) {
  const s = v == null ? '' : String(v);
  return /^[=+\-@]/.test(s) ? "'" + s : s;
}

function orderToRow_(o) {
  return [
    safeText_(o.increment_id),
    o.created_at,
    o.updated_at,
    o.status,
    safeText_(o.customer_email),
    Number(o.grand_total),
    o.order_currency_code,
  ];
}

function nextCursor_(items, current) {
  return items.reduce(function (max, o) {
    const id = Number(o.entity_id);
    return id > max ? id : max;
  }, current);
}

function syncNewOrders() {
  const props = PropertiesService.getScriptProperties();
  const sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
  const started = Date.now();
  let cursor = Number(props.getProperty('ORDER_CURSOR') || 0);

  while (Date.now() - started < RUN_BUDGET_MS) {
    const query = magentoQuery_(
      [[{ field: 'entity_id', value: cursor, cond: 'gt' }]],
      { sortField: 'entity_id', sortDir: 'ASC', pageSize: PAGE_SIZE }
    );
    const items = magentoGet_('orders?' + query).items || [];
    if (!items.length) return;

    const rows = items.map(orderToRow_);
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length)
      .setValues(rows);
    SpreadsheetApp.flush();

    cursor = nextCursor_(items, cursor);
    props.setProperty('ORDER_CURSOR', String(cursor));
    if (items.length < PAGE_SIZE) return;
  }
}

Two details in that loop carry the reliability.

The write happens before the cursor moves. setValues, then flush, then setProperty. If the run dies between them — quota, timeout, a bad gateway from the store — the next run re-reads the batch it already wrote. Re-reading one page is a bounded, fixable problem. Advancing the cursor first and then failing to write is a permanent hole.

The loop watches the clock. RUN_BUDGET_MS leaves headroom under the 6-minute execution ceiling so the run exits on its own terms with a saved cursor, rather than being killed mid-setValues.

New orders are only half of it

A cursor on entity_id finds orders that did not exist before. It will never find an order that was placed yesterday and shipped today, because updating an order does not change its entity_id.

The second pass filters on updated_at and upserts. It needs two things the naive version gets wrong.

function parseMagentoUtc_(s) {
  return new Date(String(s).replace(' ', 'T') + 'Z');
}

function toMagentoUtc_(d) {
  return d.toISOString().slice(0, 19).replace('T', ' ');
}

// Re-read a safety overlap so commits that landed late are not missed.
function windowStart_(lastRunUtc, overlapSec) {
  const t = parseMagentoUtc_(lastRunUtc).getTime() - overlapSec * 1000;
  return toMagentoUtc_(new Date(t));
}

function planUpsert_(index, rows, idCol) {
  const updates = [];
  const appends = [];
  const seen = Object.create(null);
  rows.forEach(function (row) {
    const id = String(row[idCol]);
    if (Object.prototype.hasOwnProperty.call(seen, id)) {
      updates.push({ row: seen[id], values: row });
      return;
    }
    if (Object.prototype.hasOwnProperty.call(index, id)) {
      seen[id] = index[id];
      updates.push({ row: index[id], values: row });
    } else {
      appends.push(row);
    }
  });
  return { updates: updates, appends: appends };
}

// increment_id -> sheet row number. Column A must be formatted as
// plain text, or Sheets hands back 123 instead of '000000123'.
function readIdIndex_(sheet) {
  const last = sheet.getLastRow();
  if (last < 2) return {};
  const ids = sheet.getRange(2, 1, last - 1, 1).getValues();
  const index = Object.create(null);
  ids.forEach(function (r, i) {
    if (r[0] !== '') index[String(r[0])] = i + 2;
  });
  return index;
}

function syncChangedOrders() {
  const props = PropertiesService.getScriptProperties();
  const sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
  const lastRun = props.getProperty('ORDER_WATERMARK') ||
    toMagentoUtc_(new Date(Date.now() - 864e5));
  const since = windowStart_(lastRun, 120);
  const runStartedUtc = toMagentoUtc_(new Date());

  const query = magentoQuery_(
    [[{ field: 'updated_at', value: since, cond: 'gteq' }]],
    { sortField: 'updated_at', sortDir: 'ASC', pageSize: PAGE_SIZE }
  );
  const items = magentoGet_('orders?' + query).items || [];
  const plan = planUpsert_(readIdIndex_(sheet), items.map(orderToRow_), 0);

  plan.updates.forEach(function (u) {
    sheet.getRange(u.row, 1, 1, u.values.length).setValues([u.values]);
  });
  if (plan.appends.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, plan.appends.length,
      plan.appends[0].length).setValues(plan.appends);
  }
  SpreadsheetApp.flush();
  props.setProperty('ORDER_WATERMARK', runStartedUtc);
}

The window starts before the last run, not at it. A transaction can be stamped with an updated_at of 14:03:22 and commit a second or two later. If your next run asks for updated_at >= 14:03:23, that row is already invisible. The 120-second overlap re-reads a small band on every run, and the upsert makes re-reading free.

The watermark is the run's start time, not its finish time. Save the finish time and every order that changed while the run was in flight falls into the gap between the two runs and is never picked up.

Pitfalls

Leaving out pageSize does not give you a default page. Adobe's REST documentation is explicit: "If the pageSize is not specified, the system returns all matches." Not the first 20 — all of them. On a store with tens of thousands of orders that is a single response Apps Script has to hold in memory and JSON.parse in one go. Always set it.

Two filters in one filter_groups is an OR, not an AND. The same docs: "To perform a logical OR, specify multiple filters within a filter_groups. To perform a logical AND, specify multiple filter_groups." Put status=processing and updated_at gteq X side by side in group 0 and you get every processing order ever placed, plus everything updated since X. It looks like a working sync until you check the row count.

Magento date fields are UTC; the admin grid shows store time. Copy a timestamp off the admin screen into a filter and the window shifts by your UTC offset. For a store on +03:00 that is a three-hour hole on every run, in the direction that loses orders rather than duplicating them. Build the string from a Date with toISOString, as toMagentoUtc_ does above, and never from a formatted local string.

increment_id is a string with leading zeros. 000000123 is not 123. If you build the upsert index by reading a Sheets column that was never formatted as text, Sheets hands you the number 123, no row matches, and every changed order appends a fresh duplicate. Format the column as plain text and compare with String() on both sides.

A customer name can be a formula. A billing name that starts with =, +, - or @ becomes a live formula the moment setValues writes it, and =HYPERLINK(...) in a name field is a real thing storefronts receive. That is what safeText_ in the sync loop above is for: it prefixes an apostrophe so the cell stays text. Run every customer-supplied field through it, not just the name.

Rate limits are a separate problem. A sync that behaves against 200 orders will meet 429 and transient 5xx at 5,000. magentoGet_ above marks those as retryable but does not retry; the backoff, jitter and dead-letter handling belong in the fetch layer, which I covered separately in the hidden cost of UrlFetchApp.

What this buys you

I pulled the pure logic out of the Apps Script services — the query builder, the cursor, the UTC conversions, the upsert planner — and ran it in Node: 37 assertions, covering the OR-versus-AND grouping, a cursor that refuses to move backwards, an overlap window that crosses midnight, and an increment_id of 000000123 correctly refusing to match 123.

The rule that survives every store size is the one at the top: never let your read position be a row count. Page numbers describe where you are in a list that Magento is free to reorder between two HTTP calls. An entity_id describes a row.

The production version — two passes on one trigger, retry and backoff in the fetch layer, and the full Magento-to-Sheets playbook it sits inside — is on the MageSheet blog.

Built by the MageSheet team.

Claude Prompt Caching in Apps Script: Why Your Cache Read Count Is Zero

Hayrullah Kar — Wed, 05 Aug 2026 19:17:03 +0000

I added cache_control to a WhatsApp handler's system prompt, deployed it, and watched the Apps Script logs for a week. Every single response came back with cache_read_input_tokens: 0. No error, no warning, no 400. The API accepted the cache_control block on every call and charged me the 1.25x write premium every time, then read nothing back.

Prompt caching fails silently by design. If the prefix is too short, or one byte of it moved, the API just doesn't cache — it does not tell you. And Apps Script has three specific ways to trip that wire that you won't hit in a Python service.

Here's the guard-railed version of the integration, with the checks that turn a silent miss into something you can see.

The three silent invalidators

1. The prefix is under the model's minimum, and the minimum is per-model. Below the threshold, cache_control is accepted and ignored. The numbers as of August 2026:

Model	Input $/MTok	Output $/MTok	Min cacheable prefix
`claude-haiku-4-5`	$1	$5	4,096 tokens
`claude-sonnet-4-6`	$3	$15	1,024 tokens
`claude-sonnet-5`	$3	$15	1,024 tokens
`claude-opus-4-7`	$5	$25	2,048 tokens
`claude-opus-4-8`	$5	$25	1,024 tokens
`claude-opus-5`	$5	$25	512 tokens

The minimum is not monotonic across generations. A 2,000-token system prompt caches on Sonnet 4.6 and on Opus 4.8, does not cache on Opus 4.7, and does not cache on Haiku 4.5 — the cheap model has the highest bar, at 4,096 tokens. That combination bites hardest in a router: you send cheap-path traffic to Haiku precisely because it's cheap, and that's the one model where your prefix is most likely to fall under the line.

2. A volatile byte moved. Caching is a prefix match on exact bytes, rendered in the order tools → system → messages. Apps Script projects interpolate dynamic values into system prompts constantly — new Date(), a Session.getActiveUser().getEmail(), a row count pulled from the Sheet at the top of doPost. Any of these at the front of the prompt invalidates everything after them on every request.

3. The tool list was rebuilt in a different order. Tools render at position 0, ahead of the system prompt. If your tool array is assembled from an object's keys, or filtered per user, the prefix changes and the system prompt cache goes with it.

The integration, with the guard rails

The cacheability check runs before the request, so you never pay a write premium for a prefix that can't be read back:

// Minimum cacheable prefix per model, in tokens. Below this, the API
// accepts cache_control and silently does not cache.
var CACHE_MIN_TOKENS = {
  'claude-haiku-4-5': 4096,
  'claude-sonnet-4-6': 1024,
  'claude-sonnet-5': 1024,
  'claude-opus-4-7': 2048,
  'claude-opus-4-8': 1024,
  'claude-opus-5': 512
};

// Rough guard rail for English text, not billing truth. Confirm the
// real number with usage.cache_read_input_tokens on a live call.
function estimateTokens(text) {
  return Math.ceil(text.length / 3.6);
}

function isCacheablePrefix(model, prefixText) {
  var min = CACHE_MIN_TOKENS[model];
  if (min === undefined) return false;
  return estimateTokens(prefixText) >= min;
}

function pickRoute(intent, options) {
  options = options || {};
  if (options.forceModel) return options.forceModel;
  if (intent === 'classification' || intent === 'extraction') {
    return 'claude-haiku-4-5';
  }
  if (intent === 'audit' || intent === 'planning') {
    return 'claude-opus-4-8';
  }
  return 'claude-sonnet-4-6';
}

pickRoute returns a model ID, not a route label. That matters because the model ID is the key into CACHE_MIN_TOKENS — routing and cacheability have to agree, and keeping them in one vocabulary is what makes that checkable in a test.

The request layer reads the response code before parsing, and logs the cache counters on every call:

function isRetryable(statusCode) {
  return statusCode === 408 || statusCode === 429 || statusCode >= 500;
}

function backoffMs(attempt) {
  return Math.min(1000 * Math.pow(2, attempt), 16000);
}

function callClaude(model, systemText, messages, tools) {
  var system = [{ type: 'text', text: systemText }];
  if (isCacheablePrefix(model, systemText)) {
    system[0].cache_control = { type: 'ephemeral' };
  }

  var payload = {
    model: model,
    max_tokens: 4096,
    system: system,
    messages: messages
  };
  if (tools && tools.length) payload.tools = tools;

  for (var attempt = 0; attempt < 4; attempt++) {
    var res = UrlFetchApp.fetch(
      'https://api.anthropic.com/v1/messages', {
        method: 'post',
        contentType: 'application/json',
        headers: {
          'x-api-key': PropertiesService.getScriptProperties()
            .getProperty('ANTHROPIC_API_KEY'),
          'anthropic-version': '2023-06-01'
        },
        payload: JSON.stringify(payload),
        muteHttpExceptions: true
      });

    var code = res.getResponseCode();
    var body = res.getContentText();

    if (code === 200) {
      var data = JSON.parse(body);
      Logger.log('cache_read=%s cache_write=%s input=%s',
        data.usage.cache_read_input_tokens,
        data.usage.cache_creation_input_tokens,
        data.usage.input_tokens);
      return data;
    }
    if (!isRetryable(code) || attempt === 3) {
      throw new Error('Claude ' + code + ': ' + body.slice(0, 300));
    }
    Utilities.sleep(backoffMs(attempt));
  }
}

Two things there are not decoration. muteHttpExceptions: true means a 429 or a 529 returns normally instead of throwing, so getResponseCode() is the only thing standing between you and JSON.parse-ing an error object into a variable your tool loop will later index as data.content. And the Logger.log line is the entire diagnostic: cache_read_input_tokens staying at 0 across repeated calls with the same prefix is the signal that one of the three invalidators is active.

The retry here is deliberately minimal — four attempts, capped at 16 seconds, 7 seconds of total sleep across three retries, which stays well inside the 6-minute trigger ceiling. For the rate limiter and the dead-letter queue that belong around it in a real webhook, see the UrlFetchApp quotas and retries breakdown.

The tool loop keeps tool_use_id paired with its result and turns a thrown handler into a structured error the model can recover from:

function runToolLoop(model, systemText, userText, tools, handlers) {
  var messages = [{ role: 'user', content: userText }];

  for (var turn = 0; turn < 5; turn++) {
    var res = callClaude(model, systemText, messages, tools);
    messages.push({ role: 'assistant', content: res.content });

    if (res.stop_reason !== 'tool_use') {
      return res.content
        .filter(function (b) { return b.type === 'text'; })
        .map(function (b) { return b.text; })
        .join('\n');
    }

    var results = res.content
      .filter(function (b) { return b.type === 'tool_use'; })
      .map(function (block) {
        var out;
        try {
          out = handlers[block.name](block.input);
        } catch (err) {
          return {
            type: 'tool_result',
            tool_use_id: block.id,
            content: 'Error: ' + err.message,
            is_error: true
          };
        }
        return {
          type: 'tool_result',
          tool_use_id: block.id,
          content: JSON.stringify(out)
        };
      });

    messages.push({ role: 'user', content: results });
  }
  throw new Error('Tool loop did not converge in 5 turns');
}

All tool results for one assistant turn go back in a single user message. Splitting them across several messages is accepted by the API and quietly trains the model to stop issuing parallel tool calls.

What the caching is actually worth

Take a handler at 3,000 messages a day with a 7,000-token cached prefix on Sonnet 4.6 at $3 per million input tokens. That's 630M input tokens a month:

Uncached: 630 MTok x $3 = $1,890/month
Cached, assuming 90% of calls land inside a warm 5-minute window: 63 MTok of writes at 1.25x ($236) plus 567 MTok of reads at 0.1x ($170) = $406/month

The 90% figure is the assumption doing the work, and it's a property of your traffic shape, not of the API. Bursty webhook traffic clears it easily; a trigger that fires once an hour never gets a single hit on the 5-minute cache. Measure it from cache_read_input_tokens before you put it in a business case.

Pitfalls

The 1-hour cache break-even is three requests, not two. A 5-minute write costs 1.25x base input and a read costs 0.1x, so two requests beat two uncached ones (1.35x vs 2x). A 1-hour write costs 2x, so two requests lose (2.1x vs 2x) and you only come out ahead at three. Reach for ttl: "1h" when the gap between requests exceeds five minutes, not because it sounds safer.

Four breakpoints, and automatic caching eats one. A request is capped at four explicit cache_control markers. If you also set top-level automatic caching, that consumes a slot.

Switching models mid-conversation throws the cache away. Caches are model-scoped. A router that escalates from Haiku to Opus mid-thread pays a fresh cold write on the escalation, on the full accumulated history. Budget for it, or keep the escalation on a fresh short prompt.

Apps Script cannot consume streaming. UrlFetchApp.fetch returns the whole body or nothing; it has no SSE path. This is fine in practice, because the 30-second doPost ceiling means you couldn't hold the connection anyway. If you need token-by-token output for a UI, that belongs in a separate streaming-capable service, with Apps Script out of the request path and the Sheet still the system of record.

Keep the router's return value and the cache table in the same vocabulary. The version of this router I first wrote returned labels like 'claude-haiku' and mapped them to model IDs one layer down. That layer is where the two drift apart: the router says claude-haiku, the cache table has a key for claude-haiku-4-5, and isCacheablePrefix falls through to undefined and returns false for every request. Caching silently switches itself off across the whole cheap path, and nothing errors. Returning the model ID directly makes the mismatch impossible, and a one-line test over every intent catches it if someone adds a route later.

I ran the pure logic in Node before shipping it — estimateTokens, isCacheablePrefix, pickRoute, isRetryable, backoffMs are all plain functions with no UrlFetchApp in them. 32 assertions, including the boundary case where a 3,682-character prefix estimates to 1,023 tokens and correctly fails the 1,024-token Sonnet minimum, and the assertion that every intent pickRoute can return has a key in CACHE_MIN_TOKENS. That last one is the test that would have caught the label bug.

The takeaway

Prompt caching has no failure signal, so the integration has to supply one. Check the prefix against the model's minimum before you set cache_control, log cache_read_input_tokens on every call, and treat a run of zeros as a bug rather than a cost you haven't optimized yet.

The production version — with the six-layer webhook architecture, the dead-letter queue, and the full router — is on the MageSheet blog.

Built by the MageSheet team.

Build a Calendly Alternative in Apps Script: Slot Math, Locking, and Abuse Guards

Hayrullah Kar — Sun, 02 Aug 2026 17:12:41 +0000

The first booking page I built double-booked me in week two. Two people picked the same 10:00 slot forty seconds apart, both saw "Confirmed," and I found out when they both dialled in. The calendar check had run for the second person before the first person's event was written.

That bug is the whole reason this post exists. A booking page looks like a form and a calendar read, and Calendly charges $8–16 per user per month for it. You can replace it with about 150 lines of Apps Script — but only if you handle three things the naive version gets wrong: the slot arithmetic at the boundaries, the read-then-write race, and the fact that a public web app will happily let a stranger call your booking function directly.

Here's the version that's been running without an incident since.

The shape of it

Three pieces, one Google account, no server:

doGet serves a stateless HTML form. No login, no session.
getAvailableSlots / bookSlot run as the owner and talk to Calendar.
A Bookings sheet is the append-only log — the audit trail you cancel and reschedule from.

Google Calendar is the source of truth. The Sheet never decides whether a slot is free; it only records what happened.

Setup: one calendar, one sheet, nine settings

Make a separate Google Calendar for bookable hours — not your personal one, or every dentist appointment becomes a booking conflict you have to explain. Then a Sheet with two tabs, Config and Bookings.

The Config tab is two columns, key in A and value in B, starting at row 2. This is the part most write-ups skip, and it's the part that breaks people:

Key	Example value	What it does
`calendarId`	`abc...@group.calendar.google.com`	Calendar → Settings → "Calendar ID"
`timezone`	`Europe/Istanbul`	Must match File → Project Settings → Time zone
`businessHourStart`	`9`	First bookable hour, host's local time
`businessHourEnd`	`17`	Last hour a slot may end at
`slotLengthMinutes`	`30`	Slot size
`minNoticeHours`	`4`	Don't offer a slot 10 minutes from now
`eventTitle`	`Discovery call`	Prefix for the calendar event
`hostEmail`	`you@yourdomain.com`	Added as a guest so it lands in your inbox
`bookingPageUrl`	`https://script.google.com/.../exec`	Written into the event description

Reading config from a Sheet means you change your hours without redeploying. But don't do what I first did and read it in a top-level IIFE — that runs on every execution, including each doGet, so every page load pays a Sheets read, and one typo in Config takes down the form itself. Cache it:

// Code.gs
function getConfig() {
  const cache = CacheService.getScriptCache();
  const hit = cache.get('cfg');
  if (hit) return JSON.parse(hit);

  const sheet = SpreadsheetApp.getActive().getSheetByName('Config');
  const rows = sheet.getRange('A2:B').getValues().filter(r => r[0]);
  const cfg = Object.fromEntries(rows);
  cache.put('cfg', JSON.stringify(cfg), 300); // 5 min
  return cfg;
}

function doGet() {
  return HtmlService.createHtmlOutputFromFile('booking-form')
    .setTitle('Book a time')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

A2:B is open-ended on purpose. A hardcoded A2:B10 silently ignores the eleventh setting you add six months from now, and you will spend an afternoon wondering why it has no effect.

The slot math, as a pure function

Availability is the one piece worth extracting from Google's services entirely, because it's the piece with off-by-one bugs and it's the piece you can actually test:

// Returns the start timestamps of every slot that fits and is unclaimed.
// dayStart/dayEnd/busy are epoch ms — no Calendar objects in here.
function freeSlots(dayStart, dayEnd, slotMs, busy) {
  const out = [];
  for (let t = dayStart; t + slotMs <= dayEnd; t += slotMs) {
    const end = t + slotMs;
    const taken = busy.some(b => b.start < end && b.end > t);
    if (!taken) out.push(t);
  }
  return out;
}

The two strict inequalities in taken are the whole game. b.end > t means an event that ends at exactly 10:00 does not block the 10:00 slot; b.start < end means one that starts at exactly 10:00 does not block the 09:30 slot. Flip either to >= and every back-to-back meeting eats a neighbouring slot — the "why does my calendar look full when it isn't" bug.

I ran this through 21 assertions in plain Node before it went near a calendar: an empty 09:00–17:00 day at 30 minutes gives exactly 16 slots, a 12:00–13:00 event removes exactly two, a five-minute event still burns its whole slot, a 50-minute window offers one slot rather than two, and overlapping busy ranges collapse instead of double-removing. Both boundary cases above have their own test. (The harness I use for this is in unit-testing Apps Script.)

Now the Calendar side. Two filters here matter more than they look:

function busyRanges(calendar, from, to) {
  return calendar.getEvents(from, to)
    .filter(e => !e.isAllDayEvent())
    .filter(e => e.getMyStatus() !== CalendarApp.GuestStatus.NO)
    .map(e => ({
      start: e.getStartTime().getTime(),
      end: e.getEndTime().getTime()
    }));
}

// Build a Date at `hour` on `dateString` in the SCRIPT's timezone.
function hostTime(dateString, hour) {
  const parts = dateString.split('-').map(Number);
  return new Date(parts[0], parts[1] - 1, parts[2], hour, 0, 0, 0);
}

function getAvailableSlots(dateString, visitorTz) {
  const cfg = getConfig();
  const slotMs = Number(cfg.slotLengthMinutes) * 60000;
  const dayStart = hostTime(dateString, Number(cfg.businessHourStart));
  const dayEnd = hostTime(dateString, Number(cfg.businessHourEnd));

  const notice = Number(cfg.minNoticeHours) * 3600000;
  const earliest = Date.now() + notice;
  if (dayEnd.getTime() <= earliest) return [];

  const calendar = CalendarApp.getCalendarById(cfg.calendarId);
  const busy = busyRanges(calendar, dayStart, dayEnd);

  return freeSlots(dayStart.getTime(), dayEnd.getTime(), slotMs, busy)
    .filter(t => t >= earliest)
    .map(t => ({
      iso: new Date(t).toISOString(),
      display: Utilities.formatDate(new Date(t), visitorTz, 'HH:mm')
    }));
}

Skipping the all-day filter is what produces the support ticket "your booking page says you're never available" — one all-day "Conference" entry spans your entire working day and wipes out every slot. And without the GuestStatus.NO filter, invitations you declined still block your calendar.

The iso field is UTC; only display is rendered in the visitor's zone. Send a local-looking string to the browser and you will eventually book someone at 3 a.m.

Booking, with the race closed

LockService is the fix for the double-booking that started this post. It serializes the read-then-write, so the second visitor's availability check runs after the first visitor's event exists and correctly comes back occupied:

function bookSlot(form) {
  if (form.company) return { ok: false, error: 'Rejected.' }; // honeypot

  const name = cleanText(form.name, 80);
  const email = String(form.email || '').trim().slice(0, 120);
  if (!name) return { ok: false, error: 'Please enter your name.' };
  if (!isEmail(email)) {
    return { ok: false, error: 'Please enter a valid email address.' };
  }
  if (overBookingLimit(email)) {
    return { ok: false, error: 'Too many bookings from this address today.' };
  }

  const cfg = getConfig();
  const slotStart = new Date(form.slotIso);
  const slotMs = Number(cfg.slotLengthMinutes) * 60000;
  const slotEnd = new Date(slotStart.getTime() + slotMs);

  // Re-derive the grid server-side: never trust the posted timestamp.
  const dateKey = Utilities.formatDate(slotStart, cfg.timezone, 'yyyy-MM-dd');
  const open = hostTime(dateKey, Number(cfg.businessHourStart)).getTime();
  const close = hostTime(dateKey, Number(cfg.businessHourEnd)).getTime();
  const onGrid = (slotStart.getTime() - open) % slotMs === 0;
  if (slotStart.getTime() < open || slotEnd.getTime() > close || !onGrid) {
    return { ok: false, error: 'That time is outside booking hours.' };
  }

  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) {
    return { ok: false, error: 'Server busy — please try again.' };
  }
  try {
    const calendar = CalendarApp.getCalendarById(cfg.calendarId);
    if (busyRanges(calendar, slotStart, slotEnd).length > 0) {
      return { ok: false, error: 'That slot was just taken — pick another.' };
    }

    const event = calendar.createEvent(
      cfg.eventTitle + ' — ' + name,
      slotStart,
      slotEnd,
      {
        guests: email + ',' + cfg.hostEmail,
        sendInvites: true,
        description: 'Booked via ' + cfg.bookingPageUrl
      }
    );
    event.setTag('visitorTimezone', String(form.visitorTz || ''));

    SpreadsheetApp.getActive().getSheetByName('Bookings').appendRow([
      new Date(), name, email, slotStart,
      event.getId(), form.visitorTz, 'confirmed'
    ]);

    return { ok: true, message: 'Confirmed. The invite is in your inbox.' };
  } finally {
    lock.releaseLock();
  }
}

Four details that aren't decoration:

tryLock over waitLock. waitLock throws when it times out, which surfaces to the visitor as a raw stack trace. tryLock returns false and you control the message.
releaseLock in finally. If createEvent throws, the lock still releases — otherwise your booking page is dead for the next ten seconds of every request.
The grid check. The lock stops two people booking the same slot; it does nothing about someone posting 2026-08-03T02:17:33Z. Re-deriving open/close/onGrid server-side is what makes the slot list authoritative rather than advisory.
event.getId() in the sheet. Without it, cancelling means searching the calendar by name and hoping.

Storing the visitor timezone as a calendar tag (not just a sheet column) is what makes rescheduling survive a daylight-savings change — the reschedule code reads it off the event it's actually moving.

The guards themselves are small, and all three are pure functions:

// Strip the characters Sheets would evaluate as a formula, then cap length.
function cleanText(value, maxLen) {
  return String(value == null ? '' : value)
    .replace(/^[=+\-@\t\r]+/, '')
    .trim()
    .slice(0, maxLen);
}

function isEmail(value) {
  return /^[^@\s]+@[^@\s]+\.[^@\s]{2,}$/.test(String(value).trim());
}

function overBookingLimit(email) {
  const cache = CacheService.getScriptCache();
  const key = 'bk:' + Utilities.base64EncodeWebSafe(email.toLowerCase());
  const n = Number(cache.get(key) || 0);
  if (n >= 3) return true;
  cache.put(key, String(n + 1), 3600); // 3 per email per hour
  return false;
}

cleanText is not paranoia. A name typed as =HYPERLINK("http://x","click") lands in a cell your accountant later opens, and it lands in the calendar event title too. It keeps Jean-Luc and Ayşe Yılmaz intact — it only strips the leading characters Sheets treats as formula starters.

The form

Save as booking-form.html in the same Apps Script project. The markup first:

<!DOCTYPE html>
<html>
<head>
  <style>
    body { font-family: system-ui; max-width: 480px;
           margin: 2rem auto; padding: 1rem; }
    .slot { display: inline-block; padding: .6rem; margin: .3rem;
            border: 1px solid #ccc; border-radius: 4px; cursor: pointer; }
    .slot.selected { background: #3B7CDE; color: #fff; }
    .hp { position: absolute; left: -9999px; }
  </style>
</head>
<body>
  <h2>Book a 30-minute call</h2>
  <input type="date" id="date" />
  <div id="slots"></div>

  <form id="form" hidden>
    <input id="name" placeholder="Your name" required />
    <input id="email" type="email" placeholder="Your email" required />
    <input id="company" class="hp" tabindex="-1" autocomplete="off" />
    <button type="submit">Confirm</button>
  </form>
  <p id="result"></p>

  <!-- the wiring below goes in a script tag right here -->
</body>
</html>

And the wiring that goes in that script tag:

  const tz = Intl.DateTimeFormat().resolvedOptions().timeZone;
  let slotIso = null;

  document.getElementById('date').addEventListener('change', function (e) {
    google.script.run.withSuccessHandler(function (slots) {
      const box = document.getElementById('slots');
      box.innerHTML = slots.length
        ? slots.map(function (s) {
            return '<span class="slot" data-iso="' + s.iso + '">' +
                   s.display + '</span>';
          }).join('')
        : 'No times left on that day.';
      box.querySelectorAll('.slot').forEach(function (el) {
        el.addEventListener('click', function () {
          box.querySelectorAll('.slot')
             .forEach(function (s) { s.classList.remove('selected'); });
          el.classList.add('selected');
          slotIso = el.dataset.iso;
          document.getElementById('form').hidden = false;
        });
      });
    }).getAvailableSlots(e.target.value, tz);
  });

  document.getElementById('form').addEventListener('submit', function (e) {
    e.preventDefault();
    const btn = e.target.querySelector('button');
    btn.disabled = true;                       // stop the double-submit
    google.script.run.withSuccessHandler(function (r) {
      document.getElementById('result').textContent =
        r.ok ? r.message : r.error;
      if (!r.ok) btn.disabled = false;
    }).bookSlot({
      slotIso: slotIso,
      name: document.getElementById('name').value,
      email: document.getElementById('email').value,
      company: document.getElementById('company').value,
      visitorTz: tz
    });
  });

The company field is the honeypot — off-screen, tabindex="-1", no autocomplete. Humans never fill it; naive form bots fill every input they find. It costs one line in bookSlot and removes most of the automated noise.

Deploy → New deployment → Web app → Execute as: Me, Who has access: Anyone. You'll get the unverified-app consent screen on first authorization — that's expected for a script that isn't OAuth-verified; click through Advanced. The /exec URL is what you share.

What actually breaks in production

"Anyone" means anyone. google.script.run is not the only way to reach bookSlot. Anyone with the /exec URL can call it directly with fabricated input — which is why the honeypot, the email check, the rate limit, and the grid check all live server-side. A public web app with validation only in the browser is an open calendar.
Timezone drift between the script and the config. hostTime builds dates in the script's timezone (File → Project Settings), while Utilities.formatDate uses cfg.timezone. If those two disagree, slots quietly shift by the offset. Set both, and set them once.
Consumer Gmail invite limits. The Calendar API quota is generous, but a free @gmail.com account caps the invitations a script can send per day far lower than a Workspace account does. It's Workspace that makes this comfortable at volume.
The 5-minute config cache. Change your hours and the form can serve stale settings for up to five minutes. Fine in practice; confusing when you're testing. Drop the TTL to 30 seconds while you set things up.
No cancellation link yet. The event ID is in the sheet, which is the hard part, but visitors still have to email you. Token-based cancel/reschedule links are the first thing to build next.
Cold starts. The first request after an idle period takes noticeably longer than a warm one. At a few bookings a day nobody notices. At a few hundred, they will.

When to just pay Calendly

I run this for myself and for one-person consultancies, and it's clearly the right call there. It stops being the right call when you have five-plus reps each needing their own branded page with round-robin routing, when you need Salesforce or HubSpot integration out of the box, or when booking volume is high enough that Apps Script quotas and cold starts become a customer-facing problem. Building any of those yourself costs more than the subscription.

Wrap-up

The calendar read is the easy 20 lines. What separates a booking page that works from one that quietly double-books you is three specific things: strict inequalities in the slot comparison, a script lock wrapped around the read-then-write, and server-side validation that assumes the caller skipped your form entirely.

The production version — token-based cancel and reschedule links, buffer times between meetings, branded confirmation emails, and a retry queue for failed writes — is written up on the MageSheet blog.

Built by the MageSheet team.

AppSheet vs Apps Script vs PowerApps (2026): When to Use Each

Hayrullah Kar — Sat, 01 Aug 2026 22:18:27 +0000

"Should we use AppSheet or Apps Script?" is the wrong question — and I've watched teams waste weeks arguing it. They're not competitors. AppSheet delivers a UI; Apps Script runs logic. Most production setups I build end up using both. This post is how to decide which does what, what it actually costs per user, and the hybrid pattern you'll probably land on.

What each one actually is

AppSheet is a no-code app builder. Point it at a Google Sheet and it generates a mobile/web app — forms, lists, role-based views, offline sync, GPS and camera — without writing code. Apps Script is a JavaScript runtime inside Google Workspace: it runs logic, calls any API, handles webhooks, and automates Sheets/Gmail/Calendar/Docs. PowerApps is Microsoft's AppSheet — pick it only if your data and team already live in Microsoft 365.

The one-line test: AppSheet is for the interface, Apps Script is for the logic.

The decision matrix

What you need	Pick
Mobile app for field workers (offline, GPS, camera)	AppSheet
Internal CRUD tool for under ~10 users	AppSheet
Form with dependent dropdowns + per-role permissions	AppSheet
Quick prototype to validate an internal idea	AppSheet
Custom logic / heavy data processing (>1,000 rows)	Apps Script
AI integration (OpenAI / Anthropic / Gemini)	Apps Script
Webhook receiver (Stripe, Twilio, Magento)	Apps Script
Customer-facing portal with custom branding	Apps Script
A tool 50+ employees use daily	Apps Script (cost)
A production system meant to last 5+ years	Apps Script (control)

Both keep your data in the same place — a Google Sheet you own — so choosing one now doesn't lock you out of the other later.

The cost nobody mentions

AppSheet is priced per user (as of 2026): roughly $5/user/mo Starter, $10 Core, $20 Enterprise Plus. Apps Script is $0 — no per-user fee at any headcount, under standard Workspace quotas.

That gap compounds. A 30-person warehouse team on AppSheet Core is $3,600/year, every year, forever. At 100 users it's $12,000/year. If the app is mostly logic that could run free in Apps Script, that's pure overhead. The rough break-even: below ~10–15 users, AppSheet's UI quality is worth the fee; past ~20 users, Apps Script gets dramatically cheaper over a multi-year horizon.

The hybrid pattern most teams land on

Here's the setup I ship most: AppSheet for the UI, Apps Script for the logic, one Google Sheet underneath. Field staff use the AppSheet app; when something needs real logic, an AppSheet Automation "Call a script" bot hands off to Apps Script.

A concrete example: AppSheet captures a support ticket beautifully, but computing a business-hours SLA due date is the kind of thing its expression language can't express cleanly. So the AppSheet bot calls this, and stores what it returns:

// Apps Script — the logic layer an AppSheet "Call a script" bot invokes,
// passing the ticket's created-at time. Returns the SLA due date to AppSheet.
function slaDueDate(createdAt) {
  return addBusinessHours(new Date(createdAt), 8); // 8 working hours
}

// Add N business hours (Mon–Fri, 09:00–17:00), skipping nights & weekends.
function addBusinessHours(start, hours) {
  const OPEN = 9, CLOSE = 17;
  let d = new Date(start), left = hours;
  while (left > 0) {
    if (d.getDay() === 0 || d.getDay() === 6) {         // weekend -> next day 09:00
      d.setDate(d.getDate() + 1); d.setHours(OPEN, 0, 0, 0); continue;
    }
    if (d.getHours() >= CLOSE) {                         // after close -> next day
      d.setDate(d.getDate() + 1); d.setHours(OPEN, 0, 0, 0); continue;
    }
    if (d.getHours() < OPEN) d.setHours(OPEN, 0, 0, 0);  // before open -> open
    const hoursToClose = CLOSE - d.getHours();
    const take = Math.min(left, hoursToClose);
    d.setHours(d.getHours() + take);
    left -= take;
  }
  return d;
}

I unit-tested addBusinessHours before shipping — the cases that matter are a ticket that rolls past 5 pm to the next morning, one filed on a Friday afternoon landing on Monday, and one filed over the weekend. AppSheet gives you the polished form; Apps Script gives you the logic AppSheet can't. (For the API calls in that logic layer, wrap them with retries — see UrlFetchApp quotas & retries.)

When neither is right

Be honest about the edges. If you need a public, high-traffic consumer product, a real-time collaborative editor, or a system with strict compliance certifications (HIPAA/SOX at scale), you've outgrown the Sheet-backed approach — reach for a proper database and app framework. AppSheet and Apps Script shine for internal and SMB tools, not a venture-scale SaaS.

Wrap-up

Stop framing it as AppSheet or Apps Script. Use AppSheet when the hard part is the interface — mobile, offline, roles, forms. Use Apps Script when the hard part is the logic — APIs, AI, heavy processing, or a headcount where per-user pricing hurts. And when both are hard, run them together on one Sheet: that's the setup most teams quietly end up with.

The production version of the hybrid — the AppSheet bot wiring, the Apps Script logic layer, and the cost model for your headcount — is written up on the MageSheet blog.

Built by the MageSheet team.

UrlFetchApp Quotas & Retries in Apps Script: Backoff, Rate Limits & a Dead-Letter Queue

Hayrullah Kar — Fri, 31 Jul 2026 17:00:13 +0000

A nightly product-enrichment job I built quietly dropped ~300 rows one night. No error, no alert. An upstream API had rate-limited us with a 429, UrlFetchApp.fetch threw, the loop died halfway, and the next run started fresh — the skipped rows just never came back.

Every UrlFetchApp integration hits this eventually. The fix isn't one clever function; it's four small layers that keep a transient failure from becoming silent data loss. Here's the stack I use, with the code.

The quotas you'll actually hit

Before the patterns, know the walls. UrlFetchApp allows 20,000 calls/day on consumer accounts and 100,000/day on Workspace. Each call times out at 60 seconds and caps at 50 MB each way. And the one that surprises people: concurrent calls per script are limited (undocumented, but roughly 10–20), so naive parallelism throws "Service invoked too many times."

The math sneaks up on you: 5,000 SKUs × 4 enrichment calls each = 20,000 — your entire daily budget in one run.

Pattern 1: exponential backoff with jitter

The foundation. The single most important line is muteHttpExceptions: true — without it, a 429 throws before your retry logic can even look at the status code. Retry only on 429 and 5xx; a 400 or 401 won't fix itself, so surface it immediately. The jitter (a random 0–500 ms) stops a batch of scripts from all retrying on the same beat and hammering the API in sync.

function fetchWithRetry(url, options = {}, maxAttempts = 5) {
  options.muteHttpExceptions = true; // critical — without this, retries never run

  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      const response = UrlFetchApp.fetch(url, options);
      const code = response.getResponseCode();

      if (code >= 200 && code < 300) return response;
      if (code === 429 || code >= 500) {
        // Retryable — fall through to backoff
      } else {
        // 4xx other than 429 — do not retry, surface the error
        throw new Error(
          `HTTP ${code}: ${response.getContentText().slice(0, 200)}`
        );
      }
    } catch (err) {
      if (attempt === maxAttempts) throw err;
    }

    // Exponential backoff with jitter: 1s, 2s, 4s, 8s, 16s + random 0-500ms
    const delay = Math.pow(2, attempt - 1) * 1000 + Math.random() * 500;
    Utilities.sleep(Math.min(delay, 30000));
  }

  throw new Error(`fetchWithRetry exhausted ${maxAttempts} attempts on ${url}`);
}

I unit-tested the decision logic before shipping — a 429 retries, a 404 does not, and the backoff delay always lands within its band (base to base + 500 ms) and never exceeds the 30-second cap.

Pattern 2: parallel calls with `fetchAll`

When you have many independent calls, UrlFetchApp.fetchAll runs them in one batch instead of a slow serial loop — and it counts against your quota more efficiently. Keep each batch to ~10–20 requests to respect the concurrency limit.

function enrichProductsBatch(productIds) {
  const requests = productIds.map(id => ({
    url: 'https://api.openai.com/v1/chat/completions',
    method: 'post',
    headers: { Authorization: 'Bearer ' + OPENAI_KEY },
    contentType: 'application/json',
    payload: JSON.stringify(buildEnrichPrompt(id)),
    muteHttpExceptions: true
  }));

  const responses = UrlFetchApp.fetchAll(requests);

  return productIds.map((id, i) => {
    const code = responses[i].getResponseCode();
    if (code >= 200 && code < 300) {
      return { id, ok: true, data: JSON.parse(responses[i].getContentText()) };
    }
    return { id, ok: false, code, error: responses[i].getContentText() };
  });
}

Pattern 3: per-source rate limiting with CacheService

Backoff reacts to a 429 after it happens. Rate limiting stops you from sending it in the first place. This keeps a per-minute counter per API source in CacheService, and sleeps until the next minute if you'd exceed it — so you never trip the upstream limit that gets accounts banned.

function rateLimitedFetch(source, url, options, maxPerMinute) {
  const cache = CacheService.getScriptCache();
  const key = `rl:${source}:${Math.floor(Date.now() / 60000)}`;
  const count = parseInt(cache.get(key) || '0', 10);

  if (count >= maxPerMinute) {
    Utilities.sleep(60000 - (Date.now() % 60000)); // wait until next minute
    return rateLimitedFetch(source, url, options, maxPerMinute);
  }

  cache.put(key, String(count + 1), 90); // 90s TTL covers the boundary
  return fetchWithRetry(url, options);
}

The key is bucketed by minute (Math.floor(Date.now() / 60000)), so each new minute starts a fresh count automatically — no cron, no cleanup.

Pattern 4: a dead-letter queue for exhausted retries

This is the layer that would have saved my 300 rows. When all retries fail, don't throw into the void — write the failed call to a sheet so nothing is lost and you can replay it later.

function safeFetch(source, url, options) {
  try {
    return rateLimitedFetch(source, url, options, RATE_LIMITS[source]);
  } catch (err) {
    appendToDeadLetterQueue({
      timestamp: new Date(),
      source,
      url,
      payload: options.payload || '',
      error: err.message,
      attempts: 5
    });
    return null;
  }
}

function appendToDeadLetterQueue(entry) {
  const sheet = SpreadsheetApp.openById(DLQ_SHEET_ID).getSheetByName('failed_calls');
  sheet.appendRow([
    entry.timestamp, entry.source, entry.url,
    entry.payload, entry.error, entry.attempts
  ]);
}

safeFetch is the only function the rest of your code calls. Rate limiting, retries, and failure capture all sit behind it — your business logic just calls safeFetch('openai', url, opts) and gets a response or null.

Anti-patterns that cause outages

Retrying without muteHttpExceptions. The exception fires before your code sees the status; your retry loop never runs.
Retrying 4xx. A 400/401/403 is a bug or a bad key — retrying just wastes quota and delays the real fix.
Backoff with no jitter. A fleet of scripts retrying on the exact same 1s/2s/4s beat is a self-inflicted DDoS on the upstream API.
A serial loop for thousands of calls. You'll hit the 6-minute execution limit long before you finish — batch with fetchAll.
Swallowing failures silently. Without a dead-letter queue, an exhausted retry is data you'll never know you lost.

Wrap-up

Resilient outbound calls in Apps Script aren't about one function — they're four layers: rate-limit before you send, back off with jitter when you're throttled, batch what you can, and capture what still fails. Wrap them behind a single safeFetch and every integration you write inherits it. In steady state that turns a scary "the job died again" into a boring ~0.5% of calls landing in a queue you replay on your own schedule.

The production version — quota-burndown alerts, a DLQ replay job, and idempotency keys for safe retries — is written up on the MageSheet blog.

Built by the MageSheet team.