How I Auto-Track Expenses from Gmail Invoices into Google Sheets

Monthly expense tracking went from 30 minutes to 3. AI scans Gmail for receipts, extracts amounts, and appends them to my expense tracker in Google Sheets.

Sounds simple, but there are 5 things that will break if you don't set them up right.

What You'll Learn

• The exact prompt that works (and why the obvious version doesn't)
• 5 common mistakes and their fixes: sheet names, append vs write, currency, duplicates, PDFs

The Prompt That Works

You can copy this directly:

Search Gmail for emails with "receipt" or "invoice" in the subject from the past 30 days. For each one, extract: sender, amount (number only, no currency symbol), date, and what it's for. First check my Google Sheets [spreadsheet_id] sheet name with get. Then append the results as new rows, columns: Date, Source, Amount, Category. Only process emails newer than the last date in the spreadsheet.

AI executes: Gmail search → read each email → extract structured data → Sheets append.

5 Things That Will Break (and How to Fix Them)

1. Sheet name depends on your language

⚠️ Google Sheets default sheet name depends on your account language. English accounts get "Sheet1", Chinese accounts get "工作表1", Japanese get "シート1". If you hardcode "Sheet1" but your account is Chinese, every write fails with "range not found."

✅ Fix: tell AI to run get on the spreadsheet first to find the actual sheet name. That's why the prompt above says "check sheet name with get."

2. Append vs Write

⚠️ write overwrites existing cells. If you use write with a fixed range like A1:D10, you'll overwrite your existing data every month.

✅ Fix: use append. It adds new rows after the last row of data. Run the same prompt every month without worrying about overwriting.

3. Currency symbols break formulas

⚠️ If AI writes "NT$590" into the Amount column, Sheets treats it as text, not a number. Your SUM formulas won't work.

✅ Fix: tell AI to write just the number (590) and put the currency in a separate column. The prompt above says "number only, no currency symbol."

4. Duplicate entries on re-runs

⚠️ If you run this twice in the same month, AI appends the same receipts again.

✅ Fix: tell AI to check the last date in the spreadsheet first and only process emails newer than that date. The prompt above includes this.

5. PDF receipts can't be read

⚠️ Stripe and some services attach PDF receipts instead of putting amounts in the email body. AI can read the email text but not the PDF attachment.

💡 Workaround: these services usually also include the amount in the email subject or a summary line. You'll see the sender but might get "amount not found" for some.

One More Thing

💡 Search by subject, not full text. subject:receipt OR subject:invoice is more precise than searching the entire email body. Body search catches emails that mention receipts in conversation ("I attached the receipt") which aren't actual invoices.

Setup

Needs Gmail + Google Sheets.

OctoDock connects both through one MCP URL — AI searches Gmail, extracts data, and appends to Sheets in one conversation.