Add timestamp to named column in Google Spreadsheet onEdit script

Question

I am trying to figure out how to get the index of a column with a specific name in a Google script spreadsheet.

I am working on a custom Google Script that automatically adds timestamps to a spreadsheet when editing it. The current version successfully adds a timestamp to the last column of the active cell.

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

This would work better for me, because then I could place the timestamp column wherever I want, and not worry that Script would cancel something important by accident.

My current Script is as follows:

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 index instead of the active row 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 can get the values ​​in your title bar and search for the desired title in these values ​​with 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); } } 

Answer 2

It was easier than I thought! Between the last two 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); } 

Answer 3

It is fantastic. How can I add a column to this, for example, "Update by", and specify the name of the user performing the update?