javascript - Add timestamp to named column in Google Spreadsheet onEdit script

Question

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 timestamps to a spreadsheet when it is edited. The current version successfully adds the timestamp to the last column of the active cell's row.

I want to create a new version of this script that adds the timestamp to a specially designated column by using a specific column name. For example, I want to create a column in my spreadsheet named "Last Updated," and I want the script to detect the index of that column and automatically add the timestamps to it instead of the last column in the row.

This would work better for me, because I could then place the timestamp column wherever I wanted and not have to worry about the script overriding anything important by accident.

My current script looks like this:

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 cindex = actRng.getColumnIndex();

  // Here is where I want to use a named column's index instead of the active row's last column.
  var dateCol = sheet.getLastColumn();
  //var dateCol = sheet.getColumnIndexByName('Last Updated');

  var lastCell = sheet.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

  lastCell.setValue(date);
}

Answer 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);
  }
}