DEV Community

Stackscale
Stackscale

Posted on

Reconcile Invoices in Google Sheets with Make and OpenAI

Invoices sit unpaid in your sheet. Learn how to reconcile invoices in google sheets — Make watches for new invoices and payments, OpenAI matches them by amount, date, and reference, and every invoice sorts itself into Matched, Partial, or Missing.

This is the companion tutorial for the StackScale video above. No code, no live invoices, and no real payments — the whole flow runs on eighteen fictional demo invoices so you can test it safely.

What You Will Build

A Make scenario that watches for a new invoice or payment, sends both rows to OpenAI, and files one verdict per invoice into Google Sheets: Matched, Partial, or Missing. Eighteen invoices in, fifteen matched in seconds, three waiting in a review sheet, and $2,340 reconciled without opening a second sheet.

Prerequisites / Tools

  • A Make account (free plan is enough to start)
  • An OpenAI account
  • A free Google Sheet with two tabs — one for invoices, one for payments — plus a Review tab for unmatched payments
  • Dummy invoices to test with — the fictional Demo Studio, Demo Cafe, and Demo Supplies invoices used in the video

Disclosure: Some links below are affiliate links, meaning StackScale may earn a commission at no extra cost to you.

The Workflow

Make watches both sheets for a new row — a new invoice or a new payment — and the scenario starts the moment one appears. OpenAI compares the two by amount, date, and reference, and returns one verdict as JSON. Make maps the verdict into the status column, and anything it cannot match goes to the Review tab instead of being silently marked paid. Logging a payment is not the job here — matching it is.

Step 1: Build the Invoices and Payments Sheets

The invoices sheet holds invoice number, client, amount, due date, and status. The payments sheet holds date, amount, and reference. Add a Review tab, where unmatched payments wait for you.

Step 2: Trigger on a New Invoice or Payment

In Make, set the scenario to watch both sheets for a new row. The scenario starts the moment a new invoice or a new payment appears — no button, no schedule to remember.

Step 3: The OpenAI Matching Prompt

Send both rows to OpenAI with this prompt (copy as-is):

You are matching invoices to payments. For each invoice, compare the payment amount, payment date, and payment reference. Allow a payment date within 7 days of the invoice date and a rounding difference of up to $0.05. Return JSON only: {"invoice": "INV-1041", "verdict": "Matched", "reason": "Same amount and reference."}. Verdicts: Matched (amount and reference agree), Partial (payment is less than the invoice — state the amount still owed), Missing (no payment found). Example: {"invoice": "INV-1041", "verdict": "Matched", "reason": "Amount $1,200.00 and reference INV-1041 agree."}.
Enter fullscreen mode Exit fullscreen mode

Ask for one verdict and one reason per invoice, so the result is always a single clean row.

Step 4: Map the Verdict into Sheets

Map the fields into the invoices sheet:

{
  "Invoice": "invoice number",
  "Client": "client name",
  "Amount": "invoice amount",
  "Due date": "due date",
  "Status": "Matched / Partial / Missing"
}
Enter fullscreen mode Exit fullscreen mode

Map the payments sheet:

{
  "Date": "payment date",
  "Amount": "payment amount",
  "Reference": "payment reference"
}
Enter fullscreen mode Exit fullscreen mode

And route anything unmatched to the Review tab:

{
  "Date": "payment date",
  "Amount": "payment amount",
  "Reference": "payment reference",
  "Reason": "why it could not be matched"
}
Enter fullscreen mode Exit fullscreen mode

Matched rows turn green, Partial rows show what is still owed, and Missing rows stay flagged.

Step 5: Test with the Dummy Invoices

Drop in eighteen fictional invoices, including INV-1041 for Demo Studio at $1,200.00, INV-1042 for Demo Cafe at $640.00, and INV-1043 for Demo Supplies at $500.00. Fifteen match in seconds. Three go to review, including a payment of $320.00 with no reference at all — exactly where a human should look.

The Finished Result

Eighteen invoices, fifteen matched, three in review, and $2,340 reconciled — no second sheet opened, no evening lost to matching by eye.

One Honest Limit

OpenAI matches on what it can see, so a payment with no reference and a round amount can still be ambiguous, and this is not an ERP system — it will not post to your accounts or chase clients. Check the review sheet weekly before trusting the matched column.

Watch the Full Tutorial

The video at the top walks through the same five steps with the synthetic mockups. All mockups in the video are synthetic, created for this tutorial, with dummy data only — no real invoice or payment is shown, and no real account appears anywhere.

New automation tutorials every day on StackScale — subscribe on YouTube at https://www.youtube.com/@StackScaleHQ.

Top comments (0)