Create an Invoice Number Tracker in Google Sheets

Rohit Gupta
2d
353
0
2

Article

Abstract / Overview

Build a durable invoice-numbering system in Google Sheets and drive row creation from Make.com. The sheet assigns unique IDs with prefixes and monthly resets using formulas. Make appends new invoices, wait for formulas to populate, reads back the assigned number, and optionally notifies clients. No scripts, no add-ons.

Assumption: You will write to one sheet named Invoices and one sheet named Settings. Time runs in a single IANA timezone (assume UTC unless stated).

Conceptual Background

Deterministic order. Use an immutable CreatedAt timestamp and a UUID per row. Sequence numbers derive from creation time, not editable fields like invoice date.
Human-readable IDs. Concatenate a prefix, a period key (e.g., YYYYMM), and a zero-padded sequence: INV-202508-0042.
Group-scoped counters. Reset the counter each month (or year) by grouping on a key (e.g., YYYYMM).
Idempotency. The UUID links Make’s write with the sheet’s formula outputs, so the same request never creates two rows.
Observability. A duplicate detector and a registry view expose problems quickly.
Low contention. One append per invoice avoids race conditions. Formulas compute locally, so no locking or Apps Script is needed.

Step-by-Step Walkthrough

1) Create the `Settings` sheet

Add these named values (row 1 are labels; row 2 are values):

Setting	Value	Notes
Prefix	INV	Shown in every ID
SequenceWidth	4	Pads sequence to this length
Period	Monthly	Monthly or Yearly
Timezone	UTC	Match your Make scenario

Define named ranges for A2:D2: Prefix, SequenceWidth, Period, Timezone.

2) Create the `Invoices` sheet

Header row (begin in A1):

Freeze row 1.

3) Enter array formulas (only once, in row 2)

All formulas below are array formulas. They auto-fill down as rows are appended.

A. Period key (PeriodKey in G2):

=ARRAYFORMULA(
  IF(A2:A="","",
    IF(LOWER(Settings!C2)="yearly",
       TEXT(A2:A,"YYYY"),
       TEXT(A2:A,"YYYYMM")
    )
  )
)

B. Sequence within the period (Seq in H2):
Counts how many rows in the same period were created at or before this row, then zero-pads.

=ARRAYFORMULA(
  IF(A2:A="","",
    TEXT(
      COUNTIFS(G2:G, G2:G, A2:A, "<="&A2:A),
      REPT("0", N(Settings!B2))
    )
  )
)

C. Final invoice ID (InvoiceID in I2):

=ARRAYFORMULA(
  IF(A2:A="","",
    Settings!A2 & "-" & G2:G & "-" & H2:H
  )
)

D. Duplicate detection (Duplicate? in M2):

=ARRAYFORMULA(
  IF(I2:I="","",
    IF(COUNTIF(I2:I, I2:I)>1,"DUPLICATE","")
  )
)

E. Optional sanity checks (not required but helpful):

Amount present and positive:

=ARRAYFORMULA(IF(D2:D="","",IFERROR(D2:D>0, FALSE)))

Status default:

=ARRAYFORMULA(IF(LEN(L2:L),L2:L,"Unpaid"))

Important: Columns A (CreatedAt) and J (UUID) are values written by Make, not user formulas.

4) Build registry and pivot views (optional but recommended)

Create a Registry filter view showing InvoiceID, Customer, Amount, Status, sorted by CreatedAt descending.
Create a monthly pivot: Rows = PeriodKey, Values = Count of InvoiceID, Sum of Amount.

5) Prepare Make.com connections

Add Google Sheets and, if you will send emails, Gmail. If you need Slack handoffs, add Slack. Set scenario timezone equal to Settings!D2.

6) Create the “Append and Return InvoiceID” scenario

Core steps:

Trigger: choose one.
- Webhook when your CRM or form submits an invoice request, or
- Google Sheets → Watch changes on a staging tab, or
- Scheduler that consumes a queue.
Set variables: generate createdAt and uuid.
Append row to Invoices.
Wait 1–3 seconds to let formulas calculate.
Find the row by UUID.
Read InvoiceID and continue (e.g., create the PDF in Docs, email the client, or post to Slack).
(Optional) update Status.

