// Sheets · Apps Script

Generate a sequential ID for new rows in Google Sheets.

Auto-increment a unique row ID in Google Sheets without duplicates, even when two form submissions land at the same time. Uses LockService to prevent race conditions.

“I want every new row in my sheet to get a unique sequential ID, but concurrent form submissions keep assigning the same number to two rows at once.”

The script

copy · paste · trigger

assignRowId.gs

Apps Script

// Assigns a sequential ID to the first empty cell in column A.
// Wire to onFormSubmit or run manually. Safe under concurrent submissions.
function assignRowId(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  var lock = LockService.getSpreadsheetLock();
  lock.waitLock(10000);
  try {
    var data = sheet.getRange('A2:A').getValues();
    var maxId = 0;
    for (var i = 0; i < data.length; i++) {
      var val = Number(data[i][0]);
      if (val > maxId) maxId = val;
    }
    var targetRow = e ? e.range.getRow() : sheet.getLastRow();
    sheet.getRange(targetRow, 1).setValue(maxId + 1);
  } finally {
    lock.releaseLock();
  }
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why MAX+1 without a lock breaks

The naive approach reads the highest existing ID, adds one, and writes it back. Under a single user that works fine. Under a Google Form with any real traffic, two submissions can fire within milliseconds of each other. Both executions read the same MAX value before either has written its result, and both write the same ID to different rows. You now have a duplicate, and no error was ever thrown.

LockService.getSpreadsheetLock() serializes access at the spreadsheet level. The first execution claims the lock; the second blocks at waitLock(10000), which means it will wait up to 10 seconds before throwing. In practice the first execution reads, increments, writes, and releases in well under a second, so the second execution then proceeds with an accurate MAX.

The first time I hit this problem, the sheet was a registration form for a small event. Two people submitted within the same second during an announcement email blast. Both got ticket number 47. That kind of collision is silent and only surfaces when you go to fulfill something downstream.

Wiring the trigger correctly

In the Apps Script editor, go to Triggers (the clock icon) and add an installable trigger: event source = Spreadsheet, event type = On form submit, function = assignRowId. The simple onFormSubmit(e) trigger also works but runs under the form-submitter's identity with tighter quotas; the installable trigger runs as you (the script owner) and is the safer default for anything production-facing.

The event object e carries e.range, which is the range the form submission wrote to. e.range.getRow() gives you the exact row of the new submission without any guessing about lastRow. If you run the function manually without a trigger (e is undefined), the fallback sheet.getLastRow() targets the bottom-most row with data, which is useful for backfilling existing rows.

Column A must be excluded from the form itself. If the form writes to column A, the trigger overwrites that value with the ID. Keep ID in a column the form never touches, or prepend a dedicated ID column before the form-written range.

Gaps in the sequence and when they matter

A deleted row leaves a gap. The MAX scan ignores deleted rows entirely, so the next insert gets MAX+1 of whatever survives, skipping the deleted number permanently. For audit or accounting contexts that require a gapless sequence, this is a problem. For most operational uses (order IDs, ticket numbers where the sequence just needs to be monotonically increasing and unique) gaps are fine.

If gapless sequences are a hard requirement, you need a separate counter cell (say, cell Z1) that you increment and read inside the same lock block instead of scanning the column. That counter never resets on delete. The tradeoff is that the counter cell becomes a single point of truth you have to protect from accidental edits.

Do not use COUNTA or ROW()-1 as an ID formula directly in the sheet. Both recalculate when rows are inserted or deleted above, silently renumbering historical records. A script-written static value never changes after it is written.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

What happens if two submissions arrive and waitLock times out?: waitLock(10000) throws a LockTimeoutException after 10 seconds of waiting. The try/finally still releases the lock if the first execution holds it, but the second execution's script run fails with an error. The form response is still recorded in the sheet; it just won't have an ID. You can catch that exception explicitly and log it to a separate Errors sheet, or set up an Apps Script execution failure email alert under Project Settings.
Can I use this with a regular sheet instead of a Google Form?: Yes. Remove the e parameter check and call assignRowId() from an onChange or onEdit trigger instead, or call it explicitly from another function when you append a row programmatically. The lock logic is identical. Just pass null for e and let the fallback getLastRow() path run, or pass a row number directly.
Why getSpreadsheetLock and not getScriptLock?: getScriptLock() serializes across all spreadsheets sharing this script (useful for standalone scripts deployed to multiple files). getSpreadsheetLock() serializes only within this one spreadsheet, which is the right scope here and is slightly less contention if you have other scripts running elsewhere. getDocumentLock() is the same as getSpreadsheetLock() for Sheets; the naming is a legacy artifact.
The ID column shows 0 or NaN for some rows. What went wrong?: The Number() conversion returns NaN for non-numeric values, and NaN > 0 is false, so those cells are skipped. If column A has a header like 'ID' in row 1, that is fine because the range starts at A2. If you have blank rows mid-sheet, getValues() returns empty strings for those cells; Number('') is 0, which also skips correctly. The most common cause of a 0 ID is that e.range.getRow() resolved to row 1 (the header row), meaning the trigger fired before the form appended to the correct row. Check that the form is set to append responses, not overwrite.