Google Apps Script – Create Custom Unique IDs in Google Sheets

Google Apps Script, Google Sheet

Sometimes something on the surface that sounds downright easy turns out to be quite a complex procedure. My recent experience in creating a Custom Unique ID in Google Sheets using Google Apps Script was a case in point.

In a previous version, I created a simple unique ID generator by taking advantage of JavaScript’s Date object.  It was a mere 41 lines of code. You can take a look at it here:

Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time

Then curiosity got the better of me, and I wanted to know how to create a custom ID generator. My requirements were simple:

  • It needs to be able to take any size number format. For example, from 1003 to 100000000000003.
  • It needs to handle leading zeroes. For example 0000653.
  • It needs to be able to take optional leading and trailing letters. For example, AR1340203, 3000484030BSTN, ENT900848933IRE.
  • It needs to generate a unique ID based on the pattern provided by at least one ID in the ID Column of the Sheet.
  • It needs to be able to look through all the unsorted IDs in the ID column and find the next largest number that will become the new ID.
  • It needs to be able to create IDs on multiple sheets.
  • It needs to be customisable and easily reusable in other projects.

For this project, the code would not handle the following:

  • It won’t handle letters between numbers. For example, ERC1299374ER3900TT.
  • It won’t handle non-alphanumeric characters. For example, 13948%$&)
  • ID’s must be of the same length and the newly created ID must have the same length of characters as the previous ones. For example, A99C >>  A100C.

The Results

After an afternoon tinkering, the results turned out pretty good. Take a look at the demo below:

Custom Unique ID Google Sheets

The  Code

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

You can pop out the code above to view it besides this post so it is easier for you to follow along with the explanation. 

Quick Run

To quickly run Custom Unique ID on your Google Sheet, copy the code above and navigate to the Tools menu and select Script Editor. Delete out the preexisting code and paste in your new code in the file.  Select save and rename the project.

Selecting Sheets and ID Columns

On line 25 you will see the variable locations.

This is a nested array of sheet tabs that you will add to. Add all the sheets that you want to insert a custom unique ID into. In order from left to right input:

  1. The name of the Sheet tab.
  2. The column you want your custom IDs to be generated.
  3. The start of your ID range. For example. If you have a header of 1 row, and your first ID starts on row 2, then you will type 2.
  4. The edit column. This is the column you will input data that will trigger Google Apps Script to create your custom unique ID for that row.

Location format = [sheet, ID Column, ID Column Row Start, Edit Column]

For my example:

