Google Sheets: How to Generate Unique IDs

Yes. Google Sheets can generate unique IDs with a row-based formula for simple lists, or with Google Apps Script to write a permanent UUID into each new record. The right choice depends on whether an ID may change when rows move, and whether it needs to be unique only in one sheet or across a larger system.

What a unique ID does—and what it does not do

A unique ID is a value that identifies one record separately from every other record. For example, a customer table might have a name and email address that can be edited, while a separate ID such as C-00001 stays associated with that customer. An ID is useful for looking up, joining, or discussing records without relying on a name that may be duplicated or changed.

“Unique” needs a defined scope. A sequence such as 00001 may be unique in one table, but the same number could appear in another spreadsheet. If records will be combined, include a meaningful prefix, use a UUID, or assign IDs through the system that owns the full dataset. Also, a unique ID is not automatically a password, secret token, or proof that a person is authorized to see a record.

Google Sheets does not provide a built-in worksheet function named UUID that produces a permanent ID whenever a row is added. A formula can display a generated-looking value, but formulas recalculate from their inputs. For IDs that must remain fixed, use a script or convert formula results to values.

Make simple IDs with a row-based formula

For a small list where the ID only needs to distinguish rows in the current table, put a formula in the ID column and fill it down. Suppose row 1 contains headers, column A is for IDs, and column B contains the record name. In A2, enter:

="ID-"&TEXT(ROW()-1,"00000")

In row 2, ROW() returns 2, so the formula displays ID-00001. In row 3 it displays ID-00002, and in row 12 it displays ID-00011. The TEXT pattern adds leading zeroes for readability; it does not limit the sequence to five digits. Copy the formula down as far as needed, or drag the fill handle at the lower-right corner of A2.

This approach is quick and transparent, but the displayed ID is based on the row number, not on a permanent record property. Sorting, inserting, or deleting rows can change the values. If two people have the same record in different rows, they will receive different IDs, but moving either record can assign it a different one. Use this formula for temporary row labels, working lists, or reports where that behavior is acceptable—not as the permanent key for important records.

Keep formula-generated IDs from changing

If you want to use the simple sequence but need to freeze the current results, replace the formulas with their displayed values. First check that the IDs are correct and that the copied range covers every record. Then copy the ID cells and choose Edit > Paste special > Values only. The cells will contain fixed text rather than formulas, so later row changes will not recalculate the IDs.

In a browser on Windows or ChromeOS, Ctrl+C copies and Ctrl+Shift+V pastes values only in Google Sheets. On a Mac, use Command+C and Command+Shift+V. The menu option is useful if a keyboard shortcut behaves differently in your browser. Make a copy of the sheet before a large conversion if you need a simple way to recover the original formulas.

Freezing values solves formula recalculation, but it does not by itself guarantee a clean ID process. If you later add records, you must make sure the next number is not already in use. Avoid deleting an ID and casually reusing it: another sheet, export, or person may still refer to the old record. For a controlled table, record the next available number or use a script that fills only blank ID cells.

Generate permanent UUIDs with Apps Script

For IDs that should not depend on row position, Apps Script can write a UUID into column A when someone enters a record in column B. A UUID is a long, structured identifier designed to make accidental collisions extremely unlikely. It is less readable than a short sequence, but it is useful when separate files or systems may create records independently.

To set up the example below, use a sheet with headers in row 1, IDs in column A, and record data in column B. Open Extensions > Apps Script, replace the editor contents with this code, and save the project. The built-in onEdit trigger runs when a user edits the sheet; it fills an ID only when the corresponding ID cell is blank.

function onEdit(e) {
  const sheet = e.range.getSheet();
  const firstRow = Math.max(e.range.getRow(), 2);
  const lastRow = e.range.getLastRow();
  const firstCol = e.range.getColumn();
  const lastCol = e.range.getLastColumn();

  // Continue only if the edit includes column B and a data row.
  if (firstCol > 2 || lastCol < 2 || lastRow < 2) return;

  const rowCount = lastRow - firstRow + 1;
  const records = sheet.getRange(firstRow, 2, rowCount, 1).getValues();
  const idRange = sheet.getRange(firstRow, 1, rowCount, 1);
  const ids = idRange.getValues();
  let changed = false;

  for (let i = 0; i < rowCount; i++) {
    if (records[i][0] !== '' && ids[i][0] === '') {
      ids[i][0] = Utilities.getUuid();
      changed = true;
    }
  }

  if (changed) idRange.setValues(ids);
}

Return to the spreadsheet and type or paste a record into column B below the header; the script should place an ID in column A on the same row. You normally do not run onEdit from the Apps Script editor, because it expects an edit event supplied by Sheets. This simple trigger handles a pasted block that includes column B as well as a single-cell edit. It does not generate IDs for changes made only by another script or by formula recalculation.

