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

Post date: May 05, 2017 10:48:6 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);

   };

  };

 };