DEV Community: Hayrullah Kar

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

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

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

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

Reading the code cannot answer the ownership question

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

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

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

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

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

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

Check 1: who actually owns the files

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

What breaks in practice

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

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

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

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

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

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

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

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

The takeaway

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

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

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

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

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

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

The offset is a row count, not a row

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

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

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

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

Keyset pagination: make the position a value

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

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

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

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

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

The sync loop moves the cursor instead.

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

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

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

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

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

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

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

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

Two details in that loop carry the reliability.

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

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

New orders are only half of it

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

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

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

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

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

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

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

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

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

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

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

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

Pitfalls

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

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

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

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

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

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

What this buys you

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

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

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

Built by the MageSheet team.

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

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

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

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

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

The three silent invalidators

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

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

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

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

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

The integration, with the guard rails

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

What the caching is actually worth

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

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

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

Pitfalls

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

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

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

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

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

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

The takeaway

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

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

Built by the MageSheet team.

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

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

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

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

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

The shape of it

Three pieces, one Google account, no server:

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

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

Setup: one calendar, one sheet, nine settings

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

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

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

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

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

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

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

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

The slot math, as a pure function

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

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

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

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

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

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

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

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

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

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

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

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

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

Booking, with the race closed

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

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

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

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

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

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

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

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

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

Four details that aren't decoration:

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

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

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

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

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

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

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

The form

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

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

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

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

And the wiring that goes in that script tag:

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

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

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

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

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

What actually breaks in production

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

When to just pay Calendly

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

Wrap-up

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

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

Built by the MageSheet team.

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

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

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

What each one actually is

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

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

The decision matrix

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

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

The cost nobody mentions

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

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

The hybrid pattern most teams land on

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

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

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

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

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

When neither is right

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

Wrap-up

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

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

Built by the MageSheet team.

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

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

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

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

The quotas you'll actually hit

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

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

Pattern 1: exponential backoff with jitter

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

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

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

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

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

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

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

Pattern 2: parallel calls with `fetchAll`

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

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

  const responses = UrlFetchApp.fetchAll(requests);

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

Pattern 3: per-source rate limiting with CacheService

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

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

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

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

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

Pattern 4: a dead-letter queue for exhausted retries

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

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

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

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

Anti-patterns that cause outages

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

Wrap-up

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

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

Built by the MageSheet team.

Use Google Sheets as a Translation Database for Your Web App (Apps Script + Next.js)

Hayrullah Kar — Thu, 30 Jul 2026 21:43:53 +0000

Every i18n setup I've seen has the same three-way standoff. Developers want type-safe JSON in the repo. Translators want a familiar tool, not a pull request. Product wants to fix a typo without a deploy. So you either pay $50–$500/month for a localization SaaS, or you copy-paste strings between a translator's spreadsheet and your JSON files until something silently breaks.

For projects under ~1,000 keys, there's a better middle: the spreadsheet is the database. Translators edit a Google Sheet; an Apps Script endpoint serves it as clean locale JSON; your app pulls that at build time. Here's the whole pattern, with the code.

Why a sheet beats a translation service for small projects

A localization SaaS earns its price at scale — dozens of translators, thousands of keys, screenshots and review workflows. A 300-key marketing site doesn't have that problem; it has a coordination problem. A Sheet solves coordination for free: translators already know it, it has revision history and suggested edits built in, and product can change a string in ten seconds. You only add the two things a raw sheet lacks — a clean JSON API and a fallback for missing translations.

The schema: one tab, one row per key

A strings tab, with the key in column A and one column per locale:

key	en	tr	es	fr
hero.title	Welcome	Hoş geldiniz	Bienvenido	Bienvenue
hero.cta	Get started	Başla	Empezar	Commencer

Use dot-notation keys (hero.title) so the JSON nests naturally in your i18n library. Keep a tiny meta tab too: B1 = default locale (en), B3 = version (1.0.0).

The Apps Script endpoint

Deploy this as a Web App (same mechanics as any Apps Script webhook). doGet serves one locale — or all of them — as JSON, and the fallback lives right in the query: an empty cell resolves to the default locale, so a half-translated key never ships blank.

// Code.gs
const SHEET_ID = 'your-sheet-id';

