跳到正文
DEV Community· Stackscale·· 2 天前AI 評分48

如何用 Make 和 OpenAI 在 Google Sheets 對賬發票

Reconcile Invoices in Google Sheets with Make and OpenAI

AI 導讀

Make 配合 OpenAI 可在 Google Sheets 按金額、日期及參考編號核對發票與付款,並標記為 Matched、Partial 或 Missing。測試使用 18 張虛構發票,15 張即時配對成功、3 張送往 Review,合共核對 $2,340。流程只使用虛構示範資料,不會處理真實發票或付款。

正文

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."}.

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"
}

Map the payments sheet:

{
  "Date": "payment date",
  "Amount": "payment amount",
  "Reference": "payment reference"
}

And route anything unmatched to the Review tab:

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

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.

來源:DEV Community · dev.to