Parse bank-alert emails into an expense ledger

Northwind’s bank emails the team after every card swipe — a one-line alert with the merchant and amount. On their own those alerts are noise: they pile up in an inbox, get skimmed, and are forgotten by the time anyone reconciles the month. The actual spend data is sitting right there, it just never lands anywhere useful.

This script turns that stream of alerts into a tidy ledger. Every half hour it searches for new bank emails, pulls the amount and merchant out of each one with a couple of regular expressions, guesses a spending category from the merchant name, and appends a row to the Expenses sheet. Each captured email is labelled so it is never read twice. By month-end the finance lead has a categorised list instead of a folder of alerts.

What you’ll need

• A Google Sheet with an Expenses tab whose first row is the header date, merchant, amount, category, threadId.
• Transaction alert emails arriving from a known sender address — Northwind’s bank sends them from [email protected]. Swap in your own.
• A rough idea of the wording your bank uses. The regexes below match a “£12.34 … at MERCHANT on” style alert; you may need to adjust them once you have seen a real email (see “Watch out for”).
• Nothing else — the script creates the expenses/captured label itself.

The script

// The spreadsheet that holds your expense ledger.
const EXPENSES_SHEET_ID = '1abcExpensesSheetId';

// The address your bank sends transaction alerts from.
const BANK_SENDER = '[email protected]';

// How far back to look on each run. A small window plus the "captured"
// label means the same email is never processed twice.
const SEARCH_WINDOW = 'newer_than:7d';

// The Gmail label applied to every alert once its data has been saved.
const CAPTURED_LABEL = 'expenses/captured';

// Merchant-name patterns mapped to a spending category. The keys are
// regular-expression fragments; the first match wins.
const CATEGORIES = {
  'figma|adobe|notion|linear': 'software',
  'uber|trainline|airline': 'travel',
  'aws|vercel|cloudflare': 'infra',
  'pret|gail|costa': 'food',
};

/**
 * Searches for new bank-alert emails, extracts the amount and merchant
 * from each, categorises the spend, and appends a row to the Expenses
 * sheet. Captured emails are labelled so they are processed only once.
 */
function parseBankAlerts() {
  // 1. Find alerts from the bank that have not yet been captured.
  const query =
    `from:${BANK_SENDER} ${SEARCH_WINDOW} -label:${CAPTURED_LABEL}`;
  const threads = GmailApp.search(query);

  if (!threads.length) {
    Logger.log('No new bank alerts — nothing to do.');
    return;
  }

  // 2. Make sure the "captured" label exists before we start tagging.
  const captured = GmailApp.getUserLabelByName(CAPTURED_LABEL)
    || GmailApp.createLabel(CAPTURED_LABEL);

  // 3. Walk each alert and pull out the transaction details.
  const rows = [];
  for (const thread of threads) {
    const message = thread.getMessages()[0];
    const body = message.getPlainBody();

    // The amount: a £ figure with two decimal places, e.g. £1,299.00.
    const amountMatch = body.match(/£([\d,]+\.\d{2})/);

    // The merchant: the text between "at" and "on" in the alert.
    const merchantMatch = body.match(/at\s+([A-Z0-9 *&'-]+?)\s+on/i);

    // Skip anything that does not look like a transaction alert.
    if (!amountMatch || !merchantMatch) continue;

    const merchant = merchantMatch[1].trim();
    const amount = parseFloat(amountMatch[1].replace(/,/g, ''));

    rows.push([
      message.getDate(),
      merchant,
      amount,
      categorise(merchant),
      thread.getId(),
    ]);

    // 4. Tag the thread so the next run ignores it.
    thread.addLabel(captured);
  }

  // 5. Append every parsed row to the ledger in one write.
  if (rows.length) {
    const sheet = SpreadsheetApp.openById(EXPENSES_SHEET_ID).getSheets()[0];
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 5).setValues(rows);
  }
  Logger.log(`Captured ${rows.length} transaction(s).`);
}

/**
 * Matches a merchant name against the CATEGORIES patterns and returns
 * the first category that fits, or "uncategorised" if none do.
 */
function categorise(merchant) {
  const name = merchant.toLowerCase();
  for (const [pattern, category] of Object.entries(CATEGORIES)) {
    if (new RegExp(pattern).test(name)) return category;
  }
  return 'uncategorised';
}

How it works

1. parseBankAlerts builds a Gmail search for emails from the bank, within the SEARCH_WINDOW, that do not yet carry the expenses/captured label. If the search finds nothing it logs a message and stops.
2. It looks up the expenses/captured label, creating it the first time the script runs, so every processed alert can be tagged.
3. For each alert it reads the plain-text body and runs two regexes — one for the £ amount, one for the merchant name sitting between “at” and “on”. If either fails to match, that email is skipped rather than guessed at.
4. It cleans the values up: commas are stripped from the amount before parseFloat, and the merchant string is trimmed.
5. categorise maps the merchant to a spending category by testing it against each pattern in CATEGORIES; the first hit wins and anything unmatched becomes uncategorised.
6. Each captured thread is labelled immediately, so a later run never reads it again — the label is the script’s memory.
7. All parsed rows are appended to the Expenses sheet in a single setValues call below the last used row.

Example run

Say two new alerts land in the inbox:

Your card was used: £49.00 at FIGMA INC on 27 Apr.
Your card was used: £6.40 at PRET A MANGER on 27 Apr.

After the next run, two rows are appended to the Expenses sheet:

date	merchant	amount	category	threadId
2026-04-27	FIGMA INC	49	software	18f2a1b9c…
2026-04-27	PRET A MANGER	6.4	food	18f2a1c04…

Both threads now carry the expenses/captured label, so the next run leaves them alone.

Trigger it

This is a background job, so run it on a time-driven trigger rather than by hand:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for parseBankAlerts, choose Time-driven, then Minutes timer and Every 30 minutes.
3. Approve the authorisation prompt the first time it runs.

A 30-minute cadence keeps the ledger close to live without burning quota; the expenses/captured label means overlapping runs cannot create duplicates.

Watch out for

• The regexes are tuned to one bank’s wording. Forward yourself a real alert, check the exact phrasing, and adjust the £([\d,]+\.\d{2}) and at\s+(...)\s+on patterns to match before trusting the output.
• Only the first message of each thread is read. Banks rarely thread alerts, but if yours does, later messages in the same thread will be missed.
• Refunds and credits are not handled. A “£20.00 refund” alert would still be recorded as positive spend — add a sign check if your bank sends those.
• categorise is deliberately simple. New merchants land in uncategorised; review that category occasionally and add patterns to CATEGORIES as the list of suppliers grows.
• A multi-currency card will break the £ match. If Northwind ever spends in euros or dollars, widen the currency part of the amount regex.