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