You will find a blueprint in the code section.

7) Test the flow

Manually run the scenario with Customer="Acme", Amount=199.00, Currency="USD".
Confirm a new row appears, InvoiceID is like INV-YYYYMM-0001.
Trigger again. Confirm the second row increments to …-0002 for the same month.
Change Period to Yearly in Settings. Append a new row; confirm the middle segment becomes YYYY, with its own counter.

8) Operate day-to-day

Always write CreatedAt and UUID from Make. Do not rely on volatile NOW() in Sheets.
Do not sort Invoices manually. Use filter views or separate “Reports” tabs. Sorting changes the visual order but does not affect the counter, which keys off timestamps.
If you need a custom prefix per brand, add a Brand column and map Prefix from a lookup table instead of Settings!A2.

Code / JSON Snippets

A) CSV seed for `Invoices` header

CreatedAt,Customer,Email,Amount,Currency,InvoiceDate,PeriodKey,Seq,InvoiceID,UUID,Notes,Status,Duplicate?

B) Named settings (CSV reference)

Setting,Value
Prefix,INV
SequenceWidth,4
Period,Monthly
Timezone,UTC

C) Make: minimal variable setup (Set multiple variables)

createdAt = {{ formatDate(now; "YYYY-MM-DDTHH:mm:ssZ"; Settings!Timezone) }}
uuid = {{ uuid() }}

D) Make: Gmail notice to team (optional)

To: [email protected]
Subject: New invoice {{InvoiceID}} created
Body: Customer {{Customer}}, Amount {{Amount}} {{Currency}}. Link: (Sheets link to the row)

E) Sample workflow JSON code (Make scenario blueprint)

Replace connection IDs, spreadsheet IDs, and sheet names with your own.

{
  "name": "Create Invoice Row → Return InvoiceID",
  "version": 3,
  "schedule": { "type": "immediate" },
  "modules": [
    {
      "id": "1",
      "name": "Trigger (Webhook)",
      "type": "webhooks",
      "func": "customWebhook",
      "params": { "hookId": "YOUR_WEBHOOK_ID" }
    },
    {
      "id": "2",
      "name": "Set vars",
      "type": "tools",
      "func": "setVars",
      "params": {
        "vars": {
          "createdAt": "{{ formatDate(now; \"YYYY-MM-DDTHH:mm:ssZ\"; \"UTC\") }}",
          "uuid": "{{ uuid() }}",
          "customer": "{{1.body.customer}}",
          "email": "{{1.body.email}}",
          "amount": "{{ toNumber(1.body.amount) }}",
          "currency": "{{ upper(1.body.currency) }}",
          "invoiceDate": "{{ ifempty(1.body.invoiceDate; formatDate(now; \"YYYY-MM-DD\")) }}",
          "notes": "{{ ifempty(1.body.notes; \"\") }}"
        }
      }
    },
    {
      "id": "3",
      "name": "Append row to Invoices",
      "type": "google-sheets",
      "func": "appendRow",
      "params": {
        "connectionId": "conn_sheets_1",
        "spreadsheetId": "YOUR_SPREADSHEET_ID",
        "sheetName": "Invoices",
        "values": [
          "{{2.createdAt}}",
          "{{2.customer}}",
          "{{2.email}}",
          "{{2.amount}}",
          "{{2.currency}}",
          "{{2.invoiceDate}}",
          "", "", "",                 /* PeriodKey, Seq, InvoiceID are formula-driven */
          "{{2.uuid}}",
          "{{2.notes}}",
          "Unpaid",
          ""                          /* Duplicate? formula */
        ]
      }
    },
    {
      "id": "4",
      "name": "Sleep for formulas",
      "type": "tools",
      "func": "sleep",
      "params": { "seconds": 2 }
    },
    {
      "id": "5",
      "name": "Find by UUID",
      "type": "google-sheets",
      "func": "searchRows",
      "params": {
        "connectionId": "conn_sheets_1",
        "spreadsheetId": "YOUR_SPREADSHEET_ID",
        "sheetName": "Invoices",
        "query": "UUID = {{2.uuid}}",
        "limit": 1,
        "considerHeaders": true
      }
    },
    {
      "id": "6",
      "name": "Return InvoiceID",
      "type": "webhooks",
      "func": "responseJson",
      "params": {
        "status": 200,
        "body": {
          "invoiceId": "{{5.values[0].InvoiceID}}",
          "rowNumber": "{{5.rowNumber}}",
          "createdAt": "{{2.createdAt}}"
        }
      }
    }
  ],
  "links": [
    { "from_module": "1", "to_module": "2" },
    { "from_module": "2", "to_module": "3" },
    { "from_module": "3", "to_module": "4" },
    { "from_module": "4", "to_module": "5" },
    { "from_module": "5", "to_module": "6" }
  ]
}