function doGet(e) {
  const locale = (e.parameter.locale || 'all').toLowerCase();
  const result = buildLocaleData(locale);
  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

function buildLocaleData(locale) {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const data = ss.getSheetByName('strings').getDataRange().getValues();
  const headers = data[0];            // ['key','en','tr','es','fr']
  const localeIdx = {};
  headers.forEach((h, i) => {
    if (i > 0) localeIdx[String(h).toLowerCase()] = i;
  });

  const meta = ss.getSheetByName('meta');
  const defaultLocale = meta.getRange('B1').getValue() || 'en';
  const version = meta.getRange('B3').getValue() || '1.0.0';

  if (locale !== 'all' && !localeIdx[locale]) {
    return { error: 'Unknown locale', available: Object.keys(localeIdx) };
  }

  const targets = locale === 'all' ? Object.keys(localeIdx) : [locale];
  const out = { locale, defaultLocale, version, strings: {} };
  if (locale === 'all') targets.forEach(l => out.strings[l] = {});

  for (let row = 1; row < data.length; row++) {
    const key = data[row][0];
    if (!key) continue;
    if (locale === 'all') {
      targets.forEach(l => {
        out.strings[l][key] =
          data[row][localeIdx[l]] || data[row][localeIdx[defaultLocale]];
      });
    } else {
      out.strings[key] =
        data[row][localeIdx[locale]] || data[row][localeIdx[defaultLocale]];
    }
  }
  return out;
}

I unit-tested buildLocaleData before shipping — the cases that matter are an empty cell falling back to the default locale, a blank-key row being skipped, and an unknown locale returning an error with the list of available ones instead of a 500.

Frontend integration: sync at build time

Don't hit the endpoint at runtime — pull the JSON once at build and commit it to public/locales. Your app ships static files, and a Sheet outage can never take your site down.

// scripts/sync-locales.ts
import fs from 'fs';
import path from 'path';

const URL = process.env.LOCALE_SOURCE_URL!;
const LOCALES = ['en', 'tr', 'es', 'fr'];

async function sync() {
  for (const locale of LOCALES) {
    const res = await fetch(`${URL}?locale=${locale}`);
    const data = await res.json();
    const out = path.join(process.cwd(), 'public', 'locales', `${locale}.json`);
    fs.writeFileSync(out, JSON.stringify(data.strings, null, 2));
    console.log(`✓ ${locale}: ${Object.keys(data.strings).length} strings`);
  }
}

sync();

Wire it into prebuild and feed the output to next-intl or i18next like any other JSON. Between deploys, a 5-minute edge cache in front of the endpoint is plenty if you also want a preview environment reading live.

Caching, versioning, and catching missing keys

The version field from the meta tab lets you cache-bust deliberately instead of guessing. And a daily trigger turns "translators forgot three strings" from a production surprise into an email:

// Missing-keys alert — run on a daily time trigger
function alertMissingKeys() {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const data = ss.getSheetByName('strings').getDataRange().getValues();
  const headers = data[0];
  const missing = [];
  for (let r = 1; r < data.length; r++) {
    headers.forEach((h, c) => {
      if (c > 0 && !data[r][c]) missing.push(`${data[r][0]} → ${h}`);
    });
  }
  if (missing.length) {
    MailApp.sendEmail('translators@example.com',
      `${missing.length} missing translations`,
      missing.join('\n'));
  }
}

When to stop using Sheets and pay for Lokalise

Be honest about the ceiling. Sheets stays comfortable up to roughly 1,000 keys; Apps Script serves this at ~50–100 requests/second, which build-time sync never approaches. The real limit is people: past 5 or so active translators, you'll want proper roles, screenshots, and review queues — that's when you export the sheet to CSV and move to Lokalise or Crowdin. Below that, the tooling is the coordination cost, not the value.

Pitfalls

Don't fetch at runtime. Build-time sync means a Sheet outage can't break your live site. Commit the JSON.
Put the fallback in the query, not the client. An empty cell resolving to the default locale (as above) is simpler and safer than fallback logic scattered across components.
Skip blank-key rows. Translators leave empty rows; guard with if (!key) continue; or they become "" keys in your JSON.
Version deliberately. Cache on the version field, not a timestamp, so a typo fix doesn't force a full rebuild and a rebuild isn't skipped when you need it.
Plurals: one row per form. For anything beyond simple counts, store ICU MessageFormat strings and parse them client-side — don't invent your own plural columns.

Wrap-up

For a project under a thousand keys, i18n doesn't need a subscription — it needs a shared editable source and a clean JSON API. A Google Sheet is the first; ~40 lines of Apps Script is the second. Translators get their tool, developers get their JSON, product gets instant edits, and nothing ships blank.

The production version — the edge-cache layer, version pinning, and the full Next.js wiring — is written up on the MageSheet blog.

Built by the MageSheet team.

How to Actually Unit-Test Google Apps Script (Vitest, Mocks & Clasp CI)

Hayrullah Kar — Wed, 29 Jul 2026 16:28:00 +0000

The bug that finally made me set up tests was a tax rounding error that quietly shipped wrong totals for two weeks. It lived in a plain function — no Sheets, no API — the kind of thing a single unit test would have caught in a second. But the project had zero tests, because the last person who tried had hit the wall everyone hits: Jest can't load SpreadsheetApp.

Apps Script is testable. You just can't test all of it the same way. Here's the four-layer approach I use, with the real code — and yes, I ran the examples in this post before publishing.

Why Jest and Vitest choke on Apps Script

Apps Script runs on Google's servers with global services — SpreadsheetApp, DriveApp, GmailApp, UrlFetchApp — that simply don't exist in Node. Point Jest or Vitest at a file that calls SpreadsheetApp.getActive() and it throws ReferenceError: SpreadsheetApp is not defined before a single assertion runs. Coverage tools like Istanbul have the same problem. The script editor, meanwhile, has no test runner at all.

So you don't test Apps Script as one thing. You split it into layers by what each layer needs to run.

The four-layer test pyramid

Most of the logic that actually breaks — date math, status transitions, dedupe, validation, tax calculation, AI prompt assembly — is pure. In real projects that's 50–60% of the code, and it runs in Node with no Apps Script at all. That's the layer you invest in first.

Pattern 1: pure-function isolation

Keep the logic that doesn't touch a Google service in its own file, and make it importable in both runtimes with a module.exports guard.

// pure/orderMath.js — works in both Node and Apps Script
function calculateOrderTotal(items, taxRate, discountCode) {
  const subtotal = items.reduce((s, i) => s + i.price * i.qty, 0);
  const discount = applyDiscount(subtotal, discountCode);
  const taxable = subtotal - discount;
  return {
    subtotal,
    discount,
    tax: round2(taxable * taxRate),
    total: round2(taxable + taxable * taxRate)
  };
}

function applyDiscount(amount, code) {
  if (code === 'WELCOME10') return amount * 0.1;
  if (code === 'VIP25')     return amount * 0.25;
  return 0;
}

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

if (typeof module !== 'undefined') {
  module.exports = { calculateOrderTotal, applyDiscount, round2 };
}

Now it's a normal Vitest test:

// pure/orderMath.test.js
import { describe, it, expect } from 'vitest';
const { calculateOrderTotal } = require('./orderMath.js');

describe('calculateOrderTotal', () => {
  const items = [{ price: 10, qty: 2 }, { price: 5, qty: 1 }];

  it('applies VIP25 discount before tax', () => {
    const r = calculateOrderTotal(items, 0.18, 'VIP25');
    expect(r.subtotal).toBe(25);
    expect(r.discount).toBe(6.25);
    expect(r.total).toBe(22.13);
  });

  it('treats unknown codes as no discount', () => {
    const r = calculateOrderTotal(items, 0.18, 'BOGUS');
    expect(r.discount).toBe(0);
  });
});

That 22.13 is the case that would have caught my rounding bug: subtotal 25, minus a 6.25 VIP discount, plus 18% tax on the remaining 18.75, rounded to two places. The order-total function is the same shape as the one I use in a WhatsApp storefront build — exactly the kind of money math you never want running untested.

Pattern 2: mocking Apps Script services

Some functions have to touch a service. Don't hard-code SpreadsheetApp inside them — pass it in. Then in the test you hand over a fake and assert how it was called.

// services/orderWriter.js
function writeOrderRow(sheetApp, sheetName, orderData) {
  const sheet = sheetApp.getActive().getSheetByName(sheetName);
  const row = [orderData.id, orderData.email, orderData.total, orderData.timestamp];
  sheet.appendRow(row);
  return row;
}

import { vi, describe, it, expect } from 'vitest';

describe('writeOrderRow', () => {
  it('appends a row in the correct order', () => {
    const appendRow = vi.fn();
    const fakeSheet = { appendRow };
    const fakeSpreadsheet = { getSheetByName: vi.fn(() => fakeSheet) };
    const fakeApp = { getActive: vi.fn(() => fakeSpreadsheet) };

    const ts = new Date('2026-04-30T10:00:00Z');
    writeOrderRow(fakeApp, 'Orders', { id: 'O-1', email: 'a@b.com', total: 50, timestamp: ts });

    expect(fakeSpreadsheet.getSheetByName).toHaveBeenCalledWith('Orders');
    expect(appendRow).toHaveBeenCalledWith(['O-1', 'a@b.com', 50, ts]);
  });
});

The trick is dependency injection: the moment a function receives sheetApp as an argument instead of reaching for the global, it becomes testable in Node. This layer catches "wrote the columns in the wrong order" bugs, which are silent and miserable.

Pattern 3: sheet-based integration tests

Some things you can only trust against a real sheet — a dedupe that depends on how Sheets actually stores values, for instance. These run inside Apps Script against a dedicated fixture spreadsheet, with tiny homegrown helpers.

// integration/dedupe.test.gs
function test_dedupe_removes_exact_duplicates() {
  const ss = SpreadsheetApp.openById(TEST_FIXTURE_ID);
  resetSheet_(ss, 'Customers');
  seedFromFixture_(ss, 'Customers', '_TEST_FIXTURES');

  dedupeCustomers(ss.getSheetByName('Customers'));

  const rows = ss.getSheetByName('Customers').getDataRange().getValues();
  assertEquals_(rows.length, 4); // 5 seeded, 1 dupe removed
  assertEquals_(rows[1][1], 'alice@example.com');
}

function resetSheet_(ss, name) {
  const sheet = ss.getSheetByName(name);
  if (sheet.getLastRow() > 1) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
  }
}

function assertEquals_(actual, expected) {
  if (actual !== expected) {
    throw new Error(`Expected ${expected}, got ${actual}`);
  }
}

Always reset and re-seed from a fixture at the start of each test — never trust the sheet's leftover state from the last run.

Pattern 4: clasp-driven CI

To run all of it on every push, you need the pure/mock tests in Node and the integration tests in Apps Script. clasp pushes your code to a dev script; GitHub Actions runs both halves and fails the build if either does.

# .github/workflows/apps-script-tests.yml
on: [push]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Run pure unit tests
        run: npx vitest run

      - name: Auth clasp
        run: echo "${{ secrets.CLASPRC }}" > ~/.clasprc.json

      - name: Push to dev script
        run: npx clasp push --force

      - name: Run integration tests in Apps Script
        run: |
          npx clasp run runAllTests | tee out.log
          grep -q '"FAIL": 0' out.log

That last grep is the part people forget: clasp run can succeed as a command while your tests report failures in their output. Grepping for "FAIL": 0 is what actually makes the CI job go red when a test breaks.

Pitfalls that quietly kill an Apps Script test suite

Reaching for the global inside the function. If a function calls SpreadsheetApp directly, you can't test it in Node. Inject the service.
Trusting clasp run's exit code. It exits 0 even when tests fail — assert on the output (the grep above), or a red suite looks green.
Dirty fixtures. Integration tests that don't reset the sheet pass or fail depending on the previous run. Reset and re-seed every time.
Testing what won't break. You don't need 90% coverage of glue code. Put the effort in the pure logic — that's where the money bugs live.
Reaching for Jest coverage tools. Istanbul/nyc assume a Node runtime; on the Apps Script side they don't apply. Measure coverage on the pure layer only.

Wrap-up

You can't run all of Apps Script in Node — but you can run the half that actually breaks. Isolate pure logic and test it with Vitest, inject services so you can mock them, keep a thin layer of real-sheet integration tests, and wire clasp into CI so every push checks both worlds. The rounding bug that cost me two weeks would have died in a one-line assertion.

The production setup — a shared assertion library, fixture management, and a coverage gate — is written up on the MageSheet blog.

Built by the MageSheet team.

Apps Script 6-Minute Limit: Run Long Jobs Without Cloud Functions

Hayrullah Kar — Mon, 27 Jul 2026 13:31:05 +0000

There comes a point in every Apps Script project where the same error fires three days in a row and quietly ruins your weekend:

Exception: Service Spreadsheets timed out while accessing document...
Exception: Exceeded maximum execution time

You wrote a script that was supposed to import 50,000 Magento orders into a Sheet. It worked on the first 8,000 rows, then died at the six-minute mark with no rollback and no progress checkpoint. Half your data is in, half your sheet is locked, and your two options are (a) rewrite as a Cloud Function or (b) pray.

