Bank Payment Reconciliation n8n: GPT-4o-mini + Google Sheets

Automatically reconcile bank payments to invoices in your Google Sheets—daily, with AI assistance

This n8n workflow automatically matches incoming bank transactions from your Google Sheets to open invoices, marking paid/partial/overpaid records, logging every decision, alerting exceptions in Telegram, and emailing a weekly aging report via Gmail.

What this workflow does

• Daily at 7:00, reads bank transactions and invoices from separate tabs in a Google Sheets spreadsheet (tabs: Bank, Invoices, Log).
• Filters to new incoming payments, normalizes key fields, and extracts an invoice reference from each payment description.
• Reference-first matching: matches payments to open invoices by invoice number and classifies each match as:
  • Paid
  • Partial
  • Overpaid (based on the amount difference)
• AI matching fallback: for payments without a reference match, sends the transaction details plus the list of open invoices to OpenAI (GPT-4o-mini) and only accepts results that meet a confidence threshold.
• Updates the relevant rows in Google Sheets (invoice and bank statuses), appends a detailed reconciliation entry (including reasoning and confidence) to the Log tab, and sends Telegram alerts for partial/overpaid outcomes.
• If the AI match is low-confidence or fails, marks the bank entry as “Review”, logs the event, and notifies Telegram for manual follow-up—without updating invoices.
• Weekly (Monday at 8:00), generates an overdue invoice aging report from Google Sheets and emails it as an HTML table via Gmail.

Use cases

• SaaS operators reconciling daily bank inflows against outstanding invoices.
• Accounting teams reducing manual matching work and centralizing exceptions in Telegram.
• n8n users standardizing invoice payment reconciliation using Google Sheets as the source of truth.

Technical details

• n8n workflow scheduled: daily 7:00 and Monday 8:00.
• Uses nodes/tools including if, set, code, no op, gmail, and merge.
• Integrates with Google Sheets and OpenAI (GPT-4o-mini); sends alerts through Telegram.