# Extract invoice data with AI into Google Sheets, with validation and human approval

> For low invoice volumes: use Gemini in Google Workspace or ChatGPT file upload with a strict extraction prompt (no-code), or an n8n flow from Gmail to an LLM to Sheets (automated). Either way, totals and duplicates are checked by rules, and a human approves every invoice before payment.

- Outcome: Get vendor, invoice number, dates, line items and totals from PDF invoices into a spreadsheet log, accurately enough to approve and pay — without a dedicated AP-automation subscription.
- Replaces: Zapier (task_specific)
- Uses: Google Workspace — Gmail (incoming invoices), Drive (PDF archive), Sheets (invoice log, validation formulas, approval column).; Google Gemini (optional) — Path A: Gemini in Sheets/Drive side panel on eligible Workspace plans, or the Gemini app, to read invoice PDFs.; ChatGPT (optional) — Path A alternative: upload PDFs and run the extraction prompt.; Claude (optional) — Path A alternative: upload PDFs and run the extraction prompt.; n8n (optional) — Path B: Gmail trigger → PDF text → Information Extractor (LLM, JSON schema) → validation → Sheets.
- Extra monthly cost: $5 — Path A (no-code, using an AI assistant or Gemini plan you already pay for) adds $0. Path B adds LLM API usage on top of an n8n instance you already run. $5/month is a conservative ILLUSTRATIVE allowance for ~100 invoices — we did not verify current per-token API prices today; check your provider's pricing page and the token count of a typical invoice.
- Difficulty: moderate; skill: basic; setup: Path A: 1–2 hours. Path B: 1 day including the parallel-run test.
- Evidence: editorial; illustrative example: true; last reviewed 2026-10-13
- Canonical: https://onelesstool.com/workflows/invoice-processing-with-ai

## Good fit for
- Small businesses receiving tens to roughly a hundred supplier invoices a month
- Bookkeepers who key invoices into a spreadsheet or accounting tool by hand today
- Teams already on Google Workspace with Gmail as the invoice inbox
- Anyone using a general-purpose automation tool plus a parser add-on just for invoices

## Not a fit for
- Hundreds or thousands of invoices a month, multi-entity AP, or three-way PO matching — dedicated AP automation is built for this
- Businesses that need audit-ready approval workflows, segregation of duties or e-invoicing compliance (e.g. mandated structured e-invoice formats) — a sheet doesn't provide these
- Organisations whose data policies don't allow financial documents to be sent to an external LLM provider
- Anyone hoping to remove human review — extraction errors must be caught before payment

## Before
Invoices arrive by email. Someone opens each PDF and types vendor, number, dates and amounts into a spreadsheet or accounting system — or pays for an AP-capture tool or a parser add-on chained into an automation platform to do it.

## After
Path A: once or twice a week, drag 5–10 invoice PDFs into ChatGPT, Claude or Gemini with a saved extraction prompt; paste the JSON/TSV into the Invoice log where formulas flag line-sum, total and duplicate problems. Path B: an n8n workflow watches a Gmail label, extracts PDF text, asks an LLM for schema-valid JSON, validates totals and duplicates in a Code node, appends to the same sheet as 'Awaiting approval', and emails the approver. In both paths nothing gets paid until a person has compared the row with the PDF and marked it Approved.

## Steps

### 1. Set up the Invoice log and a Gmail label

1. Create a Google Sheet **Invoice log** with the columns listed in the *Validation rules* artifact (A–P). Add the **O2** (normalised key) and **P2** (flags) formulas and fill down.
2. Add data validation on **K (Status)**: `Awaiting approval, Approved, Rejected, Blocked – duplicate, Paid`.
3. Conditional formatting: `=$P2<>""` → red fill.
4. In Gmail, create a filter that applies the label **AP/Inbox** to mail from your suppliers or sent to your invoices@ address. Save PDFs to a Drive folder **AP/2026** so the log can link to originals (column N).

