Check if a column header title matches a string and if so then return the column index

Question

I am trying to write a script for google sheets which returns the date in the next cell when the user enters 'y' in the current cell. I have a script which does this already, but the problem with my script is that the columns which it is evaluating is based on the column index, which means if our data set ever grows then these columns always have to stay in the same index which is creating a lot of organizational issues.

My question is..

Is it possible to look for the column header title rather than the column index in my code, and if so, what changes would I need to make?

function onEdit(e) {
    if ([19].indexOf(e.range.columnStart) == -1 || ['y', 'Y'].indexOf(e.value) == -1) return;
    e.range.offset(0, 1)
        .setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy"))
}

This code currently looks at column index 19 and when either 'y' or 'Y' is entered into a cell in column index 19 it then outputs the date in the next cell in column 20.

How can I change the code to look for where the column header = 'Replied?' rather than index?

So you're trying to write a date if the following criteria are met: value is written into column 19 (S); the header of that column is 'Replied?' and the value written is either 'Y' or 'y' ? — ross, Jul 16 '19 at 14:27
Thats exactly it Ross. If the column header = 'Replied?' then when the user enters in a 'y' into that column, I would like for the date of that edit to be made into the neighbouring cell in column 20 — MikeTaylor, Jul 16 '19 at 14:31

score 2 · Accepted Answer · answered Jul 16 '19 at 14:42

Goal:

If the following criteria is met:

Value is written into column 19 (S).
Header of column 19 (S) is 'Replied?'.
Value written is either 'Y' or 'y'.

Then write a date into the adjacent cell.

Code:

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var value = e.value.toUpperCase();
  var header = sh.getRange(1, col).getValue();
  if (col === 19 && value === 'Y' && header === 'Replied?') {
    sh.getRange(row, 20).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy"))
  }
}

Explanation:

I've based everything on the event objects passed to your onEdit trigger. For var value I have used toUpperCase() so that we don't have to check for either 'Y' OR 'y', only 'Y' alone. Also, instead of using range.offset I have just specified column 20 specifically in the getRange().setValue().

References:

This worked perfectly Ross! I really appreciate your help. I made one small adjustment as I am not sure where the 'Replied?' column might actually be so I created a target variable; `var target = sh.getRange(1, col).getColumn();` and then modified the if function to edit target+1 rather than 20 to make it more flexible `sh.getRange(row, target+1).setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy"))` Just putting this here for anyones future use :) You rock Ross! — MikeTaylor, Jul 16 '19 at 17:15

score 0 · Answer 2 · answered Jul 16 '19 at 14:17

One possible way to do this is to name the column/ cell in google sheets. See this website on how to.

Basically:

Open a spreadsheet in Google Sheets.
Select the cells you want to name.
Click Data and then Named ranges. A menu will open on the right.
Type the range name you want.
To change the range, click Spreadsheet Grid.
Select a range in the spreadsheet or type the new range into the text box, then - click Ok.
Click Done.

You can then refer to that named cell in google scripts by creating a custom function

function myGetRangeByName(n) {  // just a wrapper
  return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n).getA1Notation();
}

Then, in a cell on the spreadsheet:

myGetRangeByName("Names")

score 0 · Answer 3 · answered Jul 16 '19 at 16:51

I'd do this.

function onEdit(e) {
    var editedColumn = e.range.columnStart;
    var sh = SpreadsheetApp.getActiveSpreadsheet();
    var ss = sh.getSheetByName("This");//you only want onedits to the specific page
    var data = ss.getDataRange().getValues();
    var header = data[0][editedColumn];
    if (header != "Replied") return;
    if(e.value.toLowerCase() == "y"){
    e.range.offset(0, 1)
        .setValue(Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy"));}
}

You could also consider using a checkbox, that might be faster for your users.

Check if a column header title matches a string and if so then return the column index

3 Answers3

Goal:

Code:

Explanation:

References: