CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
MikeTaylor
MikeTaylor

Reputation: 35

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

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?

Upvotes: 1

Views: 1019

Answers (3)

J. G.
J. G.

Reputation: 1832

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.

Upvotes: 0

ross
ross

Reputation: 2774

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:

Upvotes: 2

Joshua Bird
Joshua Bird

Reputation: 65

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")

Upvotes: 0

Related Questions