Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time

Google Apps Script, onEdit, Date, Google Sheets

Here is the scenario. You have a small business and you want to store your customers, products and sales information on separate Google sheets.

You’re probably going to have someone from your staff enter new customers, products are sales transactions.

We know if we get them to enter unique ID’s in manually that mistakes are going to get made. So how about we automate this process with unique ID’s based on date-timestamps.

Why use a date-timestamp to create a unique ID?

Every year is unique. Every day, hour, minute, second and millisecond of that year creates a unique number. This mean that a new unique id will be create every millisecond for us.

Wow! Wow! Wow! Yagi! Just hold it one damn minute! You could  have multiple results each millisecond that would each be the same number!

Well, true if we were running a loop generating and publishnig our date-timestamp, we could have multiple numbers. However, we are generating this unique ID as an onEdit function when the user adds some information in Google Sheets to a cell and an adjacent cell  returns our unique number.

The user’s input and then the calls to and from the server to the Google Sheet will be sufficiently slow enough not to have a number generated multiple times a millisecond, so we are safe there.

We have the main idea of what we are planning, let’s move onto the example.

The Code

Quick Use

To make this your own. Simply copy and paste the code into your own project. Then, for each sheet that you will be creating a unique ID on, add an array starting at line 5:

[ Sheet, Front letters, Back letters, ID Column, Edit Column],

You can delete out all the other sample ones from the example.

What does each one mean?:

• Sheet: name of the sheet tab.
• Front letter: the string of letters you want at the front of your Unique ID. If you don’t want letters, leave it empty e.g. "".
• Back letter: the string of letters you want at the back of your Unique ID. If you don’t want letters, leave it empty e.g. "".
• ID Column: This is the column your unique ID will appear in.
• Edit Column: This is the column you will edit or add data to. When data is added or edited it will trigger the code to add the unique ID.

Code Breakdown

onEdit()

This code is run when the user edits or adds data to a selected cell in a predetermined column. Once the data is added the adjacent edited cell displays the new unique ID. The onEdit() function is a reserved term for Google Apps Scripts that generates a trigger.

The onEdit() function references a nested array of each time we want to create a new unique ID in a sheet. Look above at Quick Use for a breakdown on what to put in the array.

When the spreadsheet opens the onEdit() function is essentially run in a holding pattern until an edit is made to the allocated cell that triggers the function to create the ID.

On line 12, we call the active spreadsheet we are using. Line 13 then identifies the Sheet actively in use.

The function then on line 16 to 38 loops through each of the sheetVals back up on line 4-5.

Before all the magic happens, we set our variables giving one to each value of the nested array of the Sheet value. For example, on the first time through the loop we will look at:

["StaffID","STA", "",1,2]

Where var:

• sheetID =”StaffID”
• frontStr = “STA”
• backStr = “”
• IDcol = 1
• editCol = 2

On the next iteration sheetID would equal :” ProtuctID” etc.

We’ll use an offset (line 23) to position our new ID. The offset indicates where the id will be to the left or right of the inputted or edited cell. To find the offset we subtract the ID column number from the Edit Column number (Note: we don’t use column letters here so the column numbers start from 1).

var offset = IDcol - editCol;

Line 25 checks if the current active spreadsheet’s name is equal to our current iteration – from our for loop – of our sheetID.

If the sheetID is a match, we create a new date and grab its primitive value:

var date = new Date().valueOf();

The valueOf() method of Date is the total number of milliseconds that has passed since midnight 01 January, 1970 UTC.

This provides us with a unique number each millisecond:

1548154470257

We then add the frontStr and backStr to our new unique number.

var newID = frontStr+date+backStr; 

Next in lines 30-31, we find the active cell in the spreadsheet. We then find that cells column number and compare that with the current iteration of sheetVals edit column number.

If the selected cell and the edit column match, we then find the cell we want to change with offset. offset takes two values, the row and the column. We only want to change the column number so we set the row to zero and then the column will be set to our variable we created earlier “offset”.

var cellToChange = selectedCell.offset(0,offset);

Finally, we set the new value of our new ID.

cellToChange.setValue(newID);

Only creating a new ID if one has not been created.

The above is great and all but what if you only want to change the ID the first time you add data to the rows?

Basically, we are asking that if a unique ID already exists for this row, do nothing.

To do this we make use of the isBlank() method on line 35 before we finally decide to add the new ID.  This is pretty self explanatory. If an id already exists in the cell we want to change, then do not update the cell with a new id.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Make an enquiry on my ‘Hire me!’ page. I occasionally pick up projects. If I'm unable to right now, I can still help you find a great trusted freelancer.