Automate PDF Data Extraction Using AI and Zapier

**Zapier Workflow: Automated Invoice Processing from Gmail to Google Sheets & QuickBooks**

**Step 1 — Trigger: Gmail "New Attachment"**
Connect the shared AP inbox. Set the search string to `has:attachment filename:pdf (invoice OR bill OR statement)` to filter only invoice emails. Output: the PDF file, sender, subject, and received date.

**Step 2 — Filter by Zapier**
Only continue if the attachment's file extension is `.pdf` and file size is greater than 10 KB. This prevents signature images or empty attachments from triggering downstream steps.

**Step 3 — Extract Text: PDF Converter by Zapier (or Formatter → Utilities → "Extract Text from PDF")**
Pass the Gmail attachment in. Output: the raw text content of the invoice.

**Step 4 — AI Extraction: "Extract Data with AI" by Zapier (or ChatGPT "Extract Structured Data" action)**
Input the raw PDF text. Define these structured output fields:
- invoice_number
- vendor_name
- vendor_email
- invoice_date
- due_date (format YYYY-MM-DD)
- subtotal
- tax
- total_amount
- currency
- line_items (array: description, quantity, unit_price, line_total)
Prompt instruction: "Return null for any field not clearly present. Do not guess vendor names — use the billing header."

**Step 5 — Formatter by Zapier: Numbers & Dates**
Normalize `total_amount`, `subtotal`, and `tax` to decimal format; convert `due_date` and `invoice_date` to ISO format. This prevents QuickBooks and Sheets formatting errors.

**Step 6 — Filter by Zapier (Data Quality Gate)**
Only proceed if `invoice_number`, `vendor_name`, and `total_amount` are all populated. If they fail, route to Step 9.

**Step 7 — Google Sheets "Create Spreadsheet Row"**
Append a row with: Date Received, Vendor, Invoice #, Invoice Date, Due Date, Subtotal, Tax, Total, Currency, Line Item Summary (concatenated), PDF Link (Gmail attachment URL), Status = "Draft Created".

**Step 8 — QuickBooks Online "Create Bill (Draft)"**
Map: Vendor = vendor_name (use "Find Vendor" lookup first; create if missing via a sub-step), Bill Date = invoice_date, Due Date = due_date, Ref No = invoice_number, Line Items = mapped from array, Total = total_amount. Leave in unpaid/draft status for AP review.

**Step 9 — Path/Fallback: Gmail "Send Email"**
If Step 6 fails, email the AP lead with the original PDF attached and a note listing which fields were missing.

**Testing & Edge Cases**
- Test multi-page invoices and scanned (image-based) PDFs — add an OCR step (e.g., Mindee or Docparser) upstream if OCR confidence is low.
- Test duplicate invoice numbers from the same vendor; add a Sheets "Lookup Row" check before Step 8 to prevent double-billing.
- Test foreign-currency and multi-line invoices to confirm line_items array maps correctly into QuickBooks line detail.

# Automated Invoice Processing Workflow

**Step 1: Trigger - Gmail New Attachment**
Set Gmail trigger to monitor the shared AP inbox for emails with PDF attachments containing "invoice" in the subject line or body. Configure to pull sender email, subject, and attachment data.

**Step 2: Filter - Validate PDF Attachment**
Add a Filter step to ensure the attachment is a PDF file type and file size is under 25MB to avoid processing errors.

**Step 3: Extract PDF Content - Zapier AI by Zapier**
Use "Zapier AI by Zapier" action with prompt engineering to extract structured data from the PDF. Request extraction of: invoice number, vendor name, invoice date, due date, line item descriptions, line item quantities, line item unit prices, subtotal, tax amount, and total amount due.

**Step 4: Format Data - Formatter by Zapier**
Apply the Formatter utility to standardize extracted dates (MM/DD/YYYY format), convert currency strings to numeric values, and trim whitespace from all text fields. Create a clean JSON object for downstream apps.

**Step 5: Log to Google Sheets**
Use "Google Sheets - Add Single Row" to create a new row in your AP tracking sheet. Map columns as: Invoice Number, Vendor Name, Invoice Date, Due Date, Subtotal, Tax, Total Amount, Status (set to "Pending"), Email From, and Timestamp.

**Step 6: Lookup Vendor in QuickBooks**
Add "QuickBooks Online - Search Customers" action to find the vendor by matching the extracted vendor name. Store the vendor ID for the next step.

