Google Sheets UniqueId Apps Script

[Ratatosk]

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...)
  – var ID_LENGTH = 5; (Change this to 8 to be consistent with Appsheet’s recommendation)

• Save your project

• Then click

• Click add trigger

• Then choose the settings below
  

Code is here:

gist.github.com

https://gist.github.com/clayperez/0d689b02693b2e94a7d1ddea98a0571c

Code.gs

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

This file has been truncated. show original

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;
}

[GreenFlux]

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.

[Ratatosk]

Thank you @GreenFlux ! I giveth and taketh!

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!

[Kirk_Masden]

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:

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

@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:

New Bug Encountered: UNIQUEID() changed to scientific notation by Google Sheets' auto-formatting Questions

A few weeks ago I had an app with just one record that was behaving differently than the others. It kept losing references to child records. When I viewed the Google Sheet, the UNIQUEID() value had been auto-formatted in scientific notation, because it just happened to meet the format of [number] E [number], like 421e24. [Screen Shot 2019-12-12 at 11.50.47 AM] I manually changed that one ID, and updated references in child tables. I figured the chances another key meeting that format were low …

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

[Kirk_Masden]

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:

docs.google.com

Key machine

Sheet1 qtsawv7,q,t,s,a,w,v,7,If you need keys to use in AppSheet (or some other context), just do the following: (1) copy this sheet (2) type something in one of the empty cells to generate new keys (3) paste the keys (column A only) into the...

[Kirk_Masden]

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

[Ratatosk]

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
}
}
});

}

[Kirk_Masden]

Thanks!!

[Steve]

help.appsheet.com

Manually Generating UNIQUEID() Key Values

How to manually generate UNIQUEID() key values.

[Chacal33]

Hey,

Great script!
Is there any way to use this script for multiple sheets?
I have tried to duplicate the script and change the functions’ names but no luck this way.
Also, i am not very keen on the copy paste option.

Many thanks!

Matt

[Ratatosk]

There should be no reason why that would not work.

[Chacal33]

Hi,

I have actually renamed onEdit with something like onEdit1, onEdit2…
But it won’t work.

[Chacal33]

Hi,

Finally managed to make it work.
For those interested here is my code to generate uniqueID in multiple tabs in one google sheet:

var SHEETNAME1 = "1";
var SHEETNAME2 = "2";
var SHEETNAME3 = "3";
var ID_COLUMN = 1;
var ID_LENGTH = 8;

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

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

  var rangeValues = range.getValues();

  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; 
    if(conc > 0) { 
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); 
      var idCell = idRange.getCell( 1, 1 ); 
      var idValue = idCell.getValue(); 
      if (idValue == "") {
        idCell.setValue( generateUID() ); 
      }
    }
  })
}

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

  var rangeValues = range.getValues();

  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; 
    if(conc > 0) { 
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); 
      var idCell = idRange.getCell( 1, 1 ); 
      var idValue = idCell.getValue(); 
      if (idValue == "") {
        idCell.setValue( generateUID() ); 
      }
    }
  })
}

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

  var rangeValues = range.getValues();

  rangeValues.forEach(function(row,index,arr){
    var conc = row.join("").length; 
    if(conc > 0) { 
      var idRange = sheet.getRange( range.getRow() + index, ID_COLUMN ); 
      var idCell = idRange.getCell( 1, 1 ); 
      var idValue = idCell.getValue(); 
      if (idValue == "") {
        idCell.setValue( generateUID() ); 
      }
    }
  })
}


function onEdit(e) {
  SN1(e);
  SN2(e);
  SN3(e);
}

Hope this helped!

Matt

[Ratatosk]

Chacal33:

function onEdit(e) {
  SN1(e);
  SN2(e);
  SN3(e);
}

Nifty!

[angk34]

Hello. This is my problem situation.

1. The two Google Forms receive different data from users.
2. Using zapier, combine the two data into one Google spreadsheet “A”.
3. Each row of “A” needs a unique ID, so I wrote this script.
4. However, scripts do not work on data coming through the Zapier and Google Form.
5. And unique IDs are also required for existing data, not new data. However, this script seems impossible because the trigger is ‘onedit’.

How can I solve this problem?
To sum up, what I’m asking is,

“I want to give a unique ID to the data that Zapier or GoogleForm automatically puts in”.
“I want to give unique ID to existing data”.

I tried to solve this problem for a long time, but I couldn’t solve it. If you give me advice, I think it will be a great help. Thank you.

[dbaum]

You could adapt the script to be triggered on a schedule rather than by sheet edits.

[Espegg]

Great solution, but now if I have in the sheet the auto ID generation and in appsheet  (data panel view) I have set the field as KEY VALUE, Appsheet ask me in the form to enter a new record, the mandatory ID value to insert....

So how to exclude it?