Zero-cost AI Invoice Automation
with Gemini & Google Sheets

Drop a PDF invoice into a Google Drive folder. Seconds later the vendor, date, amount, and tax rate are in your spreadsheet and the file is archived under a matching reference ID. No server, no OCR subscription, no copy-paste — the whole pipeline runs inside Google Apps Script on Gemini's free tier, at €0/month. This guide builds it end to end, including the part that makes it interesting: a categoriser that reads your own history and gets more accurate with every invoice you process.

The reference implementation processes German tax invoices, which turn out to be a usefully hard test case — VAT extraction, mixed-rate receipts, bills split across cost centers. Nothing in the architecture is Germany-specific: the same pipeline extracts structured fields from any document type you point it at.

The pipeline

Five stages, one feedback loop. The Brain stage makes the whole system smarter over time — every corrected category becomes a learned rule for the next run.

1 Drive folders

The pipeline hangs off three Drive folders: an inbox that everything flows through, and one archive per fiscal year, mirroring how you'll file taxes. As you create them, copy each folder's ID from its URL (the long alphanumeric string after /folders/) — these go into the CONFIG object in step 3.

Add a new archive folder each January. The script routes files automatically by the invoice date year.

2 Sheet structure

The script writes to a Google Sheet with one tab per fiscal year, named exactly 2025, 2026, etc. The tab name must match — the script routes by year.

The minimum column layout the script expects:

The reference ID in column C is auto-generated as YYYYMMDDNNN (date + 3-digit random). Files in the archive are renamed to match, so every sheet row and every archived file share the same ID.

3 Install & configure

Get a free Gemini API key

1. Go to aistudio.google.com
2. Sign in with your Google account
3. Click Get API key → Create API key
4. Choose a project (or create one — the free tier requires no billing)
5. Copy the AIza... key

Paste the script

1. Open your spreadsheet
2. Extensions → Apps Script
3. Delete the placeholder code and paste the full script (see the "Full script" section at the end of this guide)
4. Save (Cmd+S / Ctrl+S)
5. Reload the spreadsheet — a Taxes menu appears in the toolbar

Store your API key securely

Run Taxes → ⚠️ Setup API Key once. This stores your key in Apps Script's PropertiesService — encrypted at rest, never visible in the source code.

Fill in the CONFIG object

const CONFIG = {
  // No API key here — run 'Taxes → Setup API Key' once to save it securely

  SHEET_ID: 'YOUR_SPREADSHEET_ID',        // from the sheet URL
  INBOX_ID: 'YOUR_INBOX_FOLDER_ID',
  ARCHIVE_2026_ID: 'YOUR_ARCHIVE_2026_FOLDER_ID',
  ARCHIVE_2025_ID: 'YOUR_ARCHIVE_2025_FOLDER_ID',

  // Maps sheet tab ID → archive folder (for the Drive verification check)
  FOLDER_MAPPING: {
    "YOUR_2025_TAB_ID": "YOUR_ARCHIVE_2025_FOLDER_ID",
    "YOUR_2026_TAB_ID": "YOUR_ARCHIVE_2026_FOLDER_ID"
  },

  COL_DESC: 5,         // column E — description
  COL_CAT_DEFAULT: 7,  // column G — category (if "Use Case" header not found)
  CHECK_COL: 8          // column H — Drive file check
};

To find a folder ID: open the folder in Drive, copy the string after /folders/ in the URL. To find a sheet tab ID: right-click a tab → Get link to this sheet — the gid= parameter is the tab ID.

4 First run

1. Drop one invoice PDF into your Inbox folder
2. In the spreadsheet: Taxes → Process Invoices
3. Open View → Logs in Apps Script to watch the run in real time
4. Check the script_log tab — every decision is recorded there

The script writes one row per entry. If the vendor had appeared before with a consistent category, the Brain applies that rule and logs it. If this is a new vendor, the AI's suggestion is used and learned for next time.

5 How the AI extraction works

There's no OCR preprocessing step — Gemini reads the document natively. The script sends the raw file bytes alongside a structured prompt that spells out exactly which fields to return and which rules to follow, and the response comes back as schema-validated JSON. No regex, no parsing guesswork.

The 4-model cascade

Instead of a single model, extractWithAI tries four models in order. If one hits a rate limit or quota error, it falls through to the next — silently, without failing the whole run.

const models = [
  "gemini-3.5-flash",      // best quality; 429 on free quota exhaustion
  "gemini-2.5-flash",      // previous-gen flagship; solid free tier
  "gemini-3.1-flash-lite", // current ultra-fast lite; high free RPD
  "gemini-2.5-flash-lite", // previous-gen lite — reliable final fallback
];

The ordering encodes a simple bet: try the best model first, fall through cheaply. gemini-3.5-flash goes first because it extracts best — and since it fails fast when the free quota can't serve it, the attempt costs nothing. In practice, gemini-2.5-flash ends up doing most of the work. The two lite models cover high-volume days and the occasional model-level outage.

response_schema — why it matters

Without a schema, Gemini returns JSON that usually has the right fields — but sometimes wraps it in markdown fences, uses different field names (taxamount vs totalTaxAmount), or returns amounts as strings with currency symbols. A schema enforces the contract at the API level:

"generationConfig": {
  "response_mime_type": "application/json",
  "response_schema": {
    "type": "OBJECT",
    "properties": {
      "vendor":         { "type": "STRING"  },
      "date":           { "type": "STRING", "description": "YYYY-MM-DD (ISO 8601)" },
      "amount":         { "type": "NUMBER"  },  // ← number, never "€19.99"
      "totalTaxAmount": { "type": "NUMBER"  },  // ← sum of all VAT/MwSt lines
      "taxRate":        { "type": "STRING"  },  // ← "19%", "7%", or "mixed"
      "description":    { "type": "STRING"  },
      "category":       { "type": "STRING"  }
    },
    "required": ["vendor","date","amount","totalTaxAmount","taxRate","description","category"]
  }
}

The safety filters are also set to BLOCK_NONE for all four harm categories — a deliberate choice, not a reckless one. General-purpose filters are tuned for open-ended chat, and ordinary paperwork trips them: a pharmacy receipt or a medical bill reads as health content and the extraction fails. The threat model doesn't apply here anyway — the input is your own financial documents, not untrusted user text.

Mixed-VAT back-calculation

Restaurant receipts and some invoices carry multiple VAT rates (e.g. 7% on food, 19% on drinks). The AI returns the sum of all VAT amounts as totalTaxAmount. The script then back-calculates the effective rate:

// If Gemini gives us the absolute VAT sum, derive the exact blended rate
if (totalTaxRaw > 0 && totalTaxRaw < amount) {
  taxRate = totalTaxRaw / (amount - totalTaxRaw);
}

This means a receipt where you paid €60 total and the VAT lines sum to €5.23 gets written as 0.09549… — the exact blended rate — so a formula like =(B-B/(1+F)) in your tax column returns exactly €5.23.

6 The self-learning Brain

The Brain keeps no database and no stored model — the spreadsheet itself is the training data. Before every run, loadBrainFromHistory re-reads every year tab from scratch and builds two things from the rows already there:

The Brain runs before writing anything. If it finds a match for the vendor, it overrides the AI's category suggestion. This makes the system converge — the more rows you have, the more accurate it gets without any prompt tuning.

7 Dedup & audit trail

MD5 duplicate detection

Filenames can't be trusted for duplicate detection — the same invoice shows up once as an email download and again as a phone scan, under two different names. So the script hashes the raw bytes: each file's MD5 is computed before processing and stored in the script_log tab. If the same file lands in the inbox again — same bytes, same hash — it's moved to a Quarantine folder instead of processed, and the duplicate is logged.

This catches the most common mistake: a file that was already processed but never left the inbox, or the same invoice uploaded twice with different filenames.

Drive verification

After each run, checkFilePresenceForSheet scans every invoice reference in column C and checks whether the corresponding file exists in the archive folder. Missing files turn red. This catches scenarios where a file was written to the sheet but failed to move — rare, but real.

The script_log tab

Every decision the script made is recorded: timestamp, filename, model used, vendor extracted, category applied, and the reason (Brain exact match / Brain fuzzy / AI / split rule). If something looks wrong in the sheet, the log shows exactly how that row was produced.

8 Categories

No code change needed. The script reads your category list dynamically from the dropdown validation on column G (the "Use Case" column) on each run.

Adding a category

1. Click any cell in column G of a year tab
2. Go to Data → Data validation
3. Add your new category to the list of values
4. Apply to the column — the script picks it up on the next run

The AI receives the full current list in its prompt: "category: MUST be one of the valid categories listed." A category that doesn't exist in the dropdown will never appear in the sheet.

9 Vendor split rules

Some invoices cover costs that belong to multiple cost centers. applyVendorSplitLogic can turn a single invoice into two (or more) sheet rows, each with its own amount, description, and category.

The function ships with a commented template — uncomment and adapt it for your situation:

function applyVendorSplitLogic(data) {
  const vendorLower = data.vendor.toLowerCase();

  // Adapt to split a shared bill between properties or cost centers.
  // Example: a combined broadband + mobile contract billed as one invoice.
  // if (vendorLower.includes("your-provider")) {
  //   const FIXED = 29.99; // portion for Cost Center A
  //   return [
  //     { ...data, amount: FIXED,                                              description: "Provider — Cost Center A", category: "Business" },
  //     { ...data, amount: Math.round((data.amount - FIXED) * 100) / 100,      description: "Provider — Cost Center B", category: "Business" },
  //   ];
  // }

  data.description = `${data.vendor} - ${data.description}`;
  return [data];
}

You can add as many if blocks as you need — one per vendor pattern. Each block returns an array of entries; the script writes one row per entry.

10 Automation trigger

Everything so far ran from the spreadsheet menu. A time-driven trigger removes even that step, and the script is built for it: with an empty inbox it exits immediately — an idle run finishes in under a second and costs nothing — so polling every few minutes is free in practice. Drop a file, walk away; the row appears.

Set a time-driven trigger

1. In Apps Script, click the clock icon (Triggers) in the left sidebar
2. + Add Trigger
3. Function: processInvoices
4. Event source: Time-driven
5. Type: Minutes timer → Every 10 minutes (or hourly if your volume is low)
6. Save — Google will prompt for permissions on first run

Check Triggers → Executions for the run history. Apps Script logs errors to this view — a useful first stop when something doesn't show up in the sheet.

The complete script

Paste the full source into your Apps Script editor. After pasting, run Taxes → ⚠️ Setup API Key to store your Gemini key, then fill in the CONFIG values at the top.