1

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"

enter image description here

  • The post told us what you are looking for, and that is pretty fine, but it didn't mention what is the actual question (where you got stuck) and didn't shared your search/research efforts regarding that specific question. – Rubén Mar 12 '18 at 17:36
  • Thanks @Rubén, I edited my question to be more clear. Embarrassing at least, I made multiple different attempts and searching for an idea, and the best I came up with is terrible SQL... I know this isn't right, this is where I'm stuck – Ken Mar 12 '18 at 17:47
1

If the pattern will always be the same (three columns for each customer), rather than searching for a column named status, your script could look to column C and every third column after it.

C is column 3
F is column 6
I is column 9

For this you could use the remainder (%) operator. Something like this:

(r.getColumn() % 3 === 0)

The above will return true for columns C, F, I etc. and false for every other column.

The alternative is to check that the cell on the second row has the Status value:

(sheet.getRange(2,r.getColumn()).getValue() === 'Status')
  • Interesting! Unfortunately the # of columns for each customer won't always stay the same. And the first 5 columns before Customer A starts are unrelated to a customer (and should be excluded from the script). The only constant is the name of the column (in row 2) – Ken Mar 12 '18 at 18:11
  • @Ken I added an alternative condition to check the column header (2nd row value). – Rubén Mar 12 '18 at 18:14
  • Yes! That worked beautifully!! I honestly don't know how you know that, but that's magical. Thank you!! – Ken Mar 12 '18 at 18:53
  • Is there a way to use your code here to write a script which hides columns based on this "Status" in the 2nd row? var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); sheet.hideColumns(sheet.getRange(2,r.getColumn()).getValue() === 'Status'); – Ken Mar 13 '18 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.