F) Optional: Slack confirmation (post JSON)

{
  "text": "Created invoice *{{5.values[0].InvoiceID}}* for {{2.customer}}: {{2.amount}} {{2.currency}}."
}

Use Cases / Scenarios

Solo freelancer. Append one row per paid milestone and email the InvoiceID in your PDF subject.
Agency. Multiple brands with different prefixes (ACME, GLOBEX) from a lookup table. Monthly resets preserve year-month grouping.
SaaS back office. A webhook from Stripe or your CRM fires on “invoice required,” Make writes the row, and downstream automation builds the PDF and sends it.
Nonprofit. Donation receipts numbered sequentially with yearly resets for compliance.

Limitations / Considerations

Concurrent appends. Two invoices written at the same timestamp could flip the order. Make’s createdAt uses second precision. Collisions are rare. If you expect heavy parallelism, add a few milliseconds of entropy in createdAt (e.g., append a small sleep or add + RANDBETWEEN(1;999)/86400000 when displaying only.
Editing historical rows. Changing CreatedAt or moving rows affects the counter. Treat CreatedAt and UUID as immutable.
Sorting vs. views. Never re-order the base grid. Use filter views, pivots, or a read-only dashboard tab.
Cross-month invoices. If you date an invoice in a prior month but create it now, the ID still follows the creation month by design. If you need the ID tied to InvoiceDate month, switch PeriodKey to use column F (InvoiceDate) instead of CreatedAt.
Locale formats. All formulas assume ISO date formatting. Ensure Make writes ISO 8601 strings.
Free tier planning. One append + one search + one response per invoice ≈ 3 operations. Add 1–2 for notifications. See the budget below.

Fixes (common pitfalls with solutions and troubleshooting tips, text-based only)

Duplicate flag lights up. Likely manual edits or copy/paste. Restore the original CreatedAt and let the formulas recalc. If a collision remains, temporarily add +ROW()/1000000 to the COUNTIFS right-hand comparator to break ties, then remove it after the row settles.
#N/A in the search step. Your Make search must set considerHeaders=true and reference the exact column name UUID.
Blank InvoiceID after append. Sleep 1–3 seconds, then re-read. Array formulas run after the row exists.
Wrong period reset. Verify Settings!C2 equals Monthly or Yearly (case-insensitive). The formula tests LOWER(Settings!C2).
Multiple prefixes per brand. Add a Brand column. Create a Brands tab with two columns (Brand, Prefix). Replace Settings!A2 with VLOOKUP(Brand, Brands!A:B, 2, FALSE) in the InvoiceID formula.
Amounts display as text. Coerce to a number in Make with toNumber before appending.

Diagram

Budget calculation

Let:

N = invoices per month.
Ops per invoice: append (1) + sleep (0) + search (1) + webhook response/notify (1–2).
Baseline ≈ 3N operations. With Slack and Gmail, ≈ 4–5N.
Example: N=150 → 450–750 ops/month. Fits typical free tiers for light teams. Reduce load with batched notifications or by omitting Slack.

Future enhancements

Per-customer counters. Replace PeriodKey with Customer+YYYY to track per-client sequences.
Checksum suffix. Add a short hash of UUID to detect transcription errors: INV-202508-0042-7F.
Archive pipeline. After payment, move paid rows to a historical sheet via a Make batch.
PDF generation. Pass InvoiceID to a Docs template flow to create a signed PDF and attach to an email.