Auto-number invoices and POs without gaps

Northwind raises invoices and purchase orders from a shared spreadsheet, and tax authorities care about one thing in particular: the numbering must be sequential with no gaps. A missing INV-1043 invites questions about a deleted or hidden invoice. The risk is not malice — it is two people adding rows at the same time, or a formula that grabs the wrong “last” number and skips one.

This script assigns the next number to every row that does not have one yet, drawing from a counter kept in Script Properties so the sequence survives across runs. It wraps the work in a LockService lock, so two concurrent runs can never hand out the same number or leave a gap between them.

What you’ll need

• A Google Sheet whose first tab holds invoice or PO rows, with a header row.
• A column headed exactly invoiceNumber where the numbers will be written.
• The spreadsheet ID, taken from its URL.
• Nothing else — the counter is created automatically the first time the script runs.

The script

// The spreadsheet that holds your invoice / PO rows.
const INVOICES_SHEET_ID = '1abcInvoicesSheetId';

// Header of the column the numbers are written into.
const NUMBER_COLUMN = 'invoiceNumber';

// Prefix and starting value for the sequence.
const NUMBER_PREFIX = 'INV-';
const FIRST_NUMBER = 1001;

// Script Property that stores the next number to hand out.
const COUNTER_KEY = 'NEXT_INVOICE';

// How long to wait for the lock before giving up, in milliseconds.
const LOCK_TIMEOUT_MS = 30_000;

/**
 * Assigns the next sequential, gap-free document number to every row
 * that does not already have one. Guarded by a script lock so two runs
 * can never share or skip a number.
 */
function assignInvoiceNumbers() {
  // 1. Take the script lock — concurrent runs queue here.
  const lock = LockService.getScriptLock();
  lock.waitLock(LOCK_TIMEOUT_MS);

  try {
    const sheet = SpreadsheetApp.openById(INVOICES_SHEET_ID).getSheets()[0];
    const values = sheet.getDataRange().getValues();

    // 2. Split off the header and map column names to indexes.
    const [header, ...rows] = values;
    const col = Object.fromEntries(header.map((h, i) => [h, i]));

    if (rows.length === 0) {
      Logger.log('No data rows — nothing to number.');
      return;
    }

    // 3. Read the next number to hand out from Script Properties.
    const props = PropertiesService.getScriptProperties();
    let next = parseInt(props.getProperty(COUNTER_KEY) || FIRST_NUMBER, 10);

    // 4. Fill in any row whose number cell is still blank.
    let assigned = 0;
    rows.forEach((row, i) => {
      if (row[col[NUMBER_COLUMN]]) return;  // already numbered — skip
      // values[i + 1] because row 0 of `values` is the header.
      values[i + 1][col[NUMBER_COLUMN]] = NUMBER_PREFIX + next;
      next++;
      assigned++;
    });

    // 5. Write the whole grid back, then save the updated counter.
    sheet.getDataRange().setValues(values);
    props.setProperty(COUNTER_KEY, String(next));
    Logger.log('Assigned ' + assigned + ' new numbers.');
  } finally {
    // 6. Always release the lock, even if something above threw.
    lock.releaseLock();
  }
}

How it works

1. assignInvoiceNumbers acquires the script lock and waits up to 30 seconds. If another copy of the script is already running, this call queues behind it instead of running in parallel.
2. It reads the whole sheet, peels off the header row, and builds a col lookup so the code refers to invoiceNumber by name rather than a fixed index.
3. If there are no data rows it logs and returns early — the finally block still releases the lock.
4. It reads the next number from Script Properties, falling back to FIRST_NUMBER (1001) the very first time.
5. It walks the rows, and for any row whose number cell is empty it writes INV-<next> and advances the counter. Rows already numbered are left untouched, so numbers are stable once assigned.
6. The full grid is written back in one setValues call, the updated counter is saved, and the lock is released in the finally block so it is freed even if an error is thrown.

Example run

The sheet has three new rows with blank invoiceNumber cells, and the counter sits at 1043:

Before (invoiceNumber)	After
INV-1041	INV-1041
INV-1042	INV-1042
(blank)	INV-1043
(blank)	INV-1044
(blank)	INV-1045

The Script Property NEXT_INVOICE now reads 1046, ready for the next run.

Trigger it

Run this automatically so new rows are numbered without anyone remembering to:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose assignInvoiceNumbers, then either a Time-driven minutes timer every 10 minutes, or From spreadsheet → On change for near-instant numbering.

The On change trigger fires on edits, so combine it with the lock — which the script already has — to stay gap-free under concurrent edits.

Watch out for

• The counter lives in Script Properties, not the sheet. If you delete a numbered row, the counter does not roll back — the next number is still handed out, leaving a visible gap. Either avoid deleting numbered rows or void them in place.
• If you ever need to reset or migrate the sequence, change NEXT_INVOICE in Project Settings → Script Properties directly; do not edit numbers in the sheet and expect the counter to follow.
• The lock serialises runs, so two On change triggers a second apart will queue rather than collide — but a run that holds the lock for the full 30-second timeout will make the next one fail to acquire it. Keep the sheet small enough to process quickly.
• The column name must match NUMBER_COLUMN exactly, including case. A header typo means col[NUMBER_COLUMN] is undefined and numbers are written into the wrong place.