Invoice numbers must be unique under concurrency. A locked sequence cell is a simple allocator.
generateInvoiceNumber waits for a document lock, increments Meta!B1, formats INV-YYYY-00001 style ids, and appends a DRAFT row.
padStart keeps sorting friendly fixed width. Year prefix resets meaning annually while the counter can keep climbing or be reset manually.
Call from a form submit handler or web app when creating invoices.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Cell/Column | Purpose |
|---|---|---|
| Meta | B1 | Integer sequence |
| Invoices | A invoiceNo | Allocated number |
| Invoices | B createdAt | Timestamp |
| Invoices | C customerId | Caller input |
| Invoices | D status | DRAFT |
What this script does
Atomic-ish sequence allocation with lock + append.
Prerequisites
Meta and Invoices sheets; B1 starting at 0 or last number.
- Document lock
- Single Meta cell owner
- Timezone set for year prefix
Walkthrough
Call twice quickly; confirm consecutive numbers and two DRAFT rows.
Edge cases
If the script dies after increment but before append, you may skip a number — usually acceptable for invoices.
- waitLock 15s
- padStart 5 digits
How to test
Reset B1 to 0 on a copy; generate three numbers.
Hardening for production
Store prefix rules in Properties; separate counters per legal entity.
Variations
Use Utilities.getUuid for opaque ids when sequences are not legally required.
Full code: generateInvoiceNumber()
Initialize Meta!B1, then call generateInvoiceNumber(customerId) from your create-invoice flow.
/**
* Generate sequential invoice numbers with LockService.
*/
const META = "Meta";
const INVOICES = "Invoices";
function generateInvoiceNumber(customerId) {
const lock = LockService.getDocumentLock();
lock.waitLock(15000);
try {
const ss = SpreadsheetApp.getActive();
const meta = ss.getSheetByName(META);
const year = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy");
const prefix = "INV-" + year + "-";
let seq = Number(meta.getRange("B1").getValue()) || 0;
seq += 1;
meta.getRange("B1").setValue(seq);
const invoiceNo = prefix + String(seq).padStart(5, "0");
ss.getSheetByName(INVOICES).appendRow([
invoiceNo,
new Date(),
customerId || "",
"DRAFT",
]);
return invoiceNo;
} finally {
lock.releaseLock();
}
}- Line 9: Serializes concurrent allocations.
- Line 4: Holds the monotonic counter in B1.
- Line 18: Formats a fixed-width numeric suffix.
- Line 20: Creates the DRAFT invoice record.
- Line 28: Always released in finally.
- Line 26: Caller uses the allocated number.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: invoice numbers
- 1Meta!B1 initialized
- 2Invoices headers ready
- 3Legal numbering rules understood (gaps OK?)
- 4Timezone correct for year prefix
- 5Only one allocator function increments B1
- 6Test under two simultaneous executions
Frequently asked questions
Yes if append fails after increment. Most finance teams accept gaps; if not, write DRAFT first with a temp id then assign.
On Jan 1 set B1 to 0, or include year in Meta keys (B2 for 2026, etc.).
Keep Meta in one hub file; other files call a web app allocator.
Scanning the column races and breaks if rows are archived.
V8 runtime supports it; otherwise use your own pad helper.
Yes — concatenate after allocation, but keep the numeric sequence intact for audit.
waitLock throws if not acquired — catch and ask the user to retry.