Apps Script for Google Sheets to implement automatic UUID and LAST UPDATED features.

gistfile1.txt

const headerRowI = 1; // the row with the header cols
const UUID_COLUMN_NAME = 'UUID'
const LAST_UPDATED_COLUMN_NAME = 'LAST UPDATED'

// ENTRY POINTS
// SHEET HOOK ENTRY POINTS
function onEdit(e) {
  // Spreadsheet is the 'file', Sheet is the actual sheet, similar but incompatible APIs on different objects, quite the gotcha
  var sheet = e.source.getActiveSheet();

  var uuidColumn = getColumnWithName(sheet, UUID_COLUMN_NAME);
  var modColumn = getColumnWithName(sheet, LAST_UPDATED_COLUMN_NAME);
  // the forEach returns a 0-based row index into the *current range*, so it must be offset vs the range start.
  e.range.getValues().forEach(function(rangeRowValues, rangeRowIndex) {
    var rowI = e.range.getRow() + rangeRowIndex;
    ensureUUID(sheet, rowI, uuidColumn);
    touchLastUpdated(sheet, rowI, modColumn);
  });
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSheet();
  ensureUUIDForSheet(sheet);
}

// UTILITY ENTRY POINTS
// replace `uuids` below with a list of all UUID's you want to "touch"
function utilTouchRows() {
  var uuids = `
85244e9c-baf0-4bde-8d60-24f735e0462f
80eac473-210b-409e-9ecd-e852193b3fb1
`.trim().split("\n")
  console.log(`Found ${uuids.length} uuids to touch`)
  var sheet = SpreadsheetApp.getActiveSheet()
  var modColumn = getColumnWithName(sheet, LAST_UPDATED_COLUMN_NAME)
  uuids.forEach(function(uuid, index) {
    var rowI = getRowByUUID(uuid)
    if (rowI) {
      touchLastUpdated(sheet, rowI, modColumn)
    }
  })
}

// SUPPORTING FUNCTIONS

// @return NULL | rowI -- The rowI in the sheet that contains the given UUID.
function getRowByUUID(uuid) {
  var sheet = SpreadsheetApp.getActiveSheet()
  var uuidColumn = getColumnWithName(sheet, UUID_COLUMN_NAME);
  // find row w/UUID
  var dataRange = sheet.getDataRange();
  var uuidColRange = sheet.getRange(headerRowI + 1, uuidColumn, dataRange.getNumRows(), 1);
  var uuidRangeValues = uuidColRange.getValues();
  for (var rangeRowI = 0; rangeRowI < uuidColRange.getNumRows(); rangeRowI++) {
    if (uuidRangeValues[rangeRowI][0] == uuid) {
      var sheetRowI = uuidColRange.getRow() + rangeRowI;
      sheet.getRange(sheetRowI, uuidColumn, 1, dataRange.getNumColumns()).activate();
      return sheetRowI;
    }
  }
  return null;
}

function activateSelectionForRowWithUUID(uuid) {
  var sheet = SpreadsheetApp.getActiveSheet()
  var rowI = getRowByUUID(uuid)
  if (rowI) {
    sheet.getRange(rowI, 1, 1, sheet.getDataRange().getNumColumns()).activate();
  }
}

function touchLastUpdated(sheet, row, modColumn) {
  // don't edit if this row is not past the header
  if (row <= headerRowI) {
    return;
  }
  var modCell = sheet.getRange(row, modColumn);
  modCell.setValue(new Date());
}

function ensureUUIDForSheet(sheet) {
  var uuidColumn = getColumnWithName(sheet, UUID_COLUMN_NAME);
  var data = sheet.getDataRange();
  var lastRow = data.getLastRow();
  for (var rowI = 1; rowI <= lastRow; rowI++) {
    var uuidCell = sheet.getRange(rowI, uuidColumn);
    ensureUUID(sheet, rowI, uuidColumn);
  }

  // protect the UUIDs from editing
  var range = sheet.getRange(1, uuidColumn, lastRow);
  var protection = range.protect().setDescription('Never edit UUIDs!');
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  protection.setWarningOnly(true);
}

function ensureUUID(sheet, row, column) {
  // don't edit if this row is not past the header
  if (row <= headerRowI) {
    return;
  }

  var uuidCell = sheet.getRange(row, column);
  // make it idempotent so that we do not *change* the UUID
  if (uuidCell.getValue() == "") {
    uuidCell.setValue(Utilities.getUuid());
  }
}

function getColumnWithName(sheet, colName) {
  var modifiedColumnName = colName || UUID_COLUMN_NAME;
  var header = sheet.getDataRange().offset(0, 0, 1).getValues()[0];
  var modifiedColumn;
  for (var i=0; i < header.length; i++) {
    if (header[i].trim() == modifiedColumnName) {
      modifiedColumn = i + 1;
      break;
    }
  }
  if (typeof modifiedColumn != 'number') {
    throw new Error("Can't find column with name: " + modifiedColumnName);
  }
  return modifiedColumn;
}

// TEST HELPERS BELOW
function testFakeEdit() {
    onEdit({
    user : Session.getActiveUser().getEmail(),
    source : SpreadsheetApp.getActiveSpreadsheet(),
    range : SpreadsheetApp.getActiveSpreadsheet().getActiveRange(),
    value : SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getValues(),
    authMode : "LIMITED"
  });
}

// ideally this below can be used to pull URL param from link and "jump" to proper row
// but I don't know how to read the URL from apps script
function testSelectUUID() {
  activateSelectionForRowWithUUID('0f19ada2-23a9-4b5d-ba62-c14d0fea92f4')
}