From this discussion, I have a script that nicely updates a column's cell with the date another column's cell was changed. This works well because the TODAY() function always updates to today's actual date, which doesn't work for a "last updated" application.
Script:
function onEdit(e)
{
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "Project Management")
{
var r = e.source.getActiveRange();
var ColumnToCheck = r.getColumn() == /* column I */ 9
if (r.getColumn() == /* column I */ 9 && r.getValue() == "Complete")
{
sheet.getRange(r.getRow(), r.getColumn() + 1).setValue(new Date());
}
else if (r.getColumn() == /* column A */ 9 && r.getValue() != "Completed")
{
sheet.getRange(r.getRow(), r.getColumn() + 1).setValue("");
}
}
}
I'm looking to edit this script to not just look at one specific column (in this script's example, column I, represented as column 9), but rather do this for every column with the header (in row 2) named "Status". So every time a cell in any column with the header of "Status" is changed, then change the adjoining column's "Date Completed" with the change date. See below for an example of how there's multiple columns with the same header, and the spreadsheet will continuously grow with additional columns for each customer.
How can the script be edited to search by column header name (in row 2), and not by the equivalent column number (e.g. column 9)? I'd love to set this as a variable as well. So instead of
r.getColumn() == /* column I */ 9
it'd be something like (can you tell I know SQL and need to brush up on my javascript?)
r.getColumn() WHERE row2 == "Status"