Skip to content

Instantly share code, notes, and snippets.

@clayperez
Created December 18, 2019 14:53
Show Gist options
  • Save clayperez/0d689b02693b2e94a7d1ddea98a0571c to your computer and use it in GitHub Desktop.
Save clayperez/0d689b02693b2e94a7d1ddea98a0571c to your computer and use it in GitHub Desktop.
AUTO-Generate Unique IDs in Google Sheets
// 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 = "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
}
}
});
}
@cavedon-felipe
Copy link

Works perfectly! Thank you very much

@steph7089
Copy link

Yes!! thank you super exacly what I need

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment