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:

The  Code

Continue reading “Google Apps Script – Create Custom Unique IDs in Google Sheets [Update December 2021]”