DEV Community: Hayrullah Kar

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.

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

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

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

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

Why the pricing page can't answer the question

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

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

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

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

Put the plans in code, not in a slide

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

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

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

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

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

Count the rows AppSheet will actually carry

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

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

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

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

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

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

Work out the date you cross the ceiling

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

const DAY = 86400000;
const MIN_HISTORY_DAYS = 14;

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

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

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

Pitfalls

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

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

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

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

Wrap-up

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

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

Built by the MageSheet team.

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

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

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

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

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

Why one rate per rep breaks

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

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

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

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

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

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

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

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

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

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

Two decisions in there are worth stating out loud.

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

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

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

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

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

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

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

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

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

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

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

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

3. A clawback is a row, not an edit

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

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

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

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

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

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

What the tests caught

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

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

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

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

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

Pitfalls

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

Wrap-up

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

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

Built by the MageSheet team.

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

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

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

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

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

The cache is a fast path, not a session store

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The gate in front of every server function

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

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

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

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

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

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

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

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

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

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

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

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

What breaks in practice

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

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

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

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

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

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

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

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

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

The takeaway

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

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

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

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

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

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

Reading the code cannot answer the ownership question

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

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

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

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

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

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

Check 1: who actually owns the files

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

What breaks in practice

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

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

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

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

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

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

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

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

The takeaway

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

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

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

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

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

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

The offset is a row count, not a row

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

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

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

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

Keyset pagination: make the position a value

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

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

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

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

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

The sync loop moves the cursor instead.

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

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

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

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

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

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

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

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

Two details in that loop carry the reliability.

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

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

New orders are only half of it

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

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

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

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

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

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

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

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

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

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

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

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

Pitfalls

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

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

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

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

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

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

What this buys you

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

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

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

Built by the MageSheet team.