Add Timestamp To Named Column In Google Spreadsheet OnEdit Script

I'm trying to figure out how to retrieve the index of a column with a specific name in a Google Spreadsheet script. I'm working on a custom Google Script that automatically adds ti

Solution 1:

You could get the values in your header row, and search for the required header in those values using indexOf().

function onEdit(event)
{ 
  var timezone = "GMT-4";
  var timestamp_format = "yyyy-MM-dd HH:mm:ss";
  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();

  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf('Last Updated');

  if (dateCol > -1 && index > 1) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

Solution 2:

This was easier than I thought! Between the last 2 brackets:

var dateCol = headers[0].indexOf('Updated By');

  if (dateCol > -1 && index > 1) { // only timestamp if 'Updated By' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var Who = Session.getActiveUser();
    cell.setValue(Who);
  }