Route AP invoice approvals with Google Sheets three-way match and Gmail

Quick overview

This workflow runs on a schedule (or manually) to read invoices and purchase orders from Google Sheets, validate each invoice with a three-way match against the PO register, and then log the decision and send approval/exception emails via Gmail using a safe test mode.

How it works

1. Runs on weekdays at 09:00 (or on manual trigger) and loads configuration values like approval limits, allowed currencies, and test email routing.
2. Reads the "Invoice Queue" and "PO Register" tabs from Google Sheets and combines the rows into a single dataset.
3. Validates each invoice for required fields, duplicate vendor/invoice-number keys, supported currency, and matches it to an approved PO with vendor, currency, and amount tolerance checks.
4. Classifies each invoice as AUTO_APPROVE, NEED_APPROVAL, or EXCEPTION and generates a decision reason plus an idempotency key.
5. Appends the decision to the "AP Decision Log" in Google Sheets before sending any notifications.
6. Sends NEED_APPROVAL and EXCEPTION emails via Gmail to the configured test recipient and appends a full audit entry (including exception counts) to the decision log.

Setup

1. Create a Google Sheets file with three tabs named "Invoice Queue", "PO Register", and "AP Decision Log" and ensure invoices include fields like InvoiceID, InvoiceNumber, VendorName, PONumber, InvoiceAmount, and Currency.
2. Add Google Sheets OAuth2 credentials and replace SET_SPREADSHEET_ID in the configuration with your spreadsheet ID.
3. Add Gmail OAuth2 credentials and replace SET_TEST_EMAIL (and optionally SET_AP_EMAIL) in the configuration with your recipient addresses.
4. Review the configuration values (approvalLimit, amountTolerance, allowedCurrencies) and keep testMode enabled while validating with sample data before using it operationally.