Google Apps Script – Create Custom Unique IDs in Google Sheets [Update December 2021]

Google Apps Script, Google Sheet – Recent updates can be found in the changelog.

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

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


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.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

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.

Hire a Google Workspace Developer for your Business Needs

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 + 1).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;
id = id.toString()
    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.

Then, if we are just dealing with numbers, we need to convert our data to a string.

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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~Yagi

 

Changelog

Dec 2021 – Fix duplicate row ID when a row is inserted and then one is added to the end followed by a second row insertion. Line 48. Thanks to bnicho for pointing out this edge case. 

Dec 2021 – Fix error when only numbers IDs are required by converting the numbers to string as part of the extraction process. Line 90. Again, thanks to bnicho for finding this one. 

55 thoughts on “Google Apps Script – Create Custom Unique IDs in Google Sheets [Update December 2021]”

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

    Reply

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

      Reply

      1. Hi Kenneth,

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

        Reply

      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

        Reply

        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,

          Reply

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

    Reply

    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

      Reply

  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 🙂

    Reply

    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

      Reply

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

        Reply

  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?

    Reply

    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,

      Reply

      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

        Reply

        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

          Reply

          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

    Reply

    1. In the script editor click the timer trigger button and try adding a trigger onchange for the function 🙂

      Reply

      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.

        Reply

        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.

          Reply

  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.

    Reply

    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

      Reply

  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.

    Reply

    1. Hi John,

      Do you have an 1’e’ inside your onOpen(e) function?

      Cheers,

      Reply

  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

    Reply

    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

      Reply

  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.

    Reply

  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.

    Reply

    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

      Reply

    2. I am using the script “as provided” and only added the location/tab name information.

      Reply

  11. I wasn’t able to replicate the error. Are you able to replicate it on another Google Sheet?

    Reply

  12. works great, thank you! If you could generate the unique ID onto a linked completed Google form that would be great for me too 😉

    Reply

  13. Thank you very much for your code. My user they paste a range of data not one by one. How can we adapt this code to generate multiple ID if they paste a range of data. thank you very much for your reply!!

    Reply

    1. Hi Lan Anh,

      Yes, you could. It would take a pretty significant rewrite of the code. You could find the e.range from the onEdit(e) which would be the range of pasted values from your users and then generate your first unique ID. From there you would generate the IDs equal to the length of the or the range. Once you have all those unique IDs you would paste them back into your sheet.

      I’ve added this to my list of builds down the track. Thanks for the idea.

      Cheers,

      Yagi

      Reply

      1. Hi Yagi,
        it’s sooo nice of you for replying me. Also, I face an error. I put the sample Id on the first row. There are several rows in between the sheet that i don’t need to generate ID yet. when the biggest Id is on the last row (that have data) For example: the last row has ID : T00010. And the blank row that now i need to generate data (in somewhere between the sheet) also generate T00010.
        I understand the error is because, script does not recognize the ID in the last row as biggest ID, so it generate it once more time. How can we solve this?
        Thank you very much for your respond

        Reply

  14. Hi Yagi! Thank you so much for this script. I was able to get it to work perfectly based off your tutorial. I do have a question though… for my needs, I’d like to generate unique IDs based off of an ‘edit column’ that is referencing a column in a different sheet. Currently I’m using this formula for my edit column: =UNIQUE(‘Individual Items’!B$2:C).

    When I use your script regularly typing in values in the edit column it works as expected. The unique formula however just leaves all of the ID column blank. Even if I add new line items from the sheet it’s referencing it will not populate with a new ID. Any help is appreciated!

    Reply

  15. Hi,

    So happy I stumbled upon this. I’d like to know if it would be possible to include the year as the front string of the Unique ID? Seeing as with this code, we have the first row as the seed so if I went ahead and manually input 2021, for example, it wouldn’t be useable for inputs for next year. How should the code be tweaked in a way to have the front string based on when the data was input into the sheet? I’m new to the whole google scripts but I think getYear() should work? I’m just lost as to how to include it in the code.

    Thank you in advance!

    Reply

    1. Thanks for the kind words, Primo.

      The quick and dirty answer is to add:
      let year = new Date().getFullYear()
      year + newID

      …before line 115.

      Your seed will need to be without the date though.

      If you want to update your seed you will need to remove the first 4 characters from the string of seed data when you grab it for the first time. This would be a little more tricky.

      ~Yagi

      Reply

  16. Hi Yagi!
    Your code is great.
    But I try to copy and paste 1 line and they don’t work.
    They only work when editing manually in “Edit Column”
    Because my data may have to copy and paste (old data) a lot…

    Phuc Duong

    Reply

    1. Hi Phuc,

      The onEdit() trigger can perform unexpectedly with copy and paste. The current design is not set up for copy and paste along a larger range but should work for single row copy and pastes (from memory. It’s been a while since I wrote this script). You would have to change this:

      To review the full range of pasted data in all rows. You could use e.range to look at this and validate before updating the IDs one at a time and past them in as a range.

      Give it a go and if you get stuck, feel free to post your code and either I or one of my readers might be able to guide you.

      Yagi

      Reply

  17. Hi, I tried running the script verbatim, it works well, however my only problem is, that if values are automatically generated through another script in the edit column, the code doesn’t work. How can I make the code recognize that the cell is not empty anymore.

    Reply

    1. Hi Mar897,

      Your best bet here is to adapt this script to your current script. So that it runs at the same time.

      ~Yagi

      Reply

  18. G’day,
    I got your code up and running and struck two little issues.
    1. Not working if the ID is pure numeric. Must have a leading or trailing non-numeric character of some description.
    2. When I insert a new row into the data and make the edit in the appropriate column, it makes an ID number and inserts it into the correct cell, but the last row of data seems to be omitted, so it typically does +1 to the second biggest value, not the biggest value.

    Reply

    1. Hi bnicho,

      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 the attached code that replicates the problem.

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

      Cheers,
      Yagi

      Reply

        1. Hi Ben,

          Thanks for finding those two edge cases. For the case where there is a duplicate ID at the end when another is inserted (“Sheet3”), I have added an extra row to the range on line 47.
          return sheet.getRange(IDrowStart,IDcol,lastRow-IDrowStart + 1).getValues();

          When the pure integer inputs are received Apps Script-side, they are considered as integers rather than text. As such, they need to be converted to strings before the code continues.

          I have updated this on line 90 with:
          id = id.toString()

          I’ve also updated the tutorial with the changes and recognised you for your contribution to improving the code. Much appreciated.

          ~Yagi

          Reply

  19. How can I use this when creating a new row from a webform? I’ve created a webform that add data to my sheets but I need a unique id. Your scenario sounds perfect

    Reply

    1. Hi Evo,

      The form submit trigger will probably be your best bet. You can create this trigger by going to Triggers > New Trigger and assigning your function to the event type ‘on form submit’.

      Then get the last row of the data item and update it with your new ID. Normally, I recommend that you do not touch the Form Response Sheet tab but copy and paste over the new response into a new sheet using the copy method or getValues and setValues.

      ~Yagi

      Reply

Leave a Reply