Flowgrammer

Google Sheets Invoice Tracker

Build an AP invoice register in Google Sheets with statuses, duplicate keys, overdue formulas, and a human approval stop before posting or payment.

— Craig Major

A Google Sheets invoice tracker is an accounts-payable register you import and run in Sheets. Each row records who billed you, the invoice number, status, owner, exceptions, and whether a person approved the bill. Sheets stores the list, constrains the closed lists, and calculates duplicate keys and overdue flags. It does not extract fields from PDFs, create an accounting draft, certify AP approval, or send a payment.

This page implements the published Invoice Tracker in Google Sheets. The paired Google Sheets Invoice Tracker Workbook is the XLSX you import. The shared field list and sample rows live in the Invoice Tracker Starter Pack. Extraction and destination drafts stay on Invoice Processing Automation. What to automate first stays on Document Processing Automation.

Who this is for

Use this when supplier invoices already arrive and the immediate job is one visible register in Google Sheets: what came in, what is waiting, what is overdue, and what must not be entered twice.

Prerequisites:

  • One buying entity for the first register. The worked example is fictional Cedar & Quay Fabrication Ltd.
  • Agreement that this is an AP status list for bills you received, not a sales-invoice layout you send to customers
  • A named reviewer who can set approval_decision
  • A place to keep the source file, usually a Drive folder plus a source_ref
  • Agreement that posted_ref and paid_ref stay empty until a person confirms those actions in another system

Stay out of this first sheet: supplier bank-detail changes, tax filings, autonomous payment, and multi-entity matching.

If you still need the platform-neutral model, stay on the parent tracker. If you need extraction and an accounting draft, use the invoice-processing guide and the live Invoice Processing Test Pack. That pack uses labelled-text fixtures and does not measure OCR.

This is not a Google Sheets invoice template

Many Google Sheets invoice templates are sendable customer invoices with a logo, bill-to address, line items, and a PDF layout. That is accounts receivable. This workbook is an accounts-payable register for bills you received.

What Sheets implements

The Invoice Tracker Starter Pack defines the shared field list. This workbook uses the same names so you can move data between the two without remapping it.

Starter piece Sheets twin
Field dictionary Invoices columns A-AC, same names
Status model Dropdown on status
Duplicate key Formula on duplicate_key
Exceptions and roles Exceptions and Roles tabs
Sample rows INV-TR-0001-0010 Same fictional Cedar & Quay rows
Dashboard metrics Dashboard formulas at as-of Instructions!B4
Audit events AuditLog tab

Do not rename fields for Sheets convenience. The same names make later imports and migrations easier.

Sheets-native additions that are not a second schema:

  • Data validation dropdowns for currency, status, exception_code, and approval_decision
  • Computed duplicate_key, amount_delta, days_overdue, aging_bucket, is_overdue, posted_without_approval
  • An autoFilter on the Invoices table
  • Filter views you create after import
  • Optional HYPERLINK beside source_ref for a Drive URL
  • A Vendors tab (present in starter CSVs, missing as a sheet in the starter XLSX)

The starter XLSX gated duplicate_key on exception_code. That contradicts the field dictionary. This twin computes the key whenever invoice_no is present:

=IF(D2="","",LOWER(TRIM(B2))&"|"&LOWER(TRIM(D2)))

A blank invoice number leaves the key empty and pairs with missing_invoice_no. Two blank numbers are not the same bill.

Import and sharing

Google documents File > Import for Excel data. In Sheets: File > Import, choose the XLSX, then Create new spreadsheet or Replace spreadsheet. The Excel file is not changed. Official steps: Use both Excel & Sheets.

You can also import each CSV onto a matching tab. Recreate dropdowns from the Lookups tab if validation did not survive the import. Google documents in-cell dropdowns under Data > Data validation (Create an in-cell dropdown list).

Share the reviewer as Editor (Share files from Google Drive). Version history lets you see editors and restore a version (Collaborate in Sheets). That is collaboration history. It is not a certified financial audit log. Keep named decisions on the AuditLog tab.

