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 code.gs 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 = [
["StaffID",1,2,2],
["ProductID",2,2,1],
["TransactionID",1,2,2],
["CustomerID",1,2,3]
];

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

onEdit(e)

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:

onEdit()

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();
            cell.offset(0,offset).setValue(newID);
        };
    };
};

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.

getNewID()

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 +
lettersNzeroes.end;

return newID;
};

IDrange()

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"]]

getLastID(range)

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;

getLettersNzeroes(id)

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++){
    if(re.test(id[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.

getNewNumber(id)

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.

Conclusion

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!*

~Yagi

*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.

 

 

 

 

36 thoughts on “Google Apps Script – Create Custom Unique IDs in Google Sheets”

  1. This is exactly what I was looking for. The ability to add multiple sheets is amazing. Thank you so much!

    1. Me too. However it was working fine for me. Now when I try to generate a unique ID in the sheet, it says NaN, NaNaN and so forth

      1. Hi Kenneth,

        If you post your code, or a sample I might have a chance to see where the problem is.

      2. Thanks for sharing this example and code. I am having the same problem. My code, is your code copied verbatim. I have only a StaffID sheet. I created column names to match your sheet. The ID in the first row is blank, and after that…

        NaN
        NaNNaN
        NaNNaNNaN
        NaNNaNNaNNaN
        NaNNaNNaNNaNNaN
        NaNNaNNaNNaNNaNNaN
        NaNNaNNaNNaNNaNNaNNaN

        1. Hi John,

          You will need to “seed” the id with the first row cell data (Line 8 instructions).

          Let me know if you still have trouble and you can paste your code in the comments below.

          Cheers,

  2. Hello i keep getting “TypeError: Cannot read property “range” from undefined. (line 37, file “Code”)”

    1. Hi Garrett,

      If you are copying the same code, in the tutorial, my guess is that you might not be referencing a relevant location (sheet tab). If you paste in your code, I can see better where the error occurred.

      ~Yagi

  3. Awesome script. thank you!!! Just a thought. I’ve noticed that if a record/row is completely deleted where an ID number was created successfully and was indeed unique, that the script will sometimes reuse the same id again. I experimented where rows were deleted from the spreadsheet and added rows at the end and added rows in the middle. For an invoicing system this works great since no one ever typically deletes any historical data, just keeps adding. With some data sets it would be good to make sure that an id is never reused ever again. (I’ve been using appsheet to create forms that will interact with my google sheet records and the reusing of the same id’s accidentally by a row later in time could lead to weird data syncing issues.) Would it be possible to create a unique hash for the id that uses a long string of random numbers/letters that would likely never be repeated since due to the sheer number of possibilities? Or perhaps have the script create or add the id’s that have been used to a second hidden sheet and have the script reference that list of id’s used and create a new one based on what’s on the hidden sheet? I could see us creating a id ‘seed’ on the first row of the hidden sheet too and the script referencing that. This way, even if a new row is created then the script will never reuse the same id since it keeps a ‘log’ of the id’s thus far used. I guess the only limit would be the number of rows that google allows a sheet to have so one day the hidden sheet with unique id’s would eventually get full. Any thoughts? sincerely someone who loves google sheets 🙂

    1. Hi Danny,

      Thanks for the message and kind words. I really appreciate your thoughtful response.

      Yeah, I think that if the last highest value is deleted, then you create a new row, you will get that same id with the current code.

      Thinking about it now, I’d agree that storing a sorted list of user IDs and referencing that would be a good approach.:
      1. Store all used (present or deleted) values in a separate sheet tab then check that sheet before adding a new value to the Unique ID reference sheet and the actual row you want to add the data to. (This is like you mentioned)
      2. Use the PropertiesService Class to store the values off the sheet and crosscheck that before updating your sheet.

      For a list that increased by one each time, you could also store the most recently produced ID in the PropertiesService each time. Then when you call your unique ID code it checks that super quickly, adds one to it and adds it to your sheet, then updates the most recent value to your property service.

      Yes, you could also create a unique hash each time, but I would always recommend crosschecking it against a list of already used IDs. If you have your IDs stored in a sorted list then one of the fastest ways to search for a duplicate would be with a binary search.

      As for reaching the limit of the row length of Google Sheets, I’d imagine before you hit that mark it might be time to consider migrating your sheet into a database 🙂 . https://medium.com/@eric_koleda/why-you-shouldnt-use-google-sheets-as-a-database-55958ea85d17

      1. Lol well said haha. Yes at that point a real database would be the best. Thank you for the tips on how to accomplish a way to cross check id’s already used! I love to build things into websites since they can be so customized for a specific use, but google sheets is so easy to adopt and is already a part of a lot of people’s workflow that tips like these are super valuable. Thank you! Oh and i saw your other post related to creating id’s based on the date/time and realized that that solution is perfect too because a date will never repeat itself now or in the future! (assuming one adds a record by hand and not via a script which possibly could be done fast enough that the date/time on a few records is the same.)

  4. Hi! This is fantastic – just what I need.

    Quick question though – it keeps saying that there’s a type error when I try to run it. (TypeError: Cannot call method “Range” of undefined. (line 9, file “Code”))

    var range = e.Range();

    At the point of this error popping up i’ve only referenced the document and have not changed anything at all in the code. i’m relatively new to scripts on sheets, so perhaps i’m missing something obvious?

    1. Hi Laura,

      Thanks for the kind words.

      did you use the var range = e.range() or var range = e.Range()? If it is capitalized it won’t work. That looks like the source of the issue. Let me know if you get stuck.

      Cheers,

      1. Hi Yagi

        Thanks for replying! I just checked and corrected the error (it was capitalised, d’oh!) but the same message still appears. I also tried re-pasting the code completely and only referencing my sheet.

        interestingly, this change means the script DOES work, but it only generates one serial number and then it stops working.

        Best,
        Laura

        1. Hi Laura,

          It’s difficult to see where the issue is without seeing the code interacting with the sheet. Feel free to share a sample Google Sheet with attached code that replicates the problem.

          Hopefully, I will have a chance to look at it.

          Cheers,
          Yagi

          1. Hi Yagi, Thanks so much for sharing your code. I’m coming across the same error message as Lauren, “TypeError: Cannot read property ‘range’ of undefined (line 31, file “UniqueIDs”)Dismiss”

            Line 31 being:
            var range = e.range;

            From your earlier replies, I also tried “var range = e.range();” and checked to make sure I’m referencing the right tab. Any tips for what I might be overlooking?

            Thanks!

          2. Hi Sarah,

            It is a bit hard to see what’s going on with just the error message you provided. My best guess is based on a few questions:
            1. Is your function named function onEdit(e)?
            2. Did you set up your locations list?

            If you would like to paste your code in the comments it will definitely help to identify the issue. I will take a look if I have time, of one of my readers might be able to take a look.

            Cheers,

          3. Thanks, copying the code below. Other info, sheet name is “Stills Log” and the same ID I have entered into that sheet is C0001.

            // Location format = [sheet, ID Column, ID Column Row Start, Edit Column]
            var locations = [
            [“Stills Log”,7,2,3]
            ];

            function onEdit(e){
            // Set a comment on the edited cell to indicate when it was changed.
            //Entry data
            var range = e.range;
            var col = range.getColumn();
            var row = range.getRow();

            // Location Data
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getActiveSheet();

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

            };

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

            };
            };

          4. Hi Sarah,

            A few questions,
            1. have you seeded your sheet with the first ID?
            2. onEdit requires you to manually change a cell by adding something in it. You can use formulas, Google Form data or code to insert data. onEdit won’t work. Is your edit column manually edited?

            Cheers,
            Yagi

  5. I’m new to this script stuff and find this works exactly as I need but not quite as I’d like. I have found that only manual input of data to the Edit column cells triggers the script.
    Is there a way to automate this script so that it creates a unique ID for new rows created in my spreadsheet when the data input comes from google forms?

    Thanks in advance

    Ichi

      1. Thanks for pointing me in the right direction. I added the installation code found here – https://webapps.stackexchange.com/questions/119684/diference-between-onedit-or-onchange-trigger

        function createSpreadsheetOpenTrigger() {
        var ss = SpreadsheetApp.getActive();
        ScriptApp.newTrigger(‘onChange’)
        .forSpreadsheet(ss)
        .onOpen()
        .create();
        }

        and changed the name of the onEdit function to onChange and it worked as I require. The trigger still states onEdit as the name of the function though. I have no idea because I’m a copy paste coder but it worked.

        1. I was lying. I have this in cell 1. =ArrayFormula(IF(LEN(A:A),if(ROW(A:A)=1,”Unique Client Reference”,TEXT(ROW(B:B)-1,”UCR-0000″)),)) to create reference ID’s and I was looking to do the same with script so I ended up reading the wrong column by trying to use the same ID’s in both columns. Daft.

  6. Hi, thank you for the post.

    Quick question – my id pattern is 2020/0001, then 2020/0002 and so on. Your script adds the “/” at the end of the id instead of middle like this 20200002/ instead of 2020/0002.

    What changes should I make?
    Thanks in advance.

    1. Hi Raghav,

      This code was not designed to handle not alphanumeric characters.

      However, it looks like you are creating a unique ID based on the year and a unique value. You could create an extra column and then join (“&” or JOIN) the year to he unique ID.

      Cheers,
      Yagi

  7. Hi Yagi,
    What you created is a lifesaver indeed. I’m new to scripts and I copied and pasted and made the necessary modifications and tried to run it but I got errors on LineTypeError: Cannot read property ‘range’ of undefined (line 35, file “Code”).
    I checked and it was the code – var range = e.range;
    Is there something I missed?
    Thanks for your help indeed.

  8. Hi,

    My name is Georgiana and i’am trying to automatically create and custom id and i discover your work.

    I follow your instructions, but got stocked at running the script.

    I copy the code in my script editor and made only this change:


    // Location format = [sheet, ID Column, ID Column Row Start, Edit Column]
    var locations = [
    [“leads”,1,5,4]
    ];

    When i am trying to run the script it says there is a problem in raw 32, which in you example is raw 35, since i’ve made the modifications as above – ” var range = e.range;”.

    Can you, please, help me!

    In this moment i am using the code you write for uniq id, but i need to use the custom one. Also i want to integrate it with another code, which sets automatically the date in the next column when the column with the id is populated.

    // Add date when inserting value in cell

    function onEdit() {
    var s = SpreadsheetApp.getActiveSheet();
    if( s.getName() == “Sheet.name” ) { //checks that we’re on Sheet1 or not
    var r = s.getActiveCell();
    if( r.getColumn() == 1 ) { //checks that the cell being edited is in column A
    var nextCell = r.offset(0, 1);
    if( nextCell.getValue() === ” ) //checks if the adjacent cell is empty or not?
    nextCell.setValue(new Date());
    }
    }
    }

    Thank you so much for your kind support.

    Best regards,

    Georgiana

    1. Hi Georgiana,
      Usually, the issue occurs when there is no initial unique ID in the ID column for you to seed the rest of the IDs.

      Re your integration of the date, onEdit(e) is a built-in trigger function and you should only have one of them in your code. You can still run both code snippets together, however under the onEdit(e) function or create two separate (recommended) functions, 1 for custom ID and one for the date insertion and call them from the onEdit(e) function.

      Cheers,

      Yagi

  9. Hi

    First of all a big thank you to post this. Makes life of the people (like me) who are dependent on google sheets (instead of databases) and yet want some of the DB like functionalities super easy.

    I am using your code to create unique item ID for my product catalog. However, for my usecase, the way i manage my google sheet is having products in same category together (for ease of browsing). So if i ever want to add a new product in catalog, i simply insert a row in middle of sheet (depending on where other products from same category are) and add the product there.

    In the above case, the unique product id generated is same as the biggest value of product ID (it doesn’t add 1). It works fine if the product added is at the bottom of the sheet.

  10. Is there something weird about applying this to an existing Sheets file? I can see it work on a blank file as I enter new content, but I have an existing file with data and it refuses to work:

    TypeError: Cannot read property ‘range’ of undefined (line 37, file “Unique ID”)

    I have double checked the column, row, data is present, even renamed the sheet. Makes no difference.

    1. Hi Rex,

      It’s a little difficult to see what’s happening for you. Does your line 37 contain:
      var row = range.getRow();
      Do all your values in the reference var locations match what is on your Google Sheet?

      Cheers,

      Yagi

Leave a Reply