var locations = [

Next, in your sheet create your first ID in your designated ID column. This will be used as a template for your automatically generated IDs in the future.

That’s all you have to do to get started.

Code Breakdown


The onEdit(e)function  (line 32-138) is a predefined trigger in Google Apps Script. It watches your sheet interactions, and if you make changes, then it will run the code.  The e parameter is the event object or the cell or range that is sent to the function.

More examples of onEdit() in use can be found here:


Lines 33-41, collect all the spreadsheet, sheet and parameter information and store it in variables.

Looping  Through The Sheets

On line 119, we start our loop through the locations variable. The for loop iterates through each sheet, taking the sheet ID, ID column, the starting row of the ID column and the edit column.

for(i = 0; i < locations.length; i++){
  var sheetID = locations[i][0],
     IDcol = locations[i][1],
     IDrowStart = locations[i][2],
     EditCol = locations[i][3];

An offset is created from the IDcol value minus the EditCol value (line 125). The offset indicates where the ID column is relative to the Edit column.

var offset = IDcol - EditCol;

Line 126 gets the active cell. The active cell will match the first selected value of the e parameters.  I find this an easier method of ensuring that only the first cell of the active range is selected rather than getting it from the e parameter.

var cell = sheet.getActiveCell();

Conditions of Data Change onEdit

Lines 128-136 set the conditions that will determine when and where to the custom unique id.

if(sheetID === sheet.getName()){
    if(EditCol === col){
        //ID Already Exists the editing cell isn't blank.
        if(cell.offset(0,offset).isBlank() && cell.isBlank() === false){
            var newID = getNewID();

The first if statement ensures we are on the correct sheet tab that is currently active (Line 128).

We then want to match the EditCol– which is our entered values in our Locations array variable – matches the column of the currently active cell ( Line 129).

A new ID should only be added if the ID cell is blank and the editing cell contains a value. We handle this on line 131.

Then, we generate the new ID with the function getNewID() on line 132. Finally, we grab the currently active cell and offset its column to match the ID column and set the value with the new ID.


This function, starting on line 43, is the real engine under the hood for generating a new unique custom ID for our Google Sheet.

There are a number of nested functions taking on specific tasks. These functions are called down on lines 102-104.

var lastID = getLastID(IDrange());
var lettersNzeroes = getLettersNzeroes(lastID);
var newNumber = getNewNumber(lastID);

  • IDrange() : Gets all the current id’s and sorts them.
  • getLastID() : Selects the last ID of the range.
  • getLettersNzeroes() : Creates an array of leading letters and zeroes and trailing letters and stores them to be readded to the inner number.
  • getNewNumber() : Strips all the letters and leading numbers, transforms it to an integer so that 1 can be added to the number before is transformed back into a string.  This function also checks to see if the number has increased in character length. For example, from 9 (1 character) to 10 (2 characters), or from, say 999 (3 characters) to 1000 (4 characters).

If there is a difference in the character length of the new ID number, then line 106-109  removes a zero from the store leading letters and zeroes stored in lettersNzeroes(). This is on the provision that a zero exists in this string.

//If the number is 9,99,999,9999 etc we need to remove a zero if it exists.
if(newNumber.removeZero === true && lettersNzeroes.start.indexOf("0") !== -1.0){
    lettersNzeroes.start = lettersNzeroes.start.slice(0,-1);

Finally, the leading letters and trailing letters are joined together to create the new ID that is then returned back to the onEdit(e) function.

//Rejoin everything together
var newID = lettersNzeroes.start +
            newNumber.num +

return newID;


The IDrange(), on lines 44-49, first gets the data range of the active sheet. This contains all the cells with values. It then uses this information to find the last row of values.

function IDrange(){
    var dataRange = sheet.getDataRange();
    var lastRow = dataRange.getLastRow();

    return sheet.getRange(IDrowStart,IDcol,lastRow-IDrowStart).getValues();

To get the range of ID values not including the header, we use the getRange() method which takes:

  1. The Starting row number: IDrowStart
  2. The Starting column number: IDcol
  3. The end row number: lastRow-IDrowStart

It then gets the values of this range and returns them to the getNewID() function.

These values are recorded in a nests array, for example:

getNewID() = [["ABC21004"],["ABC21003"],["ABC21002"],["ABC21005"]]


The getLastID() function (lines 52-58) then takes the getNewID() values and sorts them so that the largest number is the last value in the array (line 53). It then stores the last value in the array (line 54) and returns it to the getNewID() function.

//Get largest Value in range
function getLastID(range){
    var sorted = range.sort();
    var lastIDval = sorted[sorted.length-1][0];

return lastIDval;


This function takes the last ID value from the getLastID() function and extracts any available leading letters or zeroes and any available trailing letters.

Why leading zeroes? When we get to the stage where we have to add 1 to the letters of the ID to create a new unique ID, we need to transform the values from a string to an integer. In the process, the leading zeroes are removed.

For example, the string “000023” converted to an integer would be, 23.

On line 63 we create a new regular expression that checks for any uppercase and lowercase letters, and zeroes. We then create the variable letterZero to store these characters in a string.

//Get any letters or zeroes.
var re = new RegExp("^([a-zA-Z0])$");
var letterZero = [];

The first for loop on lines 65-69 iterates through the id testing each character against the regular expression values. If any of the values exist, they are pushed into the letterZero array. This array stores the character, and the position of the character in the string. For example, [[0,”A”],[1,”B”],[character,position]].

//Get any letters or zeroes.
var re = new RegExp("^([a-zA-Z0])$");
var letterZero = [];
for(char = 0; char < id.length; char++){
        letterZero.push([char,id[char]]);// [[position, letter or zero]]

The next step is to categorize the letters and zeroes into the start block and the end block using the letterZero array we generated above(lines 71-82). First, two variables are created to take the two blocks. Next, we create a len variable to pack the length of the letterZero array minus one.

// Categorize letters and zeroes into start and end blocks
var startLetterZero = "",
endLetter = "",
len = letterZero.length - 1;
for(j = 0; j < letterZero.length; j++){
   if(letterZero[j][0] === j){
       startLetterZero += letterZero[j][1];
    }else if(letterZero[j][1] !== "0" && letterZero[len][0] - (len - j) == letterZero[j][0]){
        endLetter += letterZero[j][1];
var startNend = {"start":startLetterZero,"end":endLetter};

We then iterate through the letterZero array (line 76). If the position of our character matches the iterator (j) then we know it is part of the letter-zero block at the start of the ID. If this is true, we then store the characters in our startLetterZero string.

Otherwise, if the value is not zero ( We don’t want to collect any further zeroes in the ‘number’ part of our id) and the length of the letterZero array minus the result of the iterator minus the position of the last letterZero value equals the currently selected position value of letterZero, then add the values to the endLetter array.

For example:

  • letterZero = [["A",0],["B",1],["C",2],["D",6],["E",7],["F",8]]
  • j = 3       <<< iterator
  • len = 5   <<< letterZero.length - 1

= letterZero[len][0] - (len - j) === letterZero[j][0]

= letterZero[5][0] - (5 - 3) === letterZero[3][0]

= 8 - (5-3) === 6

= 8 - 2 === 6

= 6 === 6

If all conditions are met on line 78, we add the values to the endLetter string.

Finally, the startLetterZero and the endLetter strings are packed into the starNend object variable to be returned to the getNewID() function.

These will be joined to the new number once it is created.


The getNewNumber() function takes the getLastID() value. It returns two values, the new number as a string and whether that number has increased in length (For example from 99 (length of 2) to 100 (length of 3).

//Gets last id number. Adds 1 an checks to set if its new length is greater than the lastNumber.
function getNewNumber(id){
    var removeZero = false;

    var lastNum = parseInt(id.replace(/\D/g,''),10);//Remove letters
    var newNum = (lastNum+1).toString();

    if(lastNum.toString().length !== newNum.length){
        var removeZero = true;
    var newNumSet = {"num":newNum, "removeZero": removeZero};

    return newNumSet

First we set the removeZero to false. This considers that there will be no change in the length of the number.

We then remove all the alphabetic characters from the id string using regular expressions in the string replace() method (line 91) before transforming it into an integer. An increment of  1 is then added to the number and returned to a string.

We then crosscheck the length of the starting number against the length of the new number and change the removeZero variable to true if there is a change.

Finally, we then add the new number and removeZero boolean to an object and return it to the getNewID function where it removes a zero if there is a change in the number length before adding the leading and trailing characters to the new number.


This definitely turned out to be a much larger task than I initially expected. Particularly having to take into consideration, things like leading zeroes and cutting out the number to update it. However, it was certainly a good ride.

I know that I am going to make use of this in projects to come, so was definitely worth the time creating.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!*


*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.





Leave a Reply