This package is an XLSX twin. No live native Google Sheet was created or verified in this pass. Do not treat the download as a Template Gallery listing.

Worked example

Cedar & Quay Fabrication Ltd is a fictional Toronto buyer. Import the workbook. Leave Instructions!B4 at 8 September 2026.

  1. INV-TR-0001, Ridgemont Industrial Supply Co, INV-TP-2401, CAD 452.00. Status pending_approval. duplicate_key is ridgemont industrial supply co|inv-tp-2401. No posted_ref. No paid_ref.
  2. INV-TR-0002 uses the same vendor and invoice number on a different file. Status duplicate. The first row is unchanged. The key still computes even though exception_code is duplicate.
  3. INV-TR-0003, Ashbridge Print Works, blank invoice number. Status exception / missing_invoice_no. duplicate_key is empty.
  4. INV-TR-0004, Prairie Bolt & Fastener Ltd, printed total 185.00 versus 220.50. amount_delta is 35.50.
  5. INV-TR-0006, Kings Mill Packaging Co, due 19 August 2026, status approved. On the as-of date it is overdue. follow_up_owner is Priya Nair (fictional).
  6. INV-TR-0007 has posted_ref QBO-BILL-FICTION-441 only because a person recorded an external draft after approval_decision was approve.

The companies, people, GST/HST numbers, and destination ids are fictional. They are not customer results.

Formulas, dropdowns, and views

After import, the Invoices tab should calculate without a script.

Column Job
AD duplicate_key Identity key when invoice_no exists
AE amount_delta Absolute difference of subtotal plus tax versus printed total
AF days_overdue Days past due using Instructions!B4, zero if paid, rejected, or duplicate
AG aging_bucket current, 1_7, 8_30, 31_plus
AH is_overdue yes or no
AI posted_without_approval yes if posted_ref is set and approval_decision is empty

Rows 12-50 keep the same validations and empty-safe formulas so you can add invoices without breaking dropdowns. Dashboard formulas count only rows with a record_id.

Google documents filters and named filter views (Sort & filter your data). A filter changes the sheet for every editor. A filter view is a saved view you can name and share. Create views for pending approval, exceptions, overdue, approved unposted, and duplicates.

QUERY can build the same queues from one table (QUERY function). Conditional formatting can highlight exceptions or overdue cells (Use conditional formatting rules). Those are visual aids. They are not approval.

Optional: store a Drive URL with HYPERLINK (HYPERLINK). Keep source_ref as the durable file name. Images in cells are not a general PDF store.

What can run automatically, and what cannot

Situation Sheets can do Person required
New row with dropdowns Yes Confirm the source if intake is messy
Compute duplicate_key and amount_delta Yes Yes, before approval
Flag overdue from the as-of date Yes Assign follow_up_owner
Filter pending approval Yes The approve decision itself
Extract fields from a PDF No Use invoice processing, not this sheet
Create an accounting draft No Record posted_ref after a person confirms the write
Send payment No Record paid_ref after a person confirms payment
Certify AP approval No approval_decision is an operating field, not a legal certificate

Human gates

A person keeps these decisions:

  • Who owns the row and who may review it
  • Whether a duplicate or exception may continue
  • Approve, reject, or hold
  • Whether posted_ref may be filled after an accounting write
  • Whether paid_ref may be filled after a payment
  • Who may edit the spreadsheet

The sheet must not create a vendor bill, release a payment, change supplier bank details, or treat a formula result as approval.

Failure paths

Failure Expected result Next action
Blank invoice_no missing_invoice_no; empty duplicate_key Collect the number. Do not key two blanks together
Same vendor and invoice number Status duplicate Leave the first row alone
Subtotal plus tax does not match total conflicting_total; amount_delta > 0.011 Check the source
posted_ref with empty approval_decision posted_without_approval = yes Return to pending_approval
Overdue approved row is_overdue yes Assign follow_up_owner
Missing owner missing_owner Assign someone
Unknown vendor unknown_vendor Confirm the supplier
Treating protection as security Invalid assumption Share only with people you trust
Apps Script posts or pays Out of scope Remove the script

