Categorize expenses and transactions with AI

Northwind’s expenses arrive as a steady drip — a card export here, a few receipts logged by hand there — and every one needs an accounting category before the books balance. Doing it manually is dull, and “Adobe”, “AWS”, and “Figma” all clearly mean software to a human but mean nothing to a sheet.

This script reads the Expenses tab and, for any row that does not yet have a category, asks Claude to pick the right label from a fixed list. The list is the key: by constraining Claude to a known set of categories, the output stays consistent with the chart of accounts instead of inventing a new label every time. Rows that are already categorised are skipped, so a run only touches new expenses.

What you’ll need

• A Google Sheet with one expense per row. Row 1 holds headers including merchant, amount, and an (initially empty) category column.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• The set of categories your bookkeeping uses, edited into the CATEGORIES list so it matches your chart of accounts.

The script

// The accounting categories Claude is allowed to choose from. Keep this
// in sync with your chart of accounts — Claude only picks from this list.
const CATEGORIES = [
  'software', 'travel', 'food', 'office', 'contractor', 'marketing', 'other',
];

// The spreadsheet that holds the Expenses tab.
const EXPENSES_SHEET_ID = '1abcExpensesId';

/**
 * Reads the Expenses sheet and fills the "category" column for any row
 * that does not already have one.
 */
function categoriseExpenses() {
  const sheet = SpreadsheetApp.openById(EXPENSES_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split the header off the data rows.
  const [h, ...rows] = sheet.getDataRange().getValues();

  // 2. Map header names to column indexes so columns are addressed by name.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. Bail out if there is nothing to categorise.
  if (!rows.length) {
    Logger.log('No expenses to categorise — nothing to do.');
    return;
  }

  // 4. Walk each row, skipping any that already has a category.
  rows.forEach((r, i) => {
    if (r[col.category]) return;

    // 5. Constrain Claude to the fixed category list and ask for the label only.
    const prompt = `Categorise this Northwind expense as one of: ` +
      `${CATEGORIES.join(', ')}. Return only the label.\n\n` +
      `Merchant: ${r[col.merchant]}\nAmount: £${r[col.amount]}`;

    // 6. Write the reply back, lower-cased to match the list exactly.
    sheet.getRange(i + 2, col.category + 1)
      .setValue(callClaude(prompt).toLowerCase());
  });

  Logger.log('Finished categorising expenses.');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 *
 * @param {string} prompt The full prompt to send.
 * @return {string} Claude's reply, trimmed.
 */
function callClaude(prompt) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model: 'claude-haiku-4-5-20251001',
      max_tokens: 20,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. categoriseExpenses opens the Expenses spreadsheet and reads every row, splitting the header off the data with a destructuring assignment.
2. It builds a col map of header name to column index, so merchant, amount, and category can be addressed by name rather than a number.
3. If there are no data rows, it logs a message and stops — no wasted API calls.
4. It walks each row and skips any that already has a category, so re-running is cheap and only new expenses get sent to the API.
5. For each uncategorised row it builds a prompt that lists the allowed CATEGORIES and gives Claude the merchant and amount, asking for the label only — max_tokens: 20 keeps the reply short.
6. It lower-cases the reply before writing it, so the value always matches a member of CATEGORIES exactly even if Claude capitalises it.

Example run

Say the Expenses sheet has three new uncategorised rows:

merchant	amount	category
Figma	144
Eurostar	89
Pret A Manger	12

After categoriseExpenses, the category column is filled:

merchant	amount	category
Figma	144	software
Eurostar	89	travel
Pret A Manger	12	food

Every label is a member of CATEGORIES, ready to pivot or sum by category.

Trigger it

Card exports trickle in, so run this on a schedule rather than by hand:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose categoriseExpenses, a Time-driven source, and a Day timer — an overnight run keeps the sheet current each morning.

To run it on demand instead, add a custom menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Bookkeeping')
    .addItem('Categorise expenses', 'categoriseExpenses')
    .addToUi();
}

Watch out for

• Ambiguous merchants land in “other”. A merchant name with no obvious category — a one-off supplier, a personal name — will often get other. Treat other as a review queue, not a final answer.
• Keep CATEGORIES and the chart of accounts in sync. Claude only picks from the list. Add a category your bookkeeping needs and it can be chosen; leave it out and matching expenses get crammed into the nearest existing label.
• Merchant alone is thin context. The prompt sends merchant and amount only. If your sheet has a description or memo column, add it to the prompt — more context means fewer misfires.
• Re-running never re-categorises. Filled cells are skipped, so to fix a wrong label clear the cell first, then re-run.
• Long sheets can time out. Apps Script caps a run at six minutes. For thousands of rows, process the sheet in batches across scheduled runs.