Is there a way to retrieve a column dynamically by it's column name (header)?

Instead of:

var values = sheet.getRange("A:A").getValues();

Something like: (Just for simplicity)

var values = sheet.getRange(sheet.column.getHeader("name").getValues();

Please keep in mind that Google Apps Script is roughly ES3.

Is there a way to retrieve a column dynamically by it's column name (header)?

Instead of:

var values = sheet.getRange("A:A").getValues();

Something like: (Just for simplicity)

var values = sheet.getRange(sheet.column.getHeader("name").getValues();

Please keep in mind that Google Apps Script is roughly ES3.

• javascript
• arrays
• multidimensional-array
• google-apps-script
• google-sheets

3 Answers 3

Here's a very simple one-line function you can copy. It returns the column number (A = 1, B = 2, etc.) for use in getRange, for example.

function getColByHeader(name) {
  return SpreadsheetApp.getActiveSheet().getRange('1:1').getValues()[0].indexOf(name) + 1;
}

You can write one ;)

function getColValuesByName(sheet, name) {
  var index = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0].indexOf(name);
  index++;
  return sheet.getRange(1,index,sheet.getLastRow(),1).getValues();
}

Although there is no direct way, there are plenty of ways to get what you want with a little set up:

• Get all data and filter it(no set up):

var values = sheet.getDataRange().getValues();
var headers = values.splice(0,1);
headerIdx = headers[0].indexOf("name");
values = values.map(function(row){return [row[headerIdx]];})

• Named ranges set up:

If you have named ranges associated with that column,

spreadsheet.getRangeByName('Sheet Name!name').getValues();//where 'name' is a named range

• Developer metadata set up:

If you have developer metadata associated with that column,

SpreadsheetApp.getActive()
    .createDeveloperMetadataFinder()
    .withKey(/*METADATA_KEY_ASSOCIATED_WITH_COLUMN*/)
    .find()[0]
    .getLocation()
    .getColumn()
    .getValues();