The truth is that you almost never need to leave Apps Script for long-running work. The 6-minute limit is a hard cap on a single invocation, not on a job. With three patterns — self-rescheduling triggers, chunked state with PropertiesService, and parallel fan-out with LockService — you can run jobs that take hours, survive crashes, and resume from where they left off. All without touching Google Cloud.

Why the 6-Minute Limit Exists (And the Numbers You Need)

Google enforces this limit because Apps Script runs on shared multi-tenant infrastructure. A runaway loop with no upper bound would starve the platform; the timeout is a circuit breaker, not a punishment.

The exact numbers, verified against the Google Workspace quotas page in 2026:

Limit	Free Gmail	Workspace
Per-execution timeout	6 min	6 min (paying does not raise this)
Daily total trigger runtime	90 min	6 hours
Simultaneous executions	30	30
`doGet` / `doPost` timeout	30 sec	30 sec

The 6-minute limit is per invocation. The 90-minute or 6-hour cap is the daily total of all your trigger-driven runs combined. Web app entry points die at 30 seconds — webhook handlers must be fast or hand work off to a queue.

Stack the patterns below, and you can run 14 self-rescheduled 5-minute chunks on a free account, or 60+ on Workspace, all within a single day.

The Three Patterns That Solve 95% of Long Jobs

Self-rescheduling trigger — the workhorse. Process what you can, save a cursor, reschedule.
Chunked state with PropertiesService — when you know the total size and want deterministic progress.
Parallel fan-out with LockService — when chunks are independent and can run side by side.

Pick the simplest that fits.

Pattern 1: The Self-Rescheduling Trigger

This is the 80% pattern. Process within 5 minutes (1-minute safety margin), save progress, and create a one-shot trigger to resume.

const MAX_RUNTIME_MS = 5 * 60 * 1000; // 5 min, with 1 min safety
const PROP_KEY = 'IMPORT_CURSOR';

function processOrders() {
  const start = Date.now();
  const props = PropertiesService.getScriptProperties();
  let cursor = parseInt(
    props.getProperty(PROP_KEY) || '0', 10
  );

  const sheet =
    SpreadsheetApp.getActive().getSheetByName('Orders');
  const totalOrders = getTotalOrderCount();

  while (cursor < totalOrders) {
    if (Date.now() - start > MAX_RUNTIME_MS) {
      props.setProperty(PROP_KEY, String(cursor));
      scheduleSelf('processOrders');
      return;
    }

    const batch = fetchOrderBatch(cursor, 100);
    appendRowsToSheet(sheet, batch);
    cursor += batch.length;
  }

  // Done!
  props.deleteProperty(PROP_KEY);
  cleanupTriggers('processOrders');
  Logger.log('Import complete: ' + cursor + ' orders');
}

function scheduleSelf(handler) {
  cleanupTriggers(handler);
  ScriptApp.newTrigger(handler)
    .timeBased()
    .after(60 * 1000)
    .create();
}

function cleanupTriggers(handler) {
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === handler)
    .forEach(t => ScriptApp.deleteTrigger(t));
}

Two non-obvious points: leave a safety margin (5 minutes, not 6), because the timer does not include the time between the last check and the actual exit. And always clean up old triggers before creating a new one — orphaned schedules hit the 20-trigger project cap.

This single pattern can move a million rows. Slower than Cloud Run, but $0 and zero ops surface.

Pattern 2: Chunked State for Deterministic Progress

If your job has a known total — "rebuild dashboards for 200 stores" — you want progress visibility. Store a JSON state in PropertiesService and surface it in a status sheet.

function rebuildAllDashboards() {
  const start = Date.now();
  const props = PropertiesService.getScriptProperties();
  const state = JSON.parse(
    props.getProperty('REBUILD_STATE') || '{}'
  );

  if (!state.queue) {
    state.queue = getStoreList().map(s => s.id);
    state.completed = [];
    state.failed = [];
  }

  while (state.queue.length > 0) {
    if (Date.now() - start > 5 * 60 * 1000) break;

    const storeId = state.queue.shift();
    try {
      rebuildOneDashboard(storeId);
      state.completed.push(storeId);
    } catch (err) {
      state.failed.push({
        storeId, error: err.message
      });
    }

    props.setProperty(
      'REBUILD_STATE', JSON.stringify(state)
    );
    writeStatusToSheet(state);
  }

  if (state.queue.length > 0) {
    scheduleSelf('rebuildAllDashboards');
  } else {
    props.deleteProperty('REBUILD_STATE');
  }
}

The queue is persisted every iteration, so a crash never loses more than one item. Failures go into state.failed and the script moves on.

Pattern 3: Parallel Fan-Out with LockService

When chunks are independent — enriching 10,000 product descriptions with AI — running them serially is wasteful. Apps Script supports 30 simultaneous executions. Split the work.

function fanOutEnrichment() {
  const totalProducts = 10000;
  const numWorkers = 10;
  const chunkSize =
    Math.ceil(totalProducts / numWorkers);

  for (let i = 0; i < numWorkers; i++) {
    const start = i * chunkSize;
    const end =
      Math.min(start + chunkSize, totalProducts);
    PropertiesService.getScriptProperties()
      .setProperty(
        `WORKER_${i}_RANGE`,
        JSON.stringify({ start, end })
      );

    ScriptApp.newTrigger(`worker${i}`)
      .timeBased()
      .after(i * 1000)
      .create();
  }
}

function workerGeneric(workerIndex) {
  const lock = LockService.getScriptLock();
  const range = JSON.parse(
    PropertiesService.getScriptProperties()
      .getProperty(`WORKER_${workerIndex}_RANGE`)
  );

  for (let i = range.start; i < range.end; i++) {
    const enriched = enrichProduct(i);

    lock.waitLock(30000);
    try {
      writeEnrichedRow(i, enriched);
    } finally {
      lock.releaseLock();
    }
  }
}

