Support and intake sheets usually need a human-friendly ID like TCK-1042 alongside a truly unique identifier for internal linking, and onEditAssignUniqueId generates both automatically the moment a new row's Name field is filled in.
Relying on someone to manually type the next sequential number invites duplicates and gaps, especially when two people are entering rows around the same time - a script-maintained counter in PropertiesService avoids both problems entirely.
This example only assigns an ID to rows that don't already have one, using the Id column itself as the guard, and pairs the readable TCK-#### value with a Utilities.getUuid() string for any downstream system that needs a globally unique key.
By the end you'll have a Tickets sheet where new rows get numbered automatically and consistently, with the running counter safely persisted between script runs in PropertiesService.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Field | Purpose |
|---|---|---|
| A | Id | Sequential ticket ID like TCK-1042, written only if empty |
| B | Name | Watched column - typing a name here triggers ID assignment |
| Last column | Uuid | Globally unique trace ID generated alongside the sequential Id |
What it does
Typing into the Name column on a row without an existing Id triggers the function to pull the last assigned number out of script properties, increment it, and write both a TCK-prefixed sequential ID and a UUID for that row in a single pass.
- Only rows with a blank Id column are eligible for a new ID
- The running counter persists in PropertiesService across every future execution
- Each row also gets a globally unique UUID in the sheet's last column
Prerequisites
PropertiesService.getScriptProperties() is shared across every function in the same script project, so if other functions also write to LAST_TICKET_ID, make sure the key name doesn't collide with unrelated logic.
- A sheet named 'Tickets' with an empty Id column for new rows
- A Name column in position B that reliably gets filled in for every new row
- An installed onEdit trigger
- No manual typing directly into the Id column, which would defeat the empty-check guard
Walkthrough
The guard checks idCell.getValue() truthiness, which means any non-empty value - even a stray space - will block a new ID from being assigned, so keep the Id column genuinely empty for rows awaiting assignment.
Number(properties.getProperty('LAST_TICKET_ID') || '1000') both reads the last counter and provides a sensible starting point of 1000 the very first time the script runs, before any property has ever been set.
Edge cases
If two people edit the Name column on two different new rows within the same second, Apps Script still processes each onEdit event sequentially for a given script, so the counter increments correctly without producing duplicate IDs.
- Manually typing a value into the Id column bypasses assignment entirely, since the guard treats any existing value as already-assigned
- Deleting a row doesn't return its ID to the pool, so ID gaps are expected and not a bug
- Copy-pasting an entire row including its Id column will carry over the old ID rather than generating a new one, since the guard only checks for blankness
Testing
Clear the Id and Uuid columns on a test row, type a name into column B, and confirm both an incrementing TCK-#### value and a UUID string appear; then repeat on a second row to confirm the counter incremented rather than repeating.
- Check the script property LAST_TICKET_ID directly from the Apps Script editor's Project Settings to confirm it's persisting
- Edit the Name column on a row that already has an Id and confirm nothing changes
- Test the very first run against a fresh script with no existing property, confirming it starts at 1001
Hardening
A hard reset of the script properties, intentional or accidental, would restart the counter from 1000 and risk colliding with previously issued IDs still present in the sheet.
- Before resetting properties, scan the Id column for the highest existing number and seed LAST_TICKET_ID from that instead of a fixed constant
- Wrap the property read/increment/write sequence with LockService.getScriptLock() to prevent a race condition under concurrent edits
- Validate that the Name column edit isn't just whitespace before spending a new ID on it
Variations
The same pattern generalizes to any prefixed sequential ID scheme - invoices, purchase orders, case numbers - by swapping the TCK- prefix and the property key name for each sheet that needs its own counter.
- Use a different property key per sheet so multiple ID sequences can run independently in the same script project
- Encode the current year into the prefix, like TCK-2026-1042, resetting the numeric part every January
- Store the UUID as a QR-code-ready link back into a case management system instead of just a trace column
Full code: onEditAssignUniqueId()
The function treats the Id column as the single source of truth for whether a row has already been processed, and PropertiesService as the durable counter that survives across every future trigger execution.
function onEditAssignUniqueId(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== 'Tickets') return;
var idColumn = 1;
var nameColumn = 2;
if (e.range.getColumn() !== nameColumn) return;
var row = e.range.getRow();
if (row === 1) return;
var idCell = sheet.getRange(row, idColumn);
if (idCell.getValue()) return;
var properties = PropertiesService.getScriptProperties();
var lastId = Number(properties.getProperty('LAST_TICKET_ID') || '1000');
var nextId = lastId + 1;
idCell.setValue('TCK-' + nextId);
properties.setProperty('LAST_TICKET_ID', String(nextId));
var createdColumn = sheet.getLastColumn() + 1;
sheet.getRange(row, createdColumn).setValue(Utilities.getUuid());
}- Line 3: Limits the trigger to the Tickets sheet so ID assignment doesn't run against unrelated tabs.
- Line 7: Only reacts to edits in the Name column, since that's the signal a new row is ready for an ID.
- Line 13: Skips ID assignment entirely if the row already has any value in its Id cell.
- Line 15: Reads the persisted counter from PropertiesService, which survives across every future script execution.
- Line 16: Defaults to 1000 the very first time the script ever runs, before any counter has been stored.
- Line 19: Writes the new sequential, human-readable ticket ID into column A.
- Line 23: Adds a separate globally unique UUID into the last column for systems that need a non-guessable key.
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 turning on ID generation
- 1Id column confirmed empty for all rows awaiting assignment
- 2Name column position matches the nameColumn constant in the script
- 3LAST_TICKET_ID property seeded correctly if migrating from an existing sheet
- 4Tested across two consecutive new rows to confirm no duplicate IDs
- 5Considered LockService if concurrent edits are expected
- 6Uuid column width wide enough to display the full string
Frequently asked questions
The sequential TCK-#### ID is what humans read and reference in conversation, while the UUID guarantees global uniqueness for any integration or database key where a human-friendly but potentially colliding sequence isn't safe enough.
The next assignment would restart from 1001, which risks colliding with an ID already present in the sheet; before that happens, scan column A for the current maximum number and reset the property to match it.
Yes - move the same counter logic into an onFormSubmit handler, reading and writing to the newly submitted row via e.range instead of watching for edits to the Name column.
It's safe for storage, but the read-increment-write sequence isn't inherently atomic, so under heavy concurrent editing you should wrap it with LockService.getScriptLock() to avoid two edits both reading the same counter value before either writes back.
Starting at 1000 is just a stylistic choice so the first real ticket is TCK-1001 rather than TCK-1, giving IDs a consistent four-digit look from the very first row; change the fallback string to start anywhere you like.
No - clearing Name doesn't touch the Id or Uuid columns at all, since the function only ever writes to them, never reads Name's value to decide whether to clear anything.