**Step 7: Create Draft Bill in QuickBooks**
Use "QuickBooks Online - Create Bill" action. Map extracted fields: vendor ID from Step 6, due date, line items (with descriptions, quantities, amounts), and total. Set the bill status to "Draft" for AP team review.

**Step 8: Send Confirmation Email**
Add "Gmail - Send Email" to notify the AP team with invoice summary (vendor, total, due date) and direct links to both the Google Sheet row and QuickBooks draft bill.

**Testing & Edge Cases:**
• Test with invoices containing multiple-page PDFs, missing fields, and varying vendor name formats (abbreviations, legal vs. trading names) to ensure AI extraction handles partial data and fuzzy vendor matching.
• Validate currency handling for international invoices and edge cases like invoices with discount lines or freight charges to confirm line-item accuracy in QuickBooks.
• Monitor for duplicate invoices by adding a deduplication check using invoice number + vendor name in Google Sheets before creating QuickBooks bills.

Here is the step-by-step automation design for your accounts payable workflow.

**Zapier Workflow: Automated Invoice Processing**

1.  **Trigger: New Email in Gmail**
    *   **App:** Gmail
    *   **Event:** New Email Matching Search
    *   **Configuration:** Set up a search string to only trigger for emails in your shared inbox with PDF attachments. For example: `in:inbox has:attachment filename:pdf`. Consider creating a specific label like "Invoices" and adding `label:invoices` to the search for better control.

2.  **AI Extraction: Parse Document**
    *   **App:** Docparser (or a similar AI-powered parsing tool like Nanonets or Parseur)
    *   **Event:** Submit File for Parsing
    *   **Configuration:** In Docparser, you will first create a parsing model by uploading a few sample invoices and highlighting the data you want to extract.
    *   **Fields to Extract:** `Vendor Name`, `Invoice Number`, `Due Date`, `Total Amount`, and a table for `Line Items` (which should include columns for description, quantity, and price). The file to parse will be the attachment from the Gmail trigger step.

3.  **Format: Standardize the Date**
    *   **App:** Formatter by Zapier
    *   **Event:** Date / Time
    *   **Configuration:** Take the `Due Date` output from Docparser. Format it into `YYYY-MM-DD`, which is a standard format accepted by most accounting software and databases, preventing errors.

4.  **Log Data: Create Row in Google Sheets**
    *   **App:** Google Sheets
    *   **Event:** Create Spreadsheet Row
    *   **Configuration:** Select your spreadsheet and worksheet. Map the data from Docparser (Step 2) and the formatted date (Step 3) into the corresponding columns: Vendor Name, Invoice Number, Formatted Due Date, Total Amount. For line items, you can log the raw text or link to the original PDF.

5.  **Create Draft: Create Bill in QuickBooks**
    *   

1. **Trigger**: Gmail by Zapier → New Attachment (filter by label: "invoices" in shared inbox; captures PDF attachment data).

2. **Action**: PDF.co → Extract Text from PDF (input: Gmail attachment data; outputs raw text).

3. **Action**: AI by Zapier → Extract Structured Data from Text (input: PDF text; schema: {"invoice_number": "string", "vendor_name": "string", "line_items": "array of objects {description: string, quantity: number, unit_price: number, amount: number}", "total_amount": "number", "due_date": "string (YYYY-MM-DD)"}).

4. **Action**: Google Sheets → Create Spreadsheet Row (map: Timestamp, Invoice Number, Vendor Name, Line Items (JSON string), Total Amount, Due Date; sheet: "Invoice Log").

5. **Action**: QuickBooks Online → Find or Create Customer (search/create vendor by "vendor_name"; outputs Customer ID).

6. **Action**: QuickBooks Online → Create Bill (map: Customer ID, Invoice Number, line_items array {description, quantity, unit_price, amount}, Total Amount, Due Date; set as Draft).

7. **Action**: Gmail by Zapier → Mark as Read (input: email ID from trigger; prevents re-processing).

**Testing & Edge Cases**:
- Test with sample invoices varying formats (e.g., multi-page, scanned); verify AI accuracy >90%, fallback to manual review column in Sheets.
- Handle no PDF or extraction failure: Add Filter by Zapier after step 3 (only continue if "invoice_number" exists); route failures to Slack notification.
- Multi-line items: Confirm AI outputs valid array; use Looper by Zapier if flattening needed for Sheets (word count: 248).