How to reference a column number using its named range?

Question

I'm trying to write code that will automatically filter a column based on certain criteria no matter where that column occurs in the sheet.

I started by creating a named range and referencing that named range. However, I am trying to figure out how I can reference the column number based on the name range as this will vary based on where the column is in the sheet. Particularly, I am trying to fix the arguments within setColumnFilterCriteria which requires the column number.

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AttendeeStatus').activate();
  spreadsheet.getRange('AttendeeStatus').createFilter();
  spreadsheet.getRange('AttendeeStatus').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['', 'Attending'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(, criteria);

Currently, this requires me to manually enter the right column - is there anyway to automate this?

Answer 1

try this, it may work.

var columnNo = spreadsheet.getRange('AttendeeStatus').getColumn();