ROW() changes when someone inserts a row. A random id can collide. nextSequenceId(prefix) takes the script lock, bumps a counter in Script Properties, and returns an INV-00042-style id that is never reused, even when two triggers fire at once.
When to use
- Invoice, PO, ticket, or case numbers that must stay unique forever
- Ids written onto a row once and never recalculated
- Any counter shared across users or overlapping triggers
How to use this snippet
/**
* Next never-reuse id for `prefix` (e.g. "INV-" → "INV-00042").
* Serializes on the script lock; stores the counter in Script Properties.
*
* @param {string} prefix
* @return {string}
*/
function nextSequenceId(prefix) {
prefix = prefix == null ? '' : String(prefix);
var lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
var props = PropertiesService.getScriptProperties();
var key = 'seq:' + prefix;
var n = Number(props.getProperty(key) || '0') + 1;
if (!isFinite(n) || n < 1) n = 1;
props.setProperty(key, String(n));
var pad = ('00000' + n).slice(-5);
return prefix + pad;
} finally {
lock.releaseLock();
}
}
Example
function stampNewInvoiceIds() {
var sh = SpreadsheetApp.getActive().getSheetByName('Invoices');
var data = sh.getDataRange().getValues();
var idCol = data[0].indexOf('Invoice Id') + 1;
for (var r = 1; r < data.length; r++) {
if (data[r][idCol - 1]) continue; // already stamped
sh.getRange(r + 1, idCol).setValue(nextSequenceId('INV-'));
}
}
// INV-00001, INV-00002, …
Tips:
- Each distinct
prefixhas its own counter (seq:INV-,seq:PO-). Use different prefixes for different document types. - The lock is released in
finally, so a failed write doesn't leave the project locked. - Script Properties are per project. A copied spreadsheet with a bound script starts its counter over unless you copy the property too.
- Five zero-padded digits cover up to 99999. Past that the string just gets longer (
INV-100000).
Tip: NitroGAS Co-Pilot can wire this into an onFormSubmit or a menu stamp. Paste your header row.
Happy Coding!
