Lesson 7.3 — Build Your First Real Automation: Invoice Extraction

Spreadsheet and documents on a desk

Theory gets you started. Building something real gets you there. This lesson walks through a complete, practical automation step by step: extracting key data from invoice emails and logging it automatically to a Google Sheet. This is a real workflow that saves meaningful time for freelancers, small business owners, and operations teams.

By the end of this lesson you will have a working automation — not a demo, not a conceptual outline, but something you can actually use.

What We're Building

The automation: When an invoice email arrives in Gmail, the automation extracts the supplier name, invoice number, amount, due date, and any payment instructions using AI. It then adds this data as a new row in a Google Sheet and sends you a Slack (or email) notification.

Why this matters: Manually logging invoices is tedious, error-prone, and easy to forget. Automating it means your invoice tracker is always up to date, you have an audit trail, and nothing falls through the cracks.

What you need:

A Gmail account (with some real or test invoice emails)
A Google Sheet (we'll create this)
A Zapier account (free to start, but we'll need a multi-step Zap which may require a trial)
An OpenAI API key (free credit for new accounts)

Step 1: Prepare Your Google Sheet

Create a new Google Sheet called "Invoice Tracker."

Add these column headers in row 1:

A: Date Logged
B: Email Subject
C: Supplier Name
D: Invoice Number
E: Amount
F: Currency
G: Due Date
H: Payment Instructions
I: Original Email Date
J: From (sender)

Leave the rest blank — Zapier will add rows automatically.

Pro tip: Freeze row 1 (View → Freeze → 1 row) so headers stay visible as data accumulates.

Step 2: Set Up a Gmail Label

In Gmail, create a label called "Invoices." You can:

Manually apply this label to invoice emails
Set up a Gmail filter to auto-apply it (in Settings → Filters → Create new filter — filter on keywords like "invoice," "payment due," "please find attached invoice")

For this walkthrough, start by manually applying the label to 3–5 test invoices so you can test the automation.

Step 3: Get Your OpenAI API Key

Go to platform.openai.com and create an account (or log in)
Navigate to API Keys (top right menu)
Click "Create new secret key" and give it a name like "Zapier Invoice Bot"
Copy the key immediately — you will not be able to see it again
Save it securely (a password manager works well)

New OpenAI accounts receive $5 in free credit. Processing invoice emails costs a fraction of a cent each, so this will last a long time.

Step 4: Build the Zap

Log into Zapier and click "Create Zap." Give it a name: "Invoice Email → Google Sheets."

Trigger: New Labelled Email in Gmail

App: Gmail
Event: New Labelled Email
Account: connect your Gmail account (follow Zapier's OAuth prompt)
Label: Invoices
Click "Test trigger" — Zapier will fetch a recent labelled email

You should see your test invoice email appear. Check that the Subject, Body Plain, From, and Date fields are populated. These are what we'll pass to the AI.

Action 1: Extract Data with OpenAI

App: OpenAI
Event: Send Prompt (or "Conversation" depending on your Zapier version)
Connect your OpenAI API key

Model: GPT-4o-mini (faster and cheaper; accurate enough for structured extraction)

Prompt — paste this exactly, then use Zapier's field-mapping to insert the email data:

You are an invoice data extraction assistant. Extract the following information from the email below.

Return ONLY a JSON object with these exact keys:
{
  "supplier_name": "company name of the sender",
  "invoice_number": "invoice or reference number",
  "amount": "total amount as a number only, no currency symbol",
  "currency": "currency code e.g. GBP, USD, EUR",
  "due_date": "due date in YYYY-MM-DD format, or 'not specified' if absent",
  "payment_instructions": "any payment method, bank details, or payment link mentioned, or 'none'"
}

If a field cannot be found, use "not found" as the value.
Do not include any explanation — only the JSON object.

Email subject: [INSERT Gmail Subject field]
Email body:
[INSERT Gmail Body Plain field]

Testing this step: Run the test. You should see the AI return a clean JSON object with the extracted fields. If it returns something odd, check that your field mapping is correct and that the email body is being passed in.

Action 2: Parse the JSON (Optional but recommended)

Zapier's "Code by Zapier" step lets you parse the JSON properly if needed. However, for many users the simpler approach is to proceed directly to the Sheets step and use the raw JSON text, or use Zapier's built-in Formatter tool.

Using Zapier Formatter:

App: Formatter by Zapier
Event: Utilities → Extract JSON
Input: map the OpenAI output text
Key: type each key you want to extract (supplier_name, invoice_number, etc.)

This gives you clean, separate fields for each piece of extracted data.

Action 3: Add Row to Google Sheets

App: Google Sheets
Event: Create Spreadsheet Row
Connect your Google account
Spreadsheet: Invoice Tracker
Worksheet: Sheet1

Map the fields:

Date Logged → use Zapier's "Current date/time" utility
Email Subject → Gmail Subject
Supplier Name → extracted supplier_name from AI
Invoice Number → extracted invoice_number
Amount → extracted amount
Currency → extracted currency
Due Date → extracted due_date
Payment Instructions → extracted payment_instructions
Original Email Date → Gmail Date
From → Gmail From

Test this step. A new row should appear in your Google Sheet.

Action 4: Send Yourself a Notification (Optional)

Option A — Gmail:

App: Gmail
Event: Send Email
To: your email address
Subject: "Invoice logged: [map Gmail Subject]"
Body: "Amount: [amount] [currency] | Due: [due_date] | Supplier: [supplier_name]"

Option B — Slack:

App: Slack
Event: Send Channel Message or Send Direct Message
Message: same format as above

Step 5: Publish and Test Live

Turn on the Zap. Apply the "Invoices" label to a real email in your inbox. Wait up to 15 minutes (free tier polling interval) and check your Google Sheet.

If everything worked: you will see a new row with the extracted data.

Troubleshooting Guide

Problem	Likely cause	Fix
No trigger firing	Label not applied; polling delay	Manually test trigger in Zapier; wait 15 minutes
AI returns empty or garbled JSON	Email body not being passed correctly	Check field mapping; use "Body Plain" not "Body HTML"
Wrong amounts extracted	Email contains multiple amounts	Add to prompt: "extract the TOTAL amount only, not line items"
Date format wrong	AI returning locale-specific dates	Add to prompt: "format all dates as YYYY-MM-DD"
Extra text around the JSON	Model adding explanation	Add to prompt: "Return ONLY the JSON. No other text."
Google Sheets row not appearing	Wrong spreadsheet or sheet selected	Double-check Spreadsheet ID and Sheet name in Zapier

Extending the Automation

Once the basic version works, you can expand it:

Add a column for "Paid" and manually check it off when you pay
Set up a second Zap that monitors due dates and sends you a reminder 3 days before
Add filtering so only invoices over a certain amount go into the sheet
Connect to an accounting tool like Xero or QuickBooks if they have Zapier connectors

What You've Just Built

You have built an automation that:

Monitors your email in real time
Uses AI to understand and extract data from unstructured text
Writes that data to a structured database (Google Sheets)
Notifies you automatically

This is the fundamental pattern behind many commercial AI automations — the same logic underpins customer service triage systems, HR document processors, and compliance monitoring tools. You just built a version of it yourself.

Practice Task

Build this automation. If you hit a problem, treat it as a debugging exercise — read the error message carefully, re-check your field mapping, and test each step in isolation. The process of getting a broken automation to work is how you learn automation. The finished product is secondary to the competence you build fixing it.

Building Your First AI Automation