The brief: replace manual bookkeeping data entry with an AI back office.

The client wanted to automate the most tedious part of finance operations: getting data out of financial PDFs and into a usable, queryable form. The goal was a system that ingests invoices, expense receipts, and bank statements, uses AI OCR to extract the right fields from each, categorizes and stores everything in spreadsheets, and then lets a user interrogate all of it in plain English. We delivered this as a 77-node n8n workflow organised as a "virtual accounting department" — four AI roles, each owning one financial function, all writing to a shared Google Sheets backbone.

Role 1 — Invoice processing. Users upload invoice PDFs through a chat interface. The file is saved to a Google Drive "Invoices" folder, then uploaded to Google Gemini's File API, where Gemini performs OCR and extracts structured data: vendor name, invoice number, invoice and due dates, total and VAT amounts, and itemised line items. The AI's response is parsed and cleaned (stripping markdown fences and gracefully handling malformed JSON), the line items are split into individual spreadsheet rows for item-level tracking, and the data is written to an invoice-records sheet. The original file is then automatically renamed to a consistent {Vendor Name} - {Invoice Number} format, and a confirmation message is sent back to the user.

Role 2 — Expense receipt processing. A Google Drive "Expense Receipts" folder is monitored every minute for new uploads. Each new receipt PDF is sent to Gemini, which extracts merchant name, transaction date, total and tax amounts, payment method, and any line items. The extracted expense then passes to an LLM (GPT-4.1 via OpenRouter) that performs context-aware categorization, assigning it to one of 17 predefined expense categories, before the structured, categorised record is logged to an expenses sheet. This turns a pile of receipts into clean, analysis-ready, categorised data with no human touch.

Role 3 — Bank statement processing. A "Bank Statements" folder is similarly watched. New statement PDFs are downloaded and passed to Gemini, which extracts multiple transactions per document — transaction date, description/payee, debit and credit amounts, currency, and running balance. Because a statement contains many rows, a custom JavaScript parser cleans the AI output (handling several possible response shapes) and converts it into individual transaction objects, which are written to a bank-transactions sheet ready for reconciliation.

Role 4 — Financial analysis agent. Sitting on top of the three datasets is an AI agent acting as a finance analyst. It has read access to all three Google Sheets — invoices, expenses, and transactions — as tools, and answers natural-language queries such as "what are the last ten bank transactions?", "what's the total value of invoices for December?", or "list all unpaid invoices." This is the payoff that turns recorded data back into usable insight without manual spreadsheet work.

Why this design works. Splitting the system by document type — each with its own trigger, extraction prompt, and destination sheet — is what makes it reliable: each "role" is tuned for the structure of its documents rather than forcing one generic parser to handle everything. Gemini's File API does the heavy lifting of reading varied real-world layouts, while a dedicated categorization model and a custom multi-row parser handle the parts where raw OCR isn't enough. Throughout, the workflow cleans AI output defensively and continues on non-critical errors (like a failed rename) so a single hiccup doesn't halt processing. Google Sheets acts as both the system of record and the data source the analysis agent queries, keeping everything in one transparent, auditable place. The result is a genuine AI back office: invoices arrive by chat and receipts and statements by folder drop, and within moments they become structured, categorised, queryable financial data — with a CFO-style agent ready to answer questions on demand.