LockService serializes writes while letting the slow AI calls run in parallel. This moves a 6-hour serial job down to about 40 minutes.

The Foot-Guns Nobody Warns You About

Trigger orphaning. If your script crashes before cleanupTriggers(), you accumulate triggers forever. Wrap the whole job in try/catch and clean up in finally.

PropertiesService write contention. Two simultaneous writers to the same key silently overwrite each other. Use LockService for parallel writes, or shard keys per worker.

Web app vs trigger timeout confusion. doPost dies at 30 seconds. If your webhook needs 4 minutes of work, the handler must enqueue (write to a Sheet, return 200) and a separate trigger drains the queue.

Quota stacking. Long jobs also burn UrlFetchApp quota, Gmail send quota, and daily trigger runtime. Hitting any one silently kills the rest.

Session state lost between invocations. If your script holds a logged-in session, it does not survive across reschedules. Store the session token in PropertiesService.

When to Stop Fighting and Move to Cloud Run

Move out of Apps Script when:

A single job genuinely needs more than 30 minutes of sustained CPU
You need more than 30 concurrent workers
You have a job DAG with dependencies requiring manual trigger coordination
Your team already has a Go or Node.js skillset and the maintenance cost outweighs the simplicity

Until you hit one of those, the patterns above scale further than people assume. We have seen Magento-to-Sheets pipelines move 200,000 rows nightly on a free Workspace account using pattern 1.

The production versions of these patterns — with retry logic, dead-letter queues, and alerting — are on the MageSheet blog.

Built by the MageSheet team.

Build a WhatsApp Storefront in Google Sheets with OpenAI Function Calling (No Website)

Hayrullah Kar — Mon, 20 Jul 2026 14:17:19 +0000

A café owner asked me for an online store. She didn't want Shopify, a domain, a theme, or a $90/month bill — she wanted customers to message her on WhatsApp, ask what's available, and place an order. That's not a website. That's three functions and a spreadsheet.

Here's the whole thing: an AI that browses your catalog and logs orders over chat, backed by two Google Sheets tabs, for under $20/month. It's about 200 lines of Apps Script, and the three tools that do the work are below — verbatim.

Why function calling is the right tool here

A keyword bot ("reply 1 for menu") falls apart the moment a customer types "what pastries do you have under 50?". A language model handles that phrasing easily — but on its own it can only talk. To actually read your catalog and write an order, it needs tools: functions it's allowed to call.

OpenAI's function calling is exactly that. You hand GPT a list of tool schemas; when it decides it needs data, it returns a tool_call naming the tool and its arguments; your Apps Script runs the matching function and hands the result back. Three tools cover a storefront: list products, look up one product, log an order.

Tool 1: read the catalog

The model calls this when a customer wants to browse. It skips anything inactive or out of stock, and treats a blank stock cell as "unlimited" (handy for services and made-to-order items).

function toolGetProducts(phone, args, lead) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Products');
  if (!sheet || sheet.getLastRow() < 2) {
    return { products: [], note: 'No products configured yet.' };
  }

  const data = sheet.getDataRange().getValues();
  const products = [];
  for (let i = 1; i < data.length; i++) {
    const [sku, name, desc, price, currency, stock, active] = data[i];
    if (active !== true) continue;
    if (stock !== '' && Number(stock) <= 0) continue;
    products.push({
      sku: String(sku),
      name: String(name),
      description: String(desc || ''),
      price: Number(price),
      currency: String(currency || ''),
      in_stock: stock === '' ? null : Number(stock)
    });
  }
  return { count: products.length, products };
}

Tools 2 & 3: look up a product, then log the order

toolGetProduct is a case-insensitive SKU lookup. toolLogOrder is the one that touches money, so it validates first, re-reads the product for the authoritative price (never trusting a price the model might invent), computes the total itself, and stamps a unique order id.

function toolGetProduct(phone, args, lead) {
  const sku = args && args.sku ? String(args.sku).trim() : '';
  if (!sku) return { error: 'sku is required' };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Products');
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    if (String(row[0]).toLowerCase() === sku.toLowerCase()) {
      return {
        sku: String(row[0]),
        name: String(row[1]),
        description: String(row[2] || ''),
        price: Number(row[3]),
        currency: String(row[4] || ''),
        in_stock: row[5] === '' ? null : Number(row[5]),
        active: row[6] === true
      };
    }
  }
  return { error: 'Product ' + sku + ' not found' };
}

function toolLogOrder(phone, args, lead) {
  const sku = args && args.sku ? String(args.sku).trim() : '';
  const qty = args && args.quantity ? Number(args.quantity) : 0;
  const notes = (args && args.notes) ? String(args.notes) : '';

  if (!sku || !qty || qty < 1) return { error: 'sku and quantity required' };

  const product = toolGetProduct(phone, { sku }, lead);
  if (product.error) return product;

  const total = product.price * qty;                       // server computes it, not the model
  const orderId = 'ORD-' + Date.now().toString(36).toUpperCase();

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders').appendRow([
    orderId, phone, product.sku, product.name, qty,
    product.price, total, product.currency, notes,
    'pending', new Date().toISOString()
  ]);

  return {
    success: true, order_id: orderId, sku: product.sku,
    product_name: product.name, quantity: qty, total: total,
    currency: product.currency, status: 'pending'
  };
}

The glue: expose the tools and dispatch the model's call

This is what turns three functions into a storefront. You register each tool with the schema the model sees, and route whatever it calls to the matching handler. The description on each schema is doing real work — it's how GPT knows not to call log_order until the customer has actually confirmed.

