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.
Date object. It was a mere 41 lines of code. You can take a look at it here:
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, ERC1299374
- It won’t handle non-alphanumeric characters. For example, 1394
- 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 >>
After an afternoon tinkering, the results turned out pretty good. Take a look at the demo below: