Manually processing supplier invoices is a grind that founders, ops managers, and finance leads dread every week. Opening emails, grabbing attachments, keying in line descriptions, and typing totals into spreadsheets invites transcription errors. Payments get delayed. By setting up a practical pipeline with n8n workflow automation templates, you can automatically capture incoming billing attachments, read them with OpenAI vision, and write clean line items straight into Google Sheets.
Workflow at a glance
| Dimension | Specification |
|---|---|
| Problem it solves | Manual transcription of invoice details and line items from incoming email attachments into spreadsheets. |
| Manual steps it replaces | Opening Gmail messages, downloading invoice images, reading totals and line items, switching tabs to Google Sheets, and typing data row by row. |
| Trigger | Gmail Trigger node polling or listening for new emails matching a specific search query (e.g., invoices with attachments). |
| Output | Clean, validated rows appended to a designated Google Sheets worksheet for every invoice line item. |
| Tools | n8n (self-hosted or cloud), Gmail API, OpenAI API (Vision-capable model), Google Sheets API. |
| Skill level needed | Intermediate (JSON manipulation, basic prompt structuring, OAuth configuration). |
| Complexity | Medium: involves handling binary attachment streams, multimodal API calls, JSON array parsing, and multi-row spreadsheet appends. |
What you need before you start
- An active n8n instance: A running self-hosted setup (Docker works great here) or an n8n Cloud account with rights to create workflows. Check out existing integration setups in n8n community workflow templates to see typical building blocks.
- Google Cloud Project & Gmail Credentials: Set up a Google Cloud project with the Gmail API enabled. For OAuth2, configure your scopes carefully: fetching invoice attachments requires the https://www.googleapis.com/auth/gmail.readonly scope.
- Google Sheets Credentials: Enable the Sheets API in your console and connect it in n8n via OAuth2. Create a sheet called
Invoices_Ledgerwith headers forMessage ID,Invoice Number,Vendor Name,Invoice Date,Line Description,Quantity,Unit Price,Line Total, andTax. - OpenAI API Key: A funded platform account with access to vision-capable multimodal models.
- Sample Test Data: A clear invoice image to test against. Make sure it's an accepted format: OpenAI's image guide lists PNG, JPEG, WEBP, and non-animated GIF as supported image inputs.
How the workflow runs
- Trigger (Gmail Trigger): Polls your inbox every 5 minutes using
has:attachment (filename:png OR filename:jpg OR filename:jpeg OR filename:webp). The node pulls core metadata likeid,threadId, and headers. - Fetch Attachment (Gmail Node): Calls the API to grab the raw binary file using the message and attachment IDs.
- Binary Validation & Base64 Encode (Code Node): Makes sure the MIME type actually works with vision models, then preps the base64 payload.
- Extract Invoice Data (OpenAI Node): Sends the encoded image alongside a strict extraction prompt demanding structured JSON.
- Normalize & Flatten (Code Node): Takes that raw JSON and splits individual invoice items into separate items. One item per row.
- Deduplication Check (Google Sheets Node): Reads your sheet so you don't log the same
Message IDtwice. - Append Rows (Google Sheets Node): Writes each parsed line item into the ledger.
Build it step by step
Step 1: Set up the Gmail Trigger node
Ready to start? Head to your canvas, hit Add Node, and drop in the Gmail Trigger.
Configure these settings:
- Poll Times: Every 5 minutes.
- Event: Message Received.
- Filters: Use
has:attachment (filename:jpg OR filename:jpeg OR filename:png OR filename:webp) label:inboxin the Search Query field. - Download Attachments: Flip this toggle on. n8n needs the actual raw binary payload to pass downstream.
Make sure your OAuth credentials include read access. Behind the scenes, Gmail's messages.attachments.get endpoint retrieves an attachment using userId, messageId, and attachment id.
Check: Shoot a test email containing a receipt image over to your inbox. Click Test step in n8n. You should see an output payload with fields like id and subject, plus a populated binary tab holding your image details.
Step 2: Validate the attachment format
Never trust raw inbound files blindly. Add a Code node right after the trigger and name it Validate Format. This drops unsupported formats before you waste API calls on OpenAI.
for (const item of $input.all()) {
const binaryData = item.binary?.attachment_0;
if (!binaryData) {
throw new Error('No binary attachment found on message: ' + item.json.id);
}
const mime = binaryData.mimeType.toLowerCase();
const validMimes = ['image/png', 'image/jpeg', 'image/webp', 'image/gif'];
if (!validMimes.includes(mime)) {
throw new Error('Unsupported image format: ' + mime);
}
}
return $input.all();Check: Run the node with your test email data. It should complete without errors, passing the binary data along to the next step.
Step 3: Query OpenAI Vision with Structured Output
Next up: data extraction. Add an OpenAI node (or an HTTP Request node pointed at https://api.openai.com/v1/chat/completions). In the node settings:
- Model: Choose
gpt-4oor an equivalent vision-capable model. - Messages: Create two messages: System and User.
- System Message: Instruct the model to behave as a zero-fluff AP extraction engine and return nothing but raw JSON.
- User Message: Pass the base64 image string from your binary stream using the expression
{{ $binary.attachment_0.data }}. - Temperature: Drop this to
0.1to keep answers grounded.
Grab the structured prompt text and JSON schema straight from the Prompts and templates section below.
Check: Run a quick test on the node. You want a clean JSON string in the assistant response containing vendor info, invoice dates, and an array of individual line items.
Step 4: Parse and explode line items into rows
In practice, this means turning one API response into several spreadsheet entries. Invoices usually have multiple rows, so we need to break that nested JSON array into distinct n8n data items. Add a Code node called Format Line Items.
const results = [];
for (const item of $input.all()) {
const rawText = item.json.message?.content || item.json.choices?.[0]?.message?.content;
if (!rawText) {
throw new Error('No content returned from OpenAI vision node');
}
// Remove potential markdown fences if present
const cleaned = rawText.replace(/```json
?|```/g, '').trim();
const parsed = JSON.parse(cleaned);
const messageId = $('Gmail Trigger').first().json.id;
const vendorName = parsed.vendor_name || 'Unknown';
const invoiceNumber = parsed.invoice_number || 'N/A';
const invoiceDate = parsed.invoice_date || '';
const totalTax = parsed.total_tax || 0;
for (const line of parsed.line_items) {
results.push({
json: {
message_id: messageId,
invoice_number: invoiceNumber,
vendor_name: vendorName,
invoice_date: invoiceDate,
line_description: line.description,
quantity: line.quantity,
unit_price: line.unit_price,
line_total: line.line_total,
total_tax: totalTax
}
});
}
}
return results;Check: Run this node. If your sample invoice has 3 line items, n8n should output 3 separate items, each paired with parent invoice details.
Step 5: Check for duplicate records
Nobody wants duplicate ledger entries. Add a Google Sheets node set to Read Rows to catch them.
- Document: Select your
Invoices_Ledgerspreadsheet. - Sheet: Pick
Sheet1(or your specific tab). - Filter: Set a condition where
Message IDequals{{ $json.message_id }}.
Drop an If node right after this. Check whether the returned array is empty: {{ $json.length === 0 }}. Wire the true branch into your append step.
Check: For an unlogged email, the read operation returns empty, routing execution cleanly through the true branch.
Step 6: Append rows to Google Sheets
Connect a final Google Sheets node to that true branch.
- Operation: Append Row.
- Document: Point it to your
Invoices_Ledgerdocument. - Sheet:
Sheet1. - Mapping Mode: Map your columns manually:
Message ID→{{ $json.message_id }}Invoice Number→{{ $json.invoice_number }}Vendor Name→{{ $json.vendor_name }}Invoice Date→{{ $json.invoice_date }}Line Description→{{ $json.line_description }}Quantity→{{ $json.quantity }}Unit Price→{{ $json.unit_price }}Line Total→{{ $json.line_total }}Tax→{{ $json.total_tax }}
Check: Fire the node. Switch to your browser tab. Your sheet should have fresh rows populated with clean numeric values and metadata.
Prompts and templates
Here are the exact prompts and output schemas to drop into your OpenAI node.
System Prompt
You are an accurate, deterministic optical character recognition (OCR) and financial data extraction assistant.
Your job is to read invoice images and return only valid, minified JSON conforming strictly to the requested schema.
Do not add commentary, markdown fences, notes, or assumptions.
If a field cannot be found, use null or 0 as specified in the schema.User Prompt Template
Extract all metadata and line items from this invoice image.
Return your response in this exact JSON schema:
{
"invoice_number": "string or null",
"vendor_name": "string",
"invoice_date": "YYYY-MM-DD or string",
"currency": "string",
"total_tax": number,
"invoice_total": number,
"line_items": [
{
"description": "string",
"quantity": number,
"unit_price": number,
"line_total": number
}
]
}Expected Output Schema
{
"$schema": "http://json-schema.org/draft-07/schema#",
"title": "InvoiceData",
"type": "object",
"properties": {
"invoice_number": { "type": ["string", "null"] },
"vendor_name": { "type": "string" },
"invoice_date": { "type": "string" },
"currency": { "type": "string" },
"total_tax": { "type": "number" },
"invoice_total": { "type": "number" },
"line_items": {
"type": "array",
"items": {
"type": "object",
"properties": {
"description": { "type": "string" },
"quantity": { "type": "number" },
"unit_price": { "type": "number" },
"line_total": { "type": "number" }
},
"required": ["description", "quantity", "unit_price", "line_total"]
}
}
},
"required": ["vendor_name", "invoice_total", "line_items"]
}Test it before you trust it
Always test your automation against real messiness before leaving it on autopilot.
| Test Scenario | Input Data | Expected Output / Workflow Behavior |
|---|---|---|
| Multi-item detailed invoice | JPEG file showing 6 individual line items and subtotal. | Appends exactly 6 rows to Google Sheets, each sharing the same invoice number and vendor. |
| Duplicate email receipt | Same email processed twice with identical message ID. | Deduplication step identifies existing message ID; append step skips writing duplicate rows. |
| Unsupported file attachment | Email attachment containing a .docx or .zip file. | Validation code node throws explicit error; workflow safely terminates before calling OpenAI. |
| Malformed / unreadable scan | Heavily blurred or corrupted photo where text is unreadable. | OpenAI returns nulls for invoice number and empty line_items array; no corrupt rows written. |
Where it breaks and how to fix it
- Rate Limiting on Google Sheets API: Hit with a 429? Keep in mind Google Sheets API allows 300 read requests per minute per project and 300 write requests per minute per project. If you dump dozens of invoices at once, Google shuts the gate. Turn on Retry on Fail in the Sheets node settings and insert a brief Wait node between loops.
- Sheets Request Timeouts: Long runs can stall out. A Sheets API request that takes more than 180 seconds to process returns a timeout error. Break up massive payloads into chunks of 50 items.
- OpenAI Token Quota Throttling: Images cost tokens. OpenAI states that image inputs are converted into billable input tokens and count toward tokens-per-minute limits. If your team dumps high-res photos into inbox batches, you'll hit TPM limits quickly. Downscale oversized receipts before encoding them.
- Attachment Size Limits: Huge files will choke your runs. OpenAI's image guide states a maximum total request payload of 512 MB and up to 1,500 images per request. Run a compression or resize script on inbound camera scans first.
- Google OAuth Token Expiration: Nodes throw an
Invalid Credentialsalert out of nowhere. Go back to n8n's Credential settings, reconnect your OAuth client or service account, and verify that auto-refresh tokens are working.
Running costs and limits
Tracking quotas keeps you from waking up to broke runs or unexpected bills:
- Gmail API Limits and Costs: Good news: Standard Gmail API use is available at no additional cost. But quota limits still apply. Gmail API quotas include 1,200,000 quota units per minute per project and 6,000 per minute per user per project. Also remember that Gmail's messages.attachments.get method costs 20 quota units per call.
- Google Sheets Limits and Costs: Also free under normal use. Standard Google Sheets API use is available at no additional cost, as long as you respect the 300 read/write operations ceiling per minute.
- OpenAI Vision Pricing: Real costs depend on image resolution and token conversion. OpenAI's image-cost calculator gives an example estimated cost of $0.01229 per image at standard input rates. For shifting prices, check the OpenAI standard API pricing page.
- n8n Execution Limits: Self-hosting n8n? You're only constrained by your machine's hardware. On n8n Cloud, keep an eye on your plan's monthly executions and active triggers.
Variations and extensions
- Add an Approval Gate via Slack or Telegram: Don't want unreviewed entries? Route raw items to a Slack or Telegram node with interactive Approve/Reject buttons first. See n8n scope guard templates for solid routing patterns.
- Route Invoices into Accounting Software: Swap out Sheets for direct QuickBooks, Xero, or Stripe integrations to create draft bills automatically.
- Multi-page PDF Handling: Drop in an Execute Command node running
pdftoppmbefore the vision step to slice multi-page documents into clean images. - Automated Vendor Risk Scoring: Run vendor names against an internal watchlist before writing rows, similar to n8n vendor analysis workflows.
Hand this to ChatGPT or Claude
Drop this prompt into ChatGPT, Claude, or Cursor to adapt the logic to your own stack quickly.
GOAL:
Extract unstructured invoice line items from incoming Gmail attachments using an n8n workflow and OpenAI vision, logging structured rows to Google Sheets.
TRIGGER:
Gmail Trigger polling every 5 minutes on search query:
"has:attachment (filename:jpg OR filename:jpeg OR filename:png OR filename:webp) label:inbox"
STEPS:
1. Gmail Trigger: Poll and download raw attachment binary.
2. Code Node (Validate): Verify MIME type is in [image/png, image/jpeg, image/webp, image/gif].
3. OpenAI Vision Node: Pass base64 image data to gpt-4o with structured extraction prompt.
4. Code Node (Parse): Clean markdown, parse JSON output, explode "line_items" into individual n8n items.
5. Google Sheets Node (Read): Search target sheet for existing message_id to prevent duplicates.
6. If Node: Proceed only if message_id is not already present.
7. Google Sheets Node (Append): Write parsed line items to columns:
Message ID, Invoice Number, Vendor Name, Invoice Date, Line Description, Quantity, Unit Price, Line Total, Tax.
DATA FIELDS:
- message_id: string
- invoice_number: string | null
- vendor_name: string
- invoice_date: string (YYYY-MM-DD)
- line_description: string
- quantity: number
- unit_price: number
- line_total: number
- total_tax: number
PROMPT:
System: Accurate financial OCR assistant returning strictly valid minified JSON without formatting fences.
User: Extract invoice_number, vendor_name, invoice_date, currency, total_tax, invoice_total, and an array of line_items (description, quantity, unit_price, line_total).
ACCEPTANCE TESTS:
- Single-item invoice yields exactly 1 appended row.
- Multi-item invoice with 5 lines yields exactly 5 rows with identical invoice metadata.
- Re-running the same email triggers the deduplication branch and appends 0 rows.
- Unsupported file types throw a handled error in the validation node.
CONSTRAINTS:
- Keep Google Sheets writes under 300 requests per minute.
- Respect OpenAI image input format constraints (PNG, JPEG, WEBP, GIF).
- Ensure deterministic JSON parsing with zero conversational prose from the LLM.Frequently Asked Questions
Can this workflow process multi-page PDF invoices?
Not right out of the box. OpenAI vision models need actual image formats like PNG, JPEG, WEBP, or GIF. To deal with PDFs, drop an Execute Command node running pdftoppm (or a conversion API) ahead of the OpenAI node to rasterize each page.
How do I prevent the workflow from processing receipts multiple times?
Use the Gmail message ID. It works as an immutable unique key. Before writing any row, the workflow checks your spreadsheet to verify the ID isn't already logged. If it finds a match, execution stops right there.
What happens if an invoice has no discernible line items?
Sometimes you just get a flat sum. When that happens, the system prompt tells the model to build a single fallback line using the general description, a quantity of 1, and the full total as the line price.
How can I handle foreign currencies in the extraction?
The extraction schema outputs a dedicated currency key. Add a Currency header in your Google Sheet and map this field directly to capture whether you're billing in USD, EUR, or GBP.
Why did my Google Sheets node return a 429 Too Many Requests error?
Google enforces a strict limit of 300 write requests per minute per project. If you process a batch of receipts packed with line items, you'll hit that ceiling. Use a Split In Batches node in n8n and add a short pause to space out your writes.
Your first step
Set up your Invoices_Ledger sheet in Google Sheets first. Add your headers, wire up the Gmail Trigger in n8n, and run a test to confirm the binary image actually comes through cleanly.