const TOOLS = {
  get_products: {
    handler: toolGetProducts,
    schema: {
      name: 'get_products',
      description: 'List the active, in-stock products this shop sells.',
      parameters: { type: 'object', properties: {} }
    }
  },
  get_product: {
    handler: toolGetProduct,
    schema: {
      name: 'get_product',
      description: 'Look up one product by its SKU.',
      parameters: {
        type: 'object',
        properties: { sku: { type: 'string' } },
        required: ['sku']
      }
    }
  },
  log_order: {
    handler: toolLogOrder,
    schema: {
      name: 'log_order',
      description: 'Log a confirmed order. Only call AFTER the customer confirms the item and quantity.',
      parameters: {
        type: 'object',
        properties: {
          sku: { type: 'string' },
          quantity: { type: 'number' },
          notes: { type: 'string' }
        },
        required: ['sku', 'quantity']
      }
    }
  }
};

// When GPT returns a tool call, run the matching handler and feed the JSON back.
function runToolCall(toolCall, phone, lead) {
  const tool = TOOLS[toolCall.name];
  if (!tool) return { error: 'unknown tool ' + toolCall.name };
  let args = {};
  try { args = JSON.parse(toolCall.arguments || '{}'); }
  catch (e) { return { error: 'bad tool arguments' }; }
  return tool.handler(phone, args, lead);
}

The webhook that receives the WhatsApp message (via Twilio) and the loop that calls GPT with TOOLS are the same pattern I covered in Build a WhatsApp Sales Inbox in Google Sheets — a doPost that returns 200 fast and does the model round-trip on the message text. Drop these tools into that loop and you have a store.

I unit-tested the tool logic before shipping — the cases that matter are a blank stock cell reading as unlimited, an out-of-stock item never reaching the catalog, a case-insensitive SKU, and an order with a missing or zero quantity being rejected instead of written.

The economics

For a shop doing under ~500 messages and ~100 orders a month, the running cost breaks down to roughly $5–10 Twilio, $2–5 OpenAI on GPT-4o-mini, and $0–6 Google — call it under $20/month. A Shopify-plus-app stack for the same thing runs $80–140/month combined. The bigger win is that the orders live in your Drive, not a vendor's database.

When NOT to use this

Thousands of SKUs, or a catalog that changes hourly. This shines at 5–30 stable products; past that you want real inventory sync and a database.
Variants, tax rules, and international shipping. The model will happily log an order; it won't handle size/color matrices or VAT correctly.
Taking payment in-chat. This logs a pending order. Bolt on a Stripe payment link if you need money to move; don't fake a checkout.
Letting the model set prices. Note that toolLogOrder re-reads the price from the sheet. Never trust a total the model calculated — recompute it server-side, always.

Wrap-up

An online store, for a lot of small sellers, is three functions: list the catalog, look up an item, log the order — wired to GPT through function calling, backed by two spreadsheet tabs. No website, no monthly platform fee, and the data stays yours.

The production version — Stripe payment links, stock decrement on order, and a merchant dashboard — is written up on the MageSheet blog.

Built by the MageSheet team.

Build a Commission Attribution Engine in Google Sheets (Matching, Splits & Clawbacks)

Hayrullah Kar — Sun, 19 Jul 2026 10:42:16 +0000

A rep pings you: "My payout is $340 short." You open the sheet, and three hours later you find it — the customer paid from ali.veli+billing@gmail.com instead of the address on the account, so the sale never got attributed to anyone.

The math was never wrong. In the commission cleanups I've done for clients, nearly every dispute traced back to this: the payment didn't match the right rep. I'd put it around 95% — matching, not arithmetic. Yet almost everyone builds the calculator first.

I already wrote up the calculator — tiered rates, sales that span bands, refund reversal — in the tiered commission engine post. This post is the layer underneath it: the attribution engine that decides whose sale it was in the first place.

Why matching is where it breaks

A payment arrives from Stripe, Gumroad, or a WhatsApp deal. To pay commission on it you have to answer one question: which rep owns this? And the payment record fights you:

The buyer checked out with a personal address, not the one on the account.
They used a +tag alias, or Gmail dot variations (a.b@ vs ab@).
It's a renewal, and nobody set an owner on the original.
Two reps worked it — a BDR who sourced it and an AE who closed it.

Get this wrong and the calculator faithfully computes the wrong person's commission. That's why the matcher comes first.

1. Normalize the email before you compare anything

Half the misses disappear here. Strip +tags everywhere, and strip dots only for Gmail — because Gmail ignores dots in the local part, while other providers treat them as significant. Getting that distinction backwards silently merges different people.

function normalizeEmail(email) {
  const parts = String(email || '').toLowerCase().trim().split('@');
  if (parts.length !== 2 || !parts[1]) return '';
  let local = parts[0].split('+')[0];                              // drop +tags
  if (parts[1] === 'gmail.com') local = local.replace(/\./g, '');   // gmail ignores dots
  return local + '@' + parts[1];
}

function domainOf(email) {
  return String(email || '').toLowerCase().split('@')[1] || '';
}

Ali.Veli+shop@Gmail.com and aliveli@gmail.com are the same person. a.b@company.com and ab@company.com are not.

2. Match in tiers, and never trust a free domain

Exact email is a certain match. Company domain is a good guess. A free provider domain is not a signal at all — and this is the trap that quietly ruins commission data: if you allow domain matching on gmail.com, every consumer purchase in your system gets assigned to whichever rep happens to own one Gmail contact.

const FREE_DOMAINS = new Set([
  'gmail.com', 'outlook.com', 'hotmail.com', 'yahoo.com', 'icloud.com',
]);

// accounts: [{ email, domain, repId }]
function matchRep(payment, accounts) {
  const email = normalizeEmail(payment.email);
  if (!email) return { repId: null, confidence: 0, method: 'no-email' };

  const byEmail = accounts.find(a => normalizeEmail(a.email) === email);
  if (byEmail) return { repId: byEmail.repId, confidence: 1, method: 'email' };

  const dom = domainOf(email);
  // NEVER match on a free provider — it would assign every consumer sale to one rep.
  if (dom && !FREE_DOMAINS.has(dom)) {
    const byDomain = accounts.find(a => a.domain === dom);
    if (byDomain) return { repId: byDomain.repId, confidence: 0.8, method: 'domain' };
  }
  return { repId: null, confidence: 0, method: 'unmatched' };
}