### 2. Path A (no-code): extract with your AI assistant

Use whichever you already pay for:

- **ChatGPT or Claude:** start a Project (so the instructions persist), paste the *Invoice JSON schema* and the **Extract invoice to JSON** prompt as project instructions, then upload **5–10 PDFs per message**.
- **Gemini in Google Sheets** (on eligible Workspace editions — Business Starter does not include Gemini in Sheets per Google's pricing page): open the side panel in the Invoice log, reference the Drive PDFs and ask for a table using the same field list. Check that it read the right files.

Ask for the result as **JSON and as TSV**; paste the TSV into the log. The formulas then flag any line-sum, total or duplicate issues.

Scanned or photographed invoices: the assistant may misread digits. Mark them `HUMAN_CHECK` and compare every number.

### 3. Path B (automated): build the n8n workflow

Nodes, in order:

1. **Gmail Trigger** — poll every 15–60 min, filter by label `AP/Inbox`, unread only; enable downloading attachments.
2. **Filter** — keep only items with a PDF attachment.
3. **Extract From File** → *Extract From PDF* on the attachment's binary field. (Text-based PDFs only; n8n's docs don't mention OCR, so route scanned PDFs to a model that accepts images/PDFs directly, or to manual entry.)
4. **Information Extractor** with *Schema Type → Define using JSON Schema*, paste the schema artifact, connect your chat model (OpenAI, Anthropic, Gemini). Put the extraction rules from the prompt in the system message.
5. **Google Sheets → Get rows** from the Invoice log, then a **Code** node ("Run Once for All Items") that builds an `existingKeys` array — or simply rely on the sheet's DUPLICATE formula and keep this step simple.
6. **Code** node *Validate invoice* (artifact) — adds `flags`, `line_sum`, `norm_key`, `status`.
7. **Google Drive → Upload** the PDF to `AP/2026`; **Google Sheets → Append row** with the link and status `Awaiting approval` or `Blocked – duplicate`.
8. **Gmail → Send** the approver a summary with the flags and the sheet link.
9. Attach an **Error Workflow** so failures alert you instead of silently dropping invoices.

### 4. Validate every invoice with rules, not vibes

LLMs are good at reading invoices and bad at noticing their own mistakes. The *Validation rules* artifact catches the errors that cost money:

- **Line items sum to subtotal**, and **subtotal − discount + shipping + tax = total** (±0.01).
- **Duplicate invoice number** from the same vendor after normalising (`INV-00123` = `inv 123`) → blocked.
- **Near-duplicates** (same vendor and amount within 7 days, different number) → flagged; double-billing often looks like this.
- **Dates** in the future or more than a year old → flagged.
- **Bank details present on a new vendor or changed from last time** → verify by phone using a number you already have. Invoice fraud usually arrives as a legitimate-looking invoice with new bank details.

Anything flagged gets a human check against the PDF; a clean row still needs approval.

### 5. Human approval before any payment

Set a simple rule and write it at the top of the sheet: **nobody pays from an unapproved row.**

- The approver opens the PDF link (column N), compares vendor, number, total and due date, resolves flags with a note, then sets **Status = Approved** with their name and date.
- Payment is made in your bank or accounting software by someone other than the approver if you have two people (basic segregation of duties).
- After payment, set **Status = Paid**. Once a month, reconcile Paid rows against bank statements.

Use **Data → Protect sheets and ranges** so only approvers can edit columns K–M.

### 6. Parallel-run for one month, then decide

For one month, keep your current process and run the new one alongside it. Track per invoice: fields wrong, flags raised, minutes spent. Move to the new process only if **zero errors reached the 'Approved' state** and the time saved is real. Re-check the extraction prompt whenever a new supplier's layout causes repeated `HUMAN_CHECK` flags — add a one-line hint per problem supplier to the prompt.

## Prompts

### Extract invoice to JSON
Turn one or more invoice PDFs into schema-valid JSON without inventing values.

```text
You are an accounts-payable data-entry assistant. Extract data from the attached document(s) for {{company_name}}.

Return ONLY JSON that validates against this schema (one object per document; if several documents are attached, return a JSON array in the order given):
{{json_schema}}

Rules:
1. Copy values exactly as printed. Do not calculate, infer or 'fix' anything. If a value is not printed, use null (or 0 for tax_total only when the document clearly shows no tax).
2. Dates → YYYY-MM-DD. If the date format is ambiguous (e.g. 03/04/2026), assume {{date_convention}} and add the field to uncertain_fields.
3. Numbers → plain decimals without currency symbols or thousands separators.
4. invoice_number: keep prefixes, dashes and leading zeros exactly.
5. If it is not an invoice (statement, quote, receipt, marketing email), set document_type accordingly and still fill what you can.
6. Set bank_details_present to true if bank account / IBAN / routing details appear anywhere.
7. confidence = low if the scan is blurry, handwritten, cut off or totals don't appear.
No commentary outside the JSON.
```

### Convert extracted JSON to sheet rows
Produce paste-ready TSV in the Invoice log's column order (Path A).

```text
Convert this extracted invoice JSON into TSV rows for my Invoice log. Columns, in order: Received ({{received_date}}), Vendor, Invoice no, Invoice date, Due date, Currency, Subtotal, Tax, Total, Line sum (sum of line_items.amount, rounded to 2 decimals), Status (always "Awaiting approval"), Approver (blank), Approved on (blank), File link ({{file_link_placeholder}}).

{{extracted_json}}

Output only one TSV code block with no header row. Do not change any extracted value; if a field is null, leave the cell empty. After the code block, list any invoice where Line sum ≠ Subtotal or Subtotal + Tax ≠ Total.
```

### Second-look check on a flagged invoice
Re-read a flagged invoice field by field before a human decides.

```text
This invoice was flagged {{flags}} after extraction. Re-read the attached PDF carefully and compare it against the extracted JSON below, field by field.

{{extracted_json}}

Return a table: field | extracted value | value printed on the document (quote it exactly) | match (yes/no). Then say which flag is explained by an extraction error and which reflects a real problem on the invoice itself (e.g. the vendor's own arithmetic is wrong, or it's a duplicate). Do not recommend paying or not paying; just report what the document says.
```

## Templates and scripts

### Invoice JSON schema (schema)

```json
{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "ExtractedInvoice",
  "type": "object",
  "additionalProperties": false,
  "required": ["document_type", "vendor_name", "invoice_number", "invoice_date", "currency",
               "line_items", "subtotal", "tax_total", "total", "confidence", "uncertain_fields"],
  "properties": {
    "document_type":   { "enum": ["invoice", "credit_note", "receipt", "statement", "quote", "not_an_invoice"] },
    "vendor_name":     { "type": "string", "description": "Legal name as printed on the document" },
    "vendor_tax_id":   { "type": ["string", "null"], "description": "VAT/GST/EIN etc. exactly as printed, else null" },
    "vendor_address":  { "type": ["string", "null"] },
    "bill_to_name":    { "type": ["string", "null"] },
    "invoice_number":  { "type": "string", "description": "Exactly as printed, including prefixes and leading zeros" },
    "po_number":       { "type": ["string", "null"] },
    "invoice_date":    { "type": "string", "format": "date", "description": "YYYY-MM-DD" },
    "due_date":        { "type": ["string", "null"], "format": "date" },
    "payment_terms":   { "type": ["string", "null"], "description": "e.g. 'Net 30' as printed" },
    "currency":        { "type": "string", "pattern": "^[A-Z]{3}$", "description": "ISO 4217, e.g. USD" },
    "line_items": {
      "type": "array",
      "items": {
        "type": "object",
        "additionalProperties": false,
        "required": ["description", "quantity", "unit_price", "amount"],
        "properties": {
          "description": { "type": "string" },
          "quantity":    { "type": ["number", "null"] },
          "unit_price":  { "type": ["number", "null"] },
          "amount":      { "type": "number", "description": "Line total before tax, as printed" },
          "tax_rate":    { "type": ["number", "null"], "description": "Percent, e.g. 20 for 20%" }
        }
      }
    },
    "subtotal":        { "type": "number", "description": "Before tax, after line-level discounts" },
    "discount_total":  { "type": ["number", "null"], "description": "Document-level discount as a positive number" },
    "shipping_total":  { "type": ["number", "null"] },
    "tax_total":       { "type": "number", "description": "0 if no tax shown" },
    "total":           { "type": "number", "description": "Amount due as printed" },
    "amount_paid":     { "type": ["number", "null"] },
    "bank_details_present": { "type": "boolean", "description": "true if the document shows bank/payment account details" },
    "confidence":      { "enum": ["high", "medium", "low"] },
    "uncertain_fields": { "type": "array", "items": { "type": "string" },
                          "description": "Names of fields that were hard to read, inferred or missing" }
  }
}

Conventions: numbers are plain decimals (1234.5, not "1,234.50"); credit notes use positive numbers with
document_type "credit_note"; never compute a value that isn't printed — put the field in uncertain_fields instead.
```

### Validation rules + Google Sheets formulas (checklist)

```
VALIDATION RULES — apply to every extracted invoice before it can be approved

Tolerance: 0.01 per amount (0.02 if more than 10 line items, to allow per-line rounding).

R1  Line sum        | SUM(line_items.amount) = subtotal                                   → else "LINE_SUM_MISMATCH"
R2  Total           | subtotal − discount_total + shipping_total + tax_total = total       → else "TOTAL_MISMATCH"
R3  Tax sanity      | if line tax_rate present: tax_total ≈ Σ(amount × rate/100)          → else "TAX_CHECK" (warning)
R4  Duplicate       | same normalised vendor + same normalised invoice_number already in the log
                    | (normalise: upper-case, keep only letters/digits, drop zeros that lead a number)  → "DUPLICATE" (block)
R5  Near-duplicate  | same vendor + same total + invoice_date within 7 days, different number → "POSSIBLE_DUPLICATE"
R6  Dates           | invoice_date not in the future and not > 365 days old; due_date ≥ invoice_date → "DATE_CHECK"
R7  Required        | vendor_name, invoice_number, invoice_date, total present             → else "MISSING_FIELD"
R8  Doc type        | document_type = "invoice" (credit notes routed separately)           → else "NOT_INVOICE"
R9  Confidence      | confidence = "low" or uncertain_fields not empty                     → "HUMAN_CHECK"
R10 Bank details    | bank_details_present = true AND vendor is new, or bank details differ from the vendor master
                    | → "VERIFY_BANK_DETAILS_BY_PHONE" (classic invoice-fraud pattern: call a known number, never the one on the invoice)

Google Sheets versions (Invoice log tab; columns: A Received, B Vendor, C Invoice no, D Invoice date,
E Due date, F Currency, G Subtotal, H Tax, I Total, J Line sum, K Status, L Approver, M Approved on, N File link,
O Norm key, P Flags):
  O2  =UPPER(REGEXREPLACE(B2,"[^A-Za-z0-9]",""))&"|"&REGEXREPLACE(UPPER(REGEXREPLACE(C2,"[^A-Za-z0-9]","")),"(^|[A-Z])0+([0-9])","$1$2")
  P2  =TEXTJOIN(", ",TRUE,
        IF(ABS(J2-G2)>0.01,"LINE_SUM_MISMATCH",""),
        IF(ABS(G2+H2-I2)>0.01,"TOTAL_MISMATCH",""),
        IF(COUNTIF($O$2:$O,O2)>1,"DUPLICATE",""),
        IF(D2>TODAY(),"DATE_CHECK",""))
  (Adjust P2's TOTAL check if you track discount/shipping columns.)
  Conditional formatting on A2:P: custom formula =$P2<>"" → red fill.

APPROVAL RULE: Status may only change to "Approved" when P is empty (or each flag is resolved with a note),
and the approver has opened the original PDF. Payment happens in your bank/accounting system, never from this sheet.
```

### n8n Code node: validate invoice (script)

```javascript
// n8n Code node — "Validate invoice" (mode: Run Once for Each Item)
// Input: the Information Extractor's structured result (n8n puts it under `output`; we also accept a top-level object)
// plus `existingKeys` (array of normalised "VENDOR|INVOICENO" keys) merged in from a Google Sheets lookup step.
const inv = $json.output ?? $json;
const existingKeys = $json.existingKeys ?? [];
const flags = [];
const tol = (inv.line_items?.length ?? 0) > 10 ? 0.02 : 0.01;
const num = (v) => (typeof v === 'number' && isFinite(v) ? v : 0);
const close = (a, b) => Math.abs(a - b) <= tol + 1e-9;

for (const f of ['vendor_name', 'invoice_number', 'invoice_date', 'total']) {
  if (inv[f] === null || inv[f] === undefined || inv[f] === '') flags.push(`MISSING_FIELD:${f}`);
}
if (inv.document_type !== 'invoice') flags.push(`NOT_INVOICE:${inv.document_type}`);

const lineSum = (inv.line_items ?? []).reduce((s, l) => s + num(l.amount), 0);
if ((inv.line_items ?? []).length && !close(lineSum, num(inv.subtotal))) flags.push('LINE_SUM_MISMATCH');

const expectedTotal = num(inv.subtotal) - num(inv.discount_total) + num(inv.shipping_total) + num(inv.tax_total);
if (!close(expectedTotal, num(inv.total))) flags.push('TOTAL_MISMATCH');

const today = new Date(); today.setHours(0, 0, 0, 0);
const invDate = new Date(inv.invoice_date);
if (isNaN(invDate) || invDate > today || (today - invDate) / 86400000 > 365) flags.push('DATE_CHECK');
if (inv.due_date && new Date(inv.due_date) < invDate) flags.push('DATE_CHECK:due_before_invoice');

const norm = (s) => String(s ?? '').toUpperCase().replace(/[^A-Z0-9]/g, '');
const key = `${norm(inv.vendor_name)}|${norm(inv.invoice_number).replace(/(^|[A-Z])0+(?=\d)/g, '$1')}`; // INV-00123 -> INV123
if (existingKeys.includes(key)) flags.push('DUPLICATE');

if (inv.confidence === 'low' || (inv.uncertain_fields ?? []).length) flags.push('HUMAN_CHECK');
if (inv.bank_details_present) flags.push('VERIFY_BANK_DETAILS_BY_PHONE_IF_NEW_OR_CHANGED');

return {
  json: {
    ...inv,
    line_sum: Math.round(lineSum * 100) / 100,
    norm_key: key,
    flags: flags.join(', '),
    status: flags.includes('DUPLICATE') ? 'Blocked – duplicate' : 'Awaiting approval',
  },
};
```

## Limitations
- Extraction errors happen, especially on scans, photos, handwriting, multi-page tables and unusual layouts. Human review before payment is mandatory.
- n8n's Extract From File node reads text from PDFs; its docs don't mention OCR, so scanned invoices need a vision-capable model or manual entry.
- No three-way matching against purchase orders and goods receipts, no approval chains by amount, no vendor master management.
- Doesn't post bills into accounting software by itself; that needs another integration step or manual entry.
- Path A is still manual (upload, paste); it saves typing, not the whole process.
- Gemini features in Sheets/Drive depend on your Google Workspace edition or Google AI plan.

## Risks
- **sensitive_data** (high): Invoices contain bank details, amounts and personal data. OpenAI states it does not train on API, ChatGPT Business or Enterprise data by default; Anthropic states it does not train on commercial-product (Team, Enterprise, API) inputs by default. Consumer plans can differ — check your plan's data settings and your contracts before uploading supplier invoices.
- **reliability** (high): A misread digit or a hallucinated total can lead to an over- or under-payment. Rule-based validation catches arithmetic and duplicates, not a wrong-but-consistent number — the human check against the PDF is the control.
- **security** (medium): Automating the inbox can speed up invoice fraud (fake invoices, changed bank details) if people trust the pipeline. Keep the bank-details verification rule and never pay from a link or number on the invoice itself.
- **compliance** (medium): Tax rules may require retaining original invoices and, in some countries, structured e-invoices. Keep the original PDFs in Drive and check local requirements; the extracted sheet is not a substitute for the original.
- **functionality_loss** (medium): Compared with dedicated AP tools you lose supplier portals, automatic accounting sync, approval routing and audit trails.

## Verification checklist (run before cancelling)
- [ ] On a test set of 30 past invoices (including at least 5 scans and 5 multi-page invoices), every field matches the PDF on at least 28, and every error was caught by a flag or by the human check.
- [ ] Feeding the same invoice twice produces DUPLICATE and the second copy is blocked; 'INV-00123' and 'inv 123' from the same vendor are treated as the same number.
- [ ] An invoice with a deliberately wrong total (edit a test PDF) is flagged TOTAL_MISMATCH.
- [ ] A credit note and a supplier statement are recognised as such and not logged as invoices to pay.
- [ ] Path B: the n8n workflow processed every test email in the AP/Inbox label exactly once over 5 days, and the Error Workflow alerted on a deliberately broken run.
- [ ] Only approvers can edit the Status/Approver columns, and no payment in the parallel month was made from a row without Status = Approved.
- [ ] You have confirmed in writing which AI plan/API is used and that its data terms are acceptable for supplier financial data.

## Savings methodology
Savings = what you pay today for invoice capture (an AP-capture subscription or a parser add-on plus the automation tasks it consumes) − incremental cost of the new path (Path A: $0 with an AI plan you already have; Path B: LLM API usage). Time saved on data entry is real but not counted in dollars. Human review time remains in both cases.

Example (illustrative): A small agency processing ~60 supplier invoices a month with a paid invoice-capture add-on, switching to Path B on an n8n instance it already runs. $30/mo → $5/mo. Assumptions: ILLUSTRATIVE: $30/month for the current invoice-capture tool is an assumed figure, not a verified vendor price — use your own invoice. ILLUSTRATIVE: $5/month is a conservative allowance for LLM API usage at ~60 invoices; actual cost depends on model and pages per invoice and was not verified against a price list today. n8n and Google Workspace are already paid for; no new subscriptions. Human review (~1 minute per invoice) continues in both setups.

## Sources
- n8n docs — Information Extractor node: https://docs.n8n.io/integrations/builtin/cluster-nodes/root-nodes/n8n-nodes-langchain.information-extractor/ (accessed 2026-10-13)
- n8n docs — Extract From File: https://docs.n8n.io/integrations/builtin/core-nodes/n8n-nodes-base.extractfromfile/ (accessed 2026-10-13)
- n8n docs — Gmail Trigger: https://docs.n8n.io/integrations/builtin/trigger-nodes/n8n-nodes-base.gmailtrigger/ (accessed 2026-10-13)
- Gemini in Google Sheets: https://support.google.com/docs/answer/14218565 (accessed 2026-10-13)
- Google Workspace pricing / plan comparison: https://workspace.google.com/pricing (accessed 2026-10-13)
- OpenAI — Business data privacy: https://openai.com/business-data/ (accessed 2026-10-13)
- Anthropic — Is my data used for model training?: https://privacy.claude.com/en/articles/7996868-is-my-data-used-for-model-training (accessed 2026-10-13)