Scripting‎ > ‎

Google Sheets - Auto update/insert "Last Upated" column on change

posted May 5, 2017, 3:48 AM by Chris Franklin   [ updated May 5, 2017, 3:49 AM ]
After a cell has been changed the script will look through the header row for a Column called "Last Updated" and update or insert the date and current time into the cell on that row under the column called "Last Updated"

Text Box

function columnnumber2letter(column) {
 var temp, letter = '';
 while (column > 0) {
  temp = (column - 1) % 26;
  letter = String.fromCharCode(temp + 65) + letter;
  column = (column - temp - 1) / 26;
 }
 return letter;
}


function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activecell = sheet.getActiveCell();
  var temp = sheet.getDataRange().getValues();
  var lastupdatecolumn = temp[0].indexOf('Last Updated') + 1;

  if ( activecell.getRowIndex() > 1 ) {  
   if ( activecell.getColumn() != lastupdatecolumn ) { //checks the column
     var row = activecell.getRow();
     var time = new Date();
     time = Utilities.formatDate(time, "GMT-05:00", "yyyyMMdd HH:mm");
     sheet.getRange(columnnumber2letter(lastupdatecolumn) + row.toString()).setValue(time);
   };
  };
 };

Comments