Returning a confidence rather than a bare repId is what makes this usable. Auto-post anything at 1, route 0.8 to a dispatcher for a one-click confirm, and park 0 in an "unmatched" tab that someone clears weekly. An unmatched row is a known unknown; a wrongly-matched row is a dispute three months later.

3. Clawback windows and split credit

A refund shouldn't always claw back commission. Every commission policy needs a window — typically 30–90 days for refunds and 6–12 months for churn on annual contracts. Outside the window, the rep keeps it. Put that in the policy on day one, and encode it:

function withinClawbackWindow(saleDate, refundDate, windowDays) {
  const days = (new Date(refundDate) - new Date(saleDate)) / 86400000;
  return days >= 0 && days <= windowDays;   // negative = refund dated before the sale
}

function splitCredit(amount, splits) {
  const total = Object.keys(splits).reduce((s, k) => s + splits[k], 0);
  if (Math.abs(total - 1) > 1e-9) {
    throw new Error('splits must sum to 1, got ' + total);
  }
  const out = {};
  Object.keys(splits).forEach(k => { out[k] = amount * splits[k]; });
  return out;
}

splitCredit throws instead of silently normalizing. That's deliberate: a BDR/AE split that adds up to 0.9 means somebody fat-fingered the rules table, and you want to hear about it now rather than discover 10% of a quarter's credit evaporated.

I unit-tested all of this before shipping — the cases that matter are the Gmail dot/+tag variants, a free-provider address refusing to domain-match, a refund dated before its sale, and splits that don't sum to 1.

Pitfalls

Build the matcher before the calculator. A perfect tier engine on wrong attribution just pays the wrong person precisely.
Never domain-match a free provider. It's the single most damaging default in a commission system.
Store confidence, not just a match. Unmatched rows are cheap; wrong matches are disputes.
Write the clawback window into the policy first. Retro-applying one to sales already paid out destroys trust faster than a wrong number.
Splits drift. Re-check the rules table each quarter; a deal that changed hands mid-cycle is the usual culprit.
Give each rep a view of only their own rows. Full visibility into the ledger turns into salary politics.
Know the ceiling. This holds comfortably to roughly 15 reps on Sheets; past that the ledger wants a real database.

Wrap-up

Commission disputes almost never start in the math. They start when a payment can't find its rep — an alias, a personal address, a renewal with no owner. Normalize the email, match in tiers with an explicit confidence, refuse to guess on free domains, and keep unmatched rows visible. The calculator can be simple once the attribution is honest.

The production version — Stripe/Gumroad ingestion, the per-rep tokenized dashboard, and the finance export — is written up on the MageSheet blog.

Built by the MageSheet team.

Build a Real-Time Inventory Dashboard in Google Sheets (Reorder Points + Low-Stock Alerts)

Hayrullah Kar — Fri, 17 Jul 2026 16:57:58 +0000

A marketing campaign works, 150 units sell in a day — and 40 were in stock. Now you have 110 angry customers, refunds, and a marketplace penalty for overselling. The campaign that was supposed to make money just cost you money and trust.

The fix isn't a $200/month inventory app. It's a Google Sheet that knows your reorder point per product, tells the difference between stock you have and stock you can actually sell, and emails you before you run out. This post builds it, with the code.

Why manual stock tracking guarantees a stockout

Across a handful of SKUs you can eyeball it. Across a few hundred, you can't — each product has a different sales rate and a different supplier lead time, so "when do I reorder this one?" is a different answer for every row. Do that math by hand and you will be late on something.

There's one formula that removes the guessing — the reorder point:

ROP = (average daily sales × supplier lead time in days) + safety stock

If a product sells ~10/day, your supplier takes 14 days, and you keep 20 units of buffer: 10 × 14 + 20 = 160. When available stock hits 160, you reorder — and the shipment lands just as you'd otherwise run dry.

Set up an Inventory sheet with these columns and a matching constant:

// Inventory columns:
// sku | name | onHand | allocated | avgDailySales | leadTimeDays |
// safetyStock | available | rop | status
const INV = {
  SKU: 1, NAME: 2, ON_HAND: 3, ALLOC: 4, AVG: 5, LEAD: 6,
  SAFETY: 7, AVAIL: 8, ROP: 9, STATUS: 10,
};

1. The math: reorder point and the stock that actually matters

Three small functions carry the whole dashboard. The one people skip is availableStock — the difference between what's on the shelf and what's already promised to open orders. Overselling happens when you reorder against on-hand instead of available.

function reorderPoint(avgDailySales, leadTimeDays, safetyStock) {
  return avgDailySales * leadTimeDays + safetyStock;
}

function availableStock(onHand, allocated) {
  return onHand - allocated;   // what you can sell, not what's on the shelf
}

function stockStatus(onHand, allocated, rop) {
  const avail = availableStock(onHand, allocated);
  if (avail <= 0) return 'OUT';
  if (avail <= rop) return 'REORDER';
  return 'OK';
}

100 units on hand with 90 already allocated to unshipped orders is not "100 in stock" — it's 10. stockStatus reads the available number, so it flags REORDER while the shelf still looks full.

2. Refresh the whole dashboard in one pass

This recomputes available, ROP, and status for every row and writes them back in a single setValues call — fast, and gentle on Apps Script's quotas. Run it on an hourly trigger, or fire it from the Magento webhook that updates onHand and allocated.

