Automatically Extract Invoice Data from Email and Save to Google Sheets

Step 1: Monitor Gmail for Invoice Emails

Node: Gmail Trigger

Settings:

• Simple: False (use advanced filters)
• Filters:
  • Query: has:attachment subject:New Invoice
• Options:
  • Download Attachments: True

Customize Your Email Filter: Adjust the Gmail query to match your invoice emails:

• has:attachment subject:Invoice - Emails with "Invoice" in subject
• has:attachment from:accounting@company.com - Specific sender

Success Looks Like: The trigger activates when matching emails arrive, automatically downloading PDF attachments for processing.

Step 2: Upload Invoice to PDF.co

Node: PDF.co → Upload File

Settings:

• Binary File: True
• Input Binary Field: attachment_0
• File Name: SampleInvoice.pdf (or use dynamic naming)

Success Looks Like: The PDF attachment is uploaded to PDF.co's cloud storage, returning a file URL for processing.

Step 3: Parse Invoice Data

Node: HTTP Request → PDF.co Document Parser

Settings:

• Method: POST
• URL: https://api.pdf.co/v1/pdf/documentparser
• Headers:
  • x-api-key: Your PDF.co API key
  • Content-Type: application/json
• Body Parameters:
  • url: ={{ $json.url }} (from upload step)
  • templateId: 1 (your document parser template ID)
  • inline: true

Document Parser Template Setup:

1. Create Template: Use PDF.co's Template Editor to define extraction fields
2. Common Fields:
   • Company Name (vendor)
   • Customer Name
   • Invoice ID
   • Date Issued
   • Total Amount
   • Line item tables
3. Test Template: Use sample invoices to refine extraction accuracy (Sample here)

Success Looks Like: The parser returns structured JSON with extracted invoice fields and table data.

Step 4: Process and Structure Data

Node: Code (JavaScript)

Purpose: Transform the parsed invoice data into a format suitable for Google Sheets, handling line items and field mapping.

Key Functions:

• Extract Invoice Fields: Company names, dates, invoice IDs, totals
• Process Line Items: Handle table data with quantities, descriptions, prices
• Data Transformation: Convert types and structure for spreadsheet format

Get the Code Sample Here

Success Looks Like: Each line item becomes a separate row with all invoice header information included.

Step 5: Log to Google Sheets

Node: Google Sheets → Append Row

Settings:

• Document From List: Your Google Sheets document
• Sheet From List: Your target sheet (e.g., "Sheet1")
• Column Mappings:
  • Vendor: ={{ $json.companyName }}
  • Customer: ={{ $json.companyName2 }}
  • Invoice ID: ={{ $json.invoiceId }}
  • Invoice Date: ={{ $json.dateIssued }}
  • Quantity: ={{ $json.column1 }}
  • Description: ={{ $json.column2 }}
  • Unit Price: ={{ $json.column3 }}
  • Total: ={{ $json.column4 }}
  • Grand Total: ={{ $json.total }}

Spreadsheet Setup: Get a copy of the Sample Google Sheets here

Success Looks Like: Each processed invoice creates new rows in your spreadsheet with all extracted data properly formatted.