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.

 

 

 

 

19 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

  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.

Leave a Reply