a bot that reads our receipts.

there are two kinds of admin tasks: the ones that genuinely need a human, and the ones a human does because nobody got around to automating them. expense tracking, for us, was the second kind. chris and sarah accumulate receipts — supplier invoices, restaurant bills, scanned petrol slips, the printout from the office supplies store down the road — for quarterly vat returns and end-of-year accounting. doing it by hand wasn't a crisis. but every quarter it was three or four hours of squinting at pdfs and typing numbers into google sheets, and three or four hours that nobody volunteers for.

so we built a bot. an afternoon. about 450 lines of google apps script. here's how it works, file by file, function by function.

the orchestrator

processInbox is the entry point. a 15-minute time-driven trigger fires it; the same function is also wired to a "process inbox now" menu item on the sheet for impatient testing. it grabs a script-wide lock to prevent overlapping runs, walks each owner's inbox folder, and hands every file to processFile_. if anything throws, the file is quarantined to a _NeedsReview folder with the error written into the sheet.

function processInbox() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(LOCK_WAIT_MS)) { console.log('Another run holds the lock — skipping.'); return; }
  try {
    const start = Date.now();
    let processed = 0;
    for (const owner of OWNERS) {
      const files = getInboxFolder_(owner).getFiles();
      while (files.hasNext()) {
        if (Date.now() - start > TIME_BUDGET_MS) return;
        if (processed >= MAX_FILES_PER_RUN) return;
        const file = files.next();
        try { processFile_(file, owner); }
        catch (e) {
          console.error(`Failed on file ${file.getName()}: ${e.stack || e.message}`);
          safeMoveToReview_(file, owner, `Error: ${e.message}`);
        }
        processed++;
      }
    }
  } finally { lock.releaseLock(); }
}

apps script kills any execution that runs longer than six minutes, so we bail at four and let the next trigger pick up the backlog. MAX_FILES_PER_RUN is a safety belt: anthropic 429s or a slow drive call shouldn't be able to hold the lock all the way to the timeout.

one file at a time

processFile_ is where most of the logic lives. it's deliberately linear so any state change reads top-to-bottom:

function processFile_(file, owner) {
  const fileId = file.getId();
  const originalName = file.getName();
  const mime = file.getMimeType();
  const blob = file.getBlob();
  const hash = computeFileHash_(blob);

  const dup = findDuplicate_(hash, fileId);
  if (dup) { /* move to _NeedsReview, log as duplicate, return */ }

  if (!ALLOWED_MIME[mime]) { /* move to _NeedsReview, log as unsupported, return */ }

  const extracted = extractFields_(blob, mime);
  if (!extracted.date || extracted.amount == null) {
    /* claude couldn't parse it; quarantine */
  }

  const dest = monthFolderFor_(owner, extracted.date);
  const newName = destinationName_(extracted, originalName);
  const moved = moveFile_(file, dest, newName);
  appendRow_({ ...extracted, owner, fileId, hash, originalFilename: originalName,
               driveUrl: moved.getUrl(), status: 'ok' });
}

four exits, three of them quarantine paths, one happy path. every exit writes a row to the sheet so nothing is ever silently dropped — even an unreadable file generates a needs_review row pointing back to where the file ended up.

hash-based dedupe

before doing anything else, we sha-256 the file bytes. then scan every month tab plus _Review for a row with the same hash or the same drive file id. either match means "we've seen this before":

function findDuplicate_(hash, fileId) {
  const ss = SpreadsheetApp.getActive();
  const sheets = ss.getSheets().filter(sh => isDataSheet_(sh.getName()));
  for (const sh of sheets) {
    const last = sh.getLastRow();
    if (last < 2) continue;
    const rows = sh.getRange(2, COL.ORIGINAL_FILENAME, last - 1,
                             COL.HASH - COL.ORIGINAL_FILENAME + 1).getValues();
    for (let i = 0; i < rows.length; i++) {
      const [origName, existingId, existingHash] = rows[i];
      if ((fileId && existingId === fileId) || (hash && existingHash === hash)) {
        return { sheet: sh.getName(), row: i + 2, originalFilename: origName };
      }
    }
  }
  return null;
}

the file id check catches "user dragged a sorted file back into the inbox". the hash check catches "user re-uploaded the same pdf from their phone, which got a new file id but identical bytes". what neither catches is "user scanned the same paper receipt twice" — different bytes, same receipt. that would need content-level matching on vendor + amount + date.

the llm call

the actual reading is one anthropic api call per file. claude-haiku-4-5-20251001 is plenty for receipt extraction and costs about $0.0035 per document. we send the file as either a document block (for pdfs — claude reads pdf natively up to 32mb / 100 pages) or an image block (for jpg/png), with a strict-json system prompt:

const EXTRACTION_SYSTEM_PROMPT = [
  'You extract structured expense data from receipts, invoices, and bills.',
  'You MUST respond with a single JSON object and NOTHING else.',
  '',
  'Schema (use null when truly unknown):',
  '{ "date": string|null, "vendor": string|null, "amount": number|null,',
  '  "currency": string, "vat_amount": number|null, "category": string,',
  '  "description": string|null, "payment_method": string|null,',
  '  "confidence": "high"|"medium"|"low" }',
  '',
  'Default currency is EUR (Malta). Dates in DD/MM/YYYY are European.',
  'If not a receipt/invoice/bill: confidence "low", date/vendor/amount null.',
  'NEVER invent values.',
].join('\n');

three things in the prompt that matter more than they look: explicit nullability per field (without "use null when truly unknown" the model fills gaps with plausible-looking junk); a malta / EUR default (otherwise we'd get USD defaults for ambiguous totals — a global model averages over a global market); and a DD/MM/YYYY clause (european receipts use european date order; reminding the model in the prompt cuts mis-parses to roughly zero).

we also harden the parsing: 5xx / 429 responses retry with exponential backoff, and the json parser strips accidental triple-backtick fences just in case the model ignores its instructions on a bad day.

routing the file, routing the row

once we have the date, the file goes to its sorted folder, renamed so the listing browses chronologically:

function moveFile_(file, destFolder, newName) {
  if (newName && newName !== file.getName()) file.setName(newName);
  Drive.Files.update({}, file.getId(), null, {
    addParents: destFolder.getId(),
    removeParents: file.getParents().next().getId(),
    supportsAllDrives: true,
  });
  return DriveApp.getFileById(file.getId());
}

note supportsAllDrives: true on the move — without that flag the same code breaks the moment you put the root folder in a shared drive. the row then goes to the sheet tab matching the receipt's date, via a one-liner sheetForRecord_ that returns either the month tab (lazy-created on first write) or the _Review tab when the date couldn't be parsed.

the formulas (the bot writes formulas, not values)

three columns are sheets formulas the bot drops in per row. each formula references its own row number, computed at write time. total ex. vat is just Amount − VAT with a fallback. paid from joint account is a COUNTIF lookup against a Settings tab. converted amount uses google's built-in historical fx data, so the conversion reflects the rate on the receipt's date — not today's. weekends and holidays don't have a historical lookup, so we fall back to today's spot:

function convertedAmountFormula_(r) {
  const hist = (pair) => `INDEX(GOOGLEFINANCE("CURRENCY:${pair}","price",A${r}),2,2)`;
  const spot = (pair) => `GOOGLEFINANCE("CURRENCY:${pair}")`;
  const branch = (rateExpr) =>
    `IF(E${r}="EUR",D${r}*${rateExpr("EURUSD")},IF(E${r}="USD",D${r}*${rateExpr("USDEUR")},""))`;
  return `=IFERROR(IF(OR(D${r}="",E${r}=""),"",${branch(hist)}),${branch(spot)})`;
}

these are all things that could live in the bot's javascript. doing them in formulas means updates propagate retroactively — add a new card to settings tomorrow, and every past row that referenced it lights up its checkbox.

what bit us

three things, all fixable, all instructive:

clasp clobbered our manifest. clasp create --type sheets writes a default appsscript.json over whatever was in the local folder. our oauth scopes and the drive advanced service config vanished. fix: restore the manifest after clasp create, before the first clasp push.

scope mismatch. we asked for spreadsheets.currentonly (less alarming in the consent screen), then called SpreadsheetApp.openById(...). the broader call requires the broader spreadsheets scope. since the script is container-bound to the sheet anyway, swapping openById for SpreadsheetApp.getActive() made the narrower scope sufficient.

checkbox validation breaks getLastRow() we'd applied requireCheckbox() to the entire "shared?" column on every tab. turns out that data-validation rule writes FALSE into every cell as a side effect — so sh.getLastRow() returned 1000 on a freshly created empty sheet, and the first appended row landed at row 1001. visibly empty but technically full. fix: apply the checkbox rule per-row at write time, and find the real last row by scanning a column we always write (original filename):

function lastRowWithData_(sh) {
  const max = sh.getLastRow();
  if (max < 2) return 1;
  const values = sh.getRange(2, COL.ORIGINAL_FILENAME, max - 1, 1).getValues();
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0]) return i + 2;
  }
  return 1;
}

what's next

the shape we have handles the boring case (receipt in, row out) and the not-quite-boring cases (duplicates, unparseable files, mixed currencies). things we'd add if it kept growing: content-level dedupe on (vendor, amount, date) triples to catch re-scans of the same paper receipt; mapping categories to the accountant's actual chart of accounts; an approval step that notifies us before silently filing anything over a threshold.

but: 450 lines of code, an afternoon, $0.0035 per receipt. for a two-person team accumulating maybe twenty receipts a month, that's the kind of return that makes you wonder what else has been sitting on the "someday" list for too long.