Protected ranges are not a security control

After import you may protect formula columns AD:AI so reviewers do not type over formulas. Google’s protect-sheets help is explicit: this should not be used as a security measure. People can print, copy, paste, import, and export a protected spreadsheet. Only share spreadsheets with people you trust. Sheets cannot protect data with a password. Viewers can still reach content in hidden sheets, including by making a copy.

Cite: Protect, hide & edit sheets, accessed 8 September 2026.

Test cases

Walk these rows on the imported sheet with Instructions!B4 = 2026-09-08.

Case Row Expected
Clean received INV-TR-0001 pending_approval; key filled; no posted or paid
Duplicate INV-TR-0002 duplicate; same key as 0001; first row unchanged
Missing invoice number INV-TR-0003 exception / missing_invoice_no; key empty
Conflicting total INV-TR-0004 amount_delta 35.50
Overdue approved INV-TR-0006 is_overdue yes; follow_up_owner set
Posted after human confirm INV-TR-0007 posted_ref present; approval present
Missing owner INV-TR-0010 exception / missing_owner

Dashboard sample results at that as-of date: open 8, pending approval 1, exception 4, duplicate 1, overdue 2, overdue amount 463.30, approved unposted 2, posted unpaid 1, invalid posted without approval 0, amount mismatch 1. Those are planning counts, not savings.

What to measure

Count open rows, exceptions, duplicate stops, pending approval, overdue rows, and how often posted_ref is filled in the same week as approval.

Do not convert those counts into a savings claim. The Automation ROI Calculator is a planning worksheet. Replace its illustration with your observed numbers.

Replace the fictional sample counts with results from your own register before using them to plan work.

How to use the workbook

Download the Google Sheets Invoice Tracker Workbook. The paired workbook is an XLSX twin of the starter pack plus File > Import notes.

  1. Open the paired XLSX.
  2. In Google Sheets, use File > Import. Choose Create new spreadsheet or Replace spreadsheet.
  3. Read the Instructions tab. Note the AP disclaimer and the protection warning.
  4. Walk the seven cases above.
  5. Add your own approved or fictional rows in the preformatted blank lines.
  6. Keep Apps Script off financial writes. Google documents quotas and installable-trigger limits if you add mail later. That is optional and untested here.

Canadian buyers who later claim input tax credits still need prescribed information on the source invoice. CRA GST/HST Memorandum 8-4 is about supporting documents, not a licence for the sheet to file tax.

Next step

If one team, one inbox, and one Sheets file are enough, stay on this register until the handoffs hurt.

If invoices arrive through several channels, need named approvers with a trail a shared sheet cannot protect, or must create an accounting draft without retyping, scope a Document Intake and Approval System through AI Automation Systems.

If several document workflows are competing, use the AI Success Audit.

Sources

Frequently asked questions

What is a Google Sheets invoice tracker?

An AP register in Sheets with required fields, a status dropdown, an owner, a computed duplicate key, exception flags, and a record of who approved the row. It does not post or pay the invoice.

Is this the same as a Google Sheets invoice template?

No. A tracker records bills you received. A sales-invoice template is a document you send to a customer.

Does Google Sheets extract invoice fields from PDFs?

No. Keep a Drive link or file name in `source_ref`. Extraction stays on the invoice-processing workflow.

Does the sheet create a QuickBooks or Xero draft?

No. A person records `posted_ref` after confirming the write in accounting.

Are protected ranges enough to lock the register?

No. Google says protection is not a security measure. People can copy and export a protected spreadsheet.

Did Flowgrammer publish a live Google Sheet?

No. The workbook ships as an XLSX for File > Import. A live native sheet would be named only after an account-side import is verified.

Should we use Apps Script to post or pay?

No. Apps Script is optional later and stays out of v1 financial writes. Quotas can change. Do not attach a script that posts or pays.

When is a Sheets tracker no longer enough?

When intake has more than one channel, when approval needs a protected audit trail, or when an approved row must create an accounting draft without retyping.