AUTO-Generate Unique IDs in Google Sheets

Code.gs

// AUTO GENERATE SIMPLE UNIQUE ID'S FOR NON-EMPTY ROWS
//
// Author: Carlos Perez, [email protected]
//
// 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 = "Shifts";
var ID_COLUMN = 1;
var ID_LENGTH = 5;

// Thanks to Tom Spencer for this function
// Tom's website/blog is at fiznool.com
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 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
      }
    }
  });
  
}

@Luisnoel... 👍🏻 Happy it worked for you.

This worked really well, thanks! I actually removed the custom function generateUID() and the ability to select the UID's length for a slightly more opinionated call to Utilities.getUuid().

@adrian-cg, awesome! Do you mind sharing your code where you use Utilities.getUuid()? I haven't used that before.

@clayperez sure: https://gist.github.com/adrian-cg/ebe3542560ed1429cdef944f8d11e478

I read your code but don't understand how you made sure the newly generated ID won't be a duplicate of an existing one.

@newjie There's no guarantee that it won't duplicate. But practically improbable, given the number of possible values with a length of 4 or greater (1:13million chance). Increases to 1:44billion chance with a length of 6 characters.

…

On Tue, Sep 28, 2021 at 1:17 PM vincent ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ I read your code but don't understand how you made sure the newly generated ID won't be a duplicate of an existing one. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/0d689b02693b2e94a7d1ddea98a0571c#gistcomment-3908336>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAURNV72AWNWRU6RSOVO3IDUEIPFXANCNFSM4PZNB5RQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

Is this a good idea to avoid the slim possibility of duplication?

idCell.setValue( (function(){
          let id = 0;
          do {
               id = generateUID();
          } while (ss.getRange(2,ID_COLUMN,range.getRow()+1).getValues().flat().includes(id))
          return id;
        })() )

Works perfectly! Thank you very much

Yes!! thank you super exacly what I need