function refreshDashboard() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
  const last = sheet.getLastRow();
  if (last < 2) return;

  const range = sheet.getRange(2, 1, last - 1, INV.STATUS);
  const rows = range.getValues();

  rows.forEach(r => {
    const onHand = Number(r[INV.ON_HAND - 1]) || 0;
    const alloc  = Number(r[INV.ALLOC - 1]) || 0;
    const rop = reorderPoint(
      Number(r[INV.AVG - 1]) || 0,
      Number(r[INV.LEAD - 1]) || 0,
      Number(r[INV.SAFETY - 1]) || 0
    );
    r[INV.AVAIL - 1]  = availableStock(onHand, alloc);
    r[INV.ROP - 1]    = rop;
    r[INV.STATUS - 1] = stockStatus(onHand, alloc, rop);
  });

  range.setValues(rows);   // one write for the whole sheet
}

Reading and writing the whole range once (instead of cell by cell in a loop) is the difference between a dashboard that refreshes in a second and one that trips the 6-minute limit at a few thousand SKUs.

3. Get told before you run out

A dashboard nobody looks at doesn't prevent a stockout. A daily trigger on sendLowStockAlert emails whoever handles purchasing the moment anything crosses into REORDER or OUT.

function sendLowStockAlert() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
  const last = sheet.getLastRow();
  if (last < 2) return;

  const low = sheet.getRange(2, 1, last - 1, INV.STATUS).getValues()
    .filter(r => r[INV.STATUS - 1] === 'REORDER' || r[INV.STATUS - 1] === 'OUT')
    .map(r => `${r[INV.STATUS - 1]}  ${r[INV.SKU - 1]}  ${r[INV.NAME - 1]}  ` +
              `(available ${r[INV.AVAIL - 1]}, reorder point ${r[INV.ROP - 1]})`);

  if (!low.length) return;   // nothing to nag about today

  MailApp.sendEmail({
    to: PropertiesService.getScriptProperties().getProperty('ALERT_EMAIL'),
    subject: `⚠ ${low.length} product(s) need reordering`,
    body: 'Reorder these now:\n\n' + low.join('\n'),
  });
}

I unit-tested the math before shipping — the ROP worked example lands on 160, and the status boundaries (available exactly at the ROP, exactly at zero, and oversold into negatives) all behave.

Pitfalls

Reorder against available, not on-hand. The allocated column is what stops overselling; skip it and the dashboard lies to you while the shelf looks full.
Keep avgDailySales fresh. A number set once goes stale — recompute it from a rolling window (e.g. the last 14–30 days) so a seasonal spike actually moves the reorder point.
Read/write the range once. Cell-by-cell getValue/setValue in a loop is the classic way to hit the 6-minute execution cap on a big catalog.
Dedupe the Magento webhook. Sync events can arrive twice or out of order; key updates by SKU and ignore stale ones.
Know the ceiling. Sheets stays responsive into the thousands of SKUs and low-to-mid six-figure monthly orders; past that, keep the sheet as the dashboard and move the source of truth to a database.

Wrap-up

A real-time inventory dashboard is three ideas: a reorder point per product, the discipline to reorder against available (not on-hand) stock, and an alert that reaches you before the shelf is empty. That's a few dozen lines of Apps Script on a sheet you already own — not a $200/month subscription.

The production version — Magento two-way sync, per-warehouse stock, and supplier-aware lead times — is written up on the MageSheet blog.

Built by the MageSheet team.

DEV Community: Hayrullah Kar

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

Reading the code cannot answer the ownership question

Check 1: who actually owns the files

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

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

What breaks in practice

The takeaway

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

The offset is a row count, not a row

Keyset pagination: make the position a value

New orders are only half of it

Pitfalls

What this buys you

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

The three silent invalidators

The integration, with the guard rails

What the caching is actually worth

Pitfalls

The takeaway

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

The shape of it

Setup: one calendar, one sheet, nine settings

The slot math, as a pure function

Booking, with the race closed

The form

What actually breaks in production

When to just pay Calendly

Wrap-up

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

What each one actually is

The decision matrix

The cost nobody mentions

The hybrid pattern most teams land on

When neither is right

Wrap-up

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

The quotas you'll actually hit

Pattern 1: exponential backoff with jitter

Pattern 2: parallel calls with fetchAll

Pattern 3: per-source rate limiting with CacheService

Pattern 4: a dead-letter queue for exhausted retries

Anti-patterns that cause outages

Wrap-up

Use Google Sheets as a Translation Database for Your Web App (Apps Script + Next.js)

Why a sheet beats a translation service for small projects

The schema: one tab, one row per key

The Apps Script endpoint

Frontend integration: sync at build time

Caching, versioning, and catching missing keys

When to stop using Sheets and pay for Lokalise

Pitfalls

Wrap-up

How to Actually Unit-Test Google Apps Script (Vitest, Mocks & Clasp CI)

Why Jest and Vitest choke on Apps Script

The four-layer test pyramid

Pattern 1: pure-function isolation

Pattern 2: mocking Apps Script services

Pattern 3: sheet-based integration tests

Pattern 4: clasp-driven CI

Pitfalls that quietly kill an Apps Script test suite

Wrap-up

Apps Script 6-Minute Limit: Run Long Jobs Without Cloud Functions

Why the 6-Minute Limit Exists (And the Numbers You Need)

The Three Patterns That Solve 95% of Long Jobs

Pattern 1: The Self-Rescheduling Trigger

Pattern 2: Chunked State for Deterministic Progress

Pattern 3: Parallel Fan-Out with LockService

The Foot-Guns Nobody Warns You About

When to Stop Fighting and Move to Cloud Run

Build a WhatsApp Storefront in Google Sheets with OpenAI Function Calling (No Website)

Why function calling is the right tool here

Tool 1: read the catalog

Tools 2 & 3: look up a product, then log the order

The glue: expose the tools and dispatch the model's call

The economics

When NOT to use this

Wrap-up

Build a Commission Attribution Engine in Google Sheets (Matching, Splits & Clawbacks)

Why matching is where it breaks

Pattern 2: parallel calls with `fetchAll`