Utilities.getUuid() creates an identifier, but UUIDs are not mathematically guaranteed to be collision-free. Their design makes a collision very unlikely for ordinary spreadsheet use. If records are mission-critical, very high-volume, or created concurrently by many processes, use a database or an ID service with appropriate uniqueness constraints rather than treating a shared spreadsheet as the authoritative system.

Choose an ID method that fits the job

Before selecting a method, decide what the ID must survive. If you only need to number rows for a one-time list, a formula is usually sufficient. If IDs must remain attached to records through sorting and editing, use fixed values or automation. If multiple teams or systems create records independently, use an identifier designed for that wider scope.

Row formula: Fast and easy to inspect, but changes when row positions change. Best for temporary labels and simple reports.
Formula converted to values: Easy to create and then stable, but someone must manage the next number when adding records.
Apps Script UUID: Automatically writes a persistent identifier without relying on row order. Values are longer and less readable, and the script needs maintenance.
External system ID: Best when a database, form platform, or business application already owns the records. Keep that system’s ID rather than inventing a competing key in Sheets.

For a customer list used by several people, a practical setup is often a permanent ID in column A, descriptive information in later columns, and a rule that users do not edit or reuse existing IDs. Protecting the ID column can reduce accidental edits: select the relevant cells and use Data > Protect sheets and ranges. Protection controls editing; it does not make the IDs private or secure.

Common problems and misconceptions

A frequent mistake is using a random formula as if it created a permanent identifier. Functions such as RANDBETWEEN recalculate, so an ID may change when the sheet recalculates or when related data is edited. Random values can also collide. They are suitable for some simulations, but not a dependable way to assign lasting record keys unless the results are promptly stored as values and checked for duplicates.

Another issue is sorting only part of a table. If the ID column is not included with the rest of the record, IDs can become associated with the wrong names or details. Sort the complete table range, including the ID column, and use the header row correctly. A fixed ID should travel with its record; it should not be regenerated just because the record appears in a different position.

Do not assume an ID is unique merely because it looks unusual. Check existing values when importing data, combining sheets, or copying records. A simple duplicate check can be done with Format > Conditional formatting, using the custom formula =COUNTIF($A$2:$A,$A2)>1 for an ID list in column A beginning at row 2. Apply the rule to the ID range. This highlights repeated values; it does not prevent them or automatically repair them.

Finally, avoid using a person’s email address, phone number, or other changeable or sensitive detail as the ID. Those values can change, may be shared, and can expose more information than a neutral identifier. Keep descriptive data separate from the key and restrict access to the sheet according to the sensitivity of its contents.

Google Sheets compared with Excel

The row-based formula shown here also works in Excel because ROW and TEXT are available in both spreadsheet programs. Menu names and automation differ: this article’s steps use Google Sheets in a web browser, where Apps Script is available from Extensions > Apps Script. Excel has its own automation options, such as Office Scripts in supported Microsoft 365 environments and VBA in desktop editions; Google Apps Script code cannot be pasted into Excel and expected to run.

Google Sheets is convenient when several collaborators need to edit a shared list, but a spreadsheet is not a database with built-in relational integrity. A sheet can be sorted, copied, or edited in ways that break informal ID conventions. If other tools depend on the IDs, document the method, keep a backup, and consider making the ID column protected. For an application with strict uniqueness requirements, use the application or database as the source of truth.

Frequently Asked Questions

Does Google Sheets have a built-in UUID formula?
No. Google Sheets does not have a standard worksheet function named UUID that writes a permanent UUID into a cell. Apps Script can create one with Utilities.getUuid(), or you can use another system to assign IDs.

Will a row-number ID stay the same after sorting?
Not if it is generated by a formula based on ROW(). The formula reflects the row’s current position, so the displayed value can change when records move. Convert the results to values or use a script-generated ID if it must stay with the record.

Can I use RAND or RANDBETWEEN for unique IDs?
They can produce random numbers, but they are not reliable permanent IDs: recalculation can change results, and duplicates are possible. For stable IDs, write values once and check for duplicates, or use UUID generation or an external record system.

Can Apps Script assign an ID when a row is added?
Yes. An onEdit script can assign an ID when a user enters data, as in the example above. It will not run just because a formula recalculates, and edits made by another script do not automatically fire the simple onEdit trigger.

Are UUIDs guaranteed to be unique?
No identifier scheme should be described as an absolute guarantee without its system’s rules. UUIDs make accidental collisions extremely unlikely, while a database can enforce uniqueness with a constraint. For a small ordinary sheet, UUIDs are generally practical; for critical records, use a system that checks uniqueness.

Can I generate IDs for records in multiple spreadsheets?
Yes, but simple row numbers can repeat across files. Use a UUID or a shared external ID service, and define which system owns the identifiers. When combining existing sheets, check for duplicates before relying on the merged IDs.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.