Google Sheets UniqueId Apps Script

Here’s a tip on creating unique ID’s directly in Google sheets with Apps script:

The code works by scanning your sheet for edits in rows. If the edited row doesn’t have a ID in a set column (aka empty), it creates one.

This code does not add unique ID’s to empty rows, or change the unique ID if the row is edited.
Note that the unique ID doesn’t go away if you delete all other information in a row.

  • Open your sheet and add a column with a header name with for example “ID”

  • Go to Tools -> Script Editor and give your new script a name.

  • Paste the code and change the following values:
    – var SHEETNAME = “Your sheet”; (Change this to the name of the sheet which you are going to use)
    – var ID_COLUMN = 1; (Change it to the number corresponding to your column letter where the header is “ID” or whatever you called it)
    – var ID_LENGTH = 5; (Change this to 8 to be consistent with Appsheet’s recommendation)

  • Save your project

  • Then click image

  • Click add trigger

  • Then choose the settings below
    image

Code is here:

EDIT:

Change these parts (the function) to get a letter first in the UniqueID:

function generateUID () {
var ALPHABET = ‘0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
var ALPHABET2 = ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’;
var rtn = ‘’;
rtn += ALPHABET2.charAt(Math.floor(Math.random() * ALPHABET2.length));
for (var i = 0; i < ID_LENGTH; i++) {
rtn += ALPHABET.charAt(Math.floor(Math.random() * ALPHABET.length));
}
return rtn;
}

6 Likes

Welcome to the AppSheet forums, @Ratatosk!

Pretty cool to see someone post an Apps Script integration with a GitHub repo on their first day in the forums. :+1:

3 Likes

Thank you @GreenFlux ! I giveth and taketh! :smile:

It’s not my code, but I’ve used apps script extensively in Google Sheets before. Now our company has grown so much that we need other solutions as well. Too many people editing complex spreadsheets is not viable anymore.

So a mix between apps script and the simplicity of Appsheet seems like a good deal!

See you around!

5 Likes

Thanks from me too! I looked at the code and it seems that it distinguishes between uppercase and lower case letters. Inside of AppSheet, UNIQUEID() seems only to use lowercase letters. So, I suspect that keys are case insensitive. Is that correct? I looked at the article on keys but couldn’t find the answer there:

@Steve, since you have written about the documentation article about UNIQUEID(), I wonder if you know the answer to this.

If AppSheet keys are case in sensitive, it might be a good idea to tweak the script.

By the way, in AppSheet UNIQUEID() produces more numbers than letters in its keys. I wonder why that is. If numbers and letters were chosen randomly, you would get more letters.

Speaking of letters vs. numbers, if a key is all numbers, it can cause problems in Google sheets:

So, it might be a good idea to tweak the script so that a letter always comes at the head of the key.

4 Likes

Actually, I’m interested in generating keys outside of AppSheets because I’ve set an app of mine up to accept data via Google sheets “importrange” formula, provided that the data has a proper key to begin with. For this reason, I made the following key generator using RANDBETWEEN and CONCATENATE. I’ll share it here in case anyone might prefer this approach to using script:

Good point! Maybe also be sure to set the column format in Google Sheets to “Plain text” instead of “Automatic”.

Added a couple of lines on the code:

// AUTO GENERATE SIMPLE UNIQUE ID’S FOR NON-EMPTY ROWS
//
// Author: Carlos Perez, clayperez@gmail.com
//
// Purpose: This Google Sheets script fires when any cell is edited and
// inserts a random (reasonably unique) UID of ID_LENGTH length
// into the specified ID_COLUMN. For instance if the first column in the
// sheet specified by SHEETNAME is the column where you would like the
// UID injected, then ID_COLUMN should be 1.
//
// SETTINGS
// SHEETNAME = Name of spreadsheet to monitor
// ID_COLUMN = The column where you want your UID injected. Injection corresponds to the row that triggered this script.
//
// UID UNIQUENESS
// ID_LENGTH = 3: 226,920 posible values
// ID_LENGTH = 4: 13,388,280 posible values
// ID_LENGTH = 5: 776,520,240 posible values
// ID_LENGTH = 6: 44,261,653,680 posible values
//
// REFERENCES:
// https://developers.google.com/apps-script/guides/triggers/events
// https://www.fiznool.com/blog/2014/11/16/short-id-generation-in-javascript/

var SHEETNAME = “Ark 1”;
var ID_COLUMN = 6;
var ID_LENGTH = 8;

// Thanks to Tom Spencer for this function
// Tom’s website/blog is at fiznool.com
function generateUID () {
var ALPHABET = ‘0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
var ALPHABET2 = ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’;
var rtn = ‘’;
rtn += ALPHABET2.charAt(Math.floor(Math.random() * ALPHABET2.length));
for (var i = 0; i < ID_LENGTH; i++) {
rtn += ALPHABET.charAt(Math.floor(Math.random() * ALPHABET.length));
}
return rtn;
}

function onEdit(evt) {
var range = evt.range;
var sheet = range.getSheet();
if(sheet.getSheetName() !== SHEETNAME) return;

// getValues()
// as cells: [[A1,B1,C1],[A2,B2,C2],[A3,B3,C3],[A4,B4,C4],[…]]
// as locals: [[11,21,31],[12,22,32],[13,23,33],[14,24,34],[…]]
var rangeValues = range.getValues();

// Loop over each row of the range and check for data being entered.
// We don’t want to commit a UID value to the ID column if the data
// in adjacent columns was just deleted. We only want a UID for rows
// with data in them.
rangeValues.forEach(function(row,index,arr){
var conc = row.join("").length; // Where we check for data in the row
if(conc > 0) { // The current row edited is NOT empty. Proceed.
var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); // This is a 1-dimensional range that contains the ID cell we want to populate
var idCell = idRange.getCell( 1, 1 ); // This drills down into that single-dimensional range and picks out the cell (yeah, seems redundant but… Google)
var idValue = idCell.getValue(); // This is the actual value of that ID cell. If there’s already a UID in there, we DO NOT want to change it.
if (idValue == “”) {
idCell.setValue( generateUID() ); // Ok, everything above checks out. Let’s give this row a UID
}
}
});

}

3 Likes

Thanks!!

1 Like

By the way, if anyone would prefer the more primitive method of using a formula to generate keys, here’s another approach:

=Concatenate(Char(randbetween(1,26)+96),randbetween(1,9),Char(randbetween(1,26)+96),randbetween(1,9),Char(randbetween(1,26)+96),randbetween(1,9),Char(randbetween(1,26)+96),randbetween(1,9))

This alternates between letter and number. Of course, it’s necessary to copy and then paste again as text in order to make a stable key.

Here’s one more variation:

=concatenate(char(randbetween(97,122)),if(randbetween(0,1)=0,randbetween(0,9),char(randbetween(97,122))),if(randbetween(0,1)=0,randbetween(0,9),char(randbetween(97,122))),if(randbetween(0,1)=0,randbetween(0,9),char(randbetween(97,122))),if(randbetween(0,1)=0,randbetween(0,9),char(randbetween(97,122))),if(randbetween(0,1)=0,randbetween(0,9),char(randbetween(97,122))),if(randbetween(0,1)=0,randbetween(0,9),char(randbetween(97,122))))

1 Like
3 Likes