Get column values by column name not column index

Question

I'm new to Google Apps Script. I want to get the value of a specific cell by it's column name, not the column index. Here is what I have:

var rows = sheet.getDataRange();
var values = rows.getValues();
var row =values[1];
var rowStaffName = row[0];

Instead of using row[0], I want to use the column name. Is there an easy way to do that?

Answer 1

The following function retries the value in a column with a given name, in a given row.

function getByName(colName, row) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var col = data[0].indexOf(colName);
  if (col != -1) {
    return data[row-1][col];
  }
}

Specifically, var col = data[0].indexOf(colName); looks up the given name in the top row of the sheet. If it's found, then the value in the given row of that column is returned (row-1 is used to account for JavaScript indices being 0-based).

To test that this works, try something like

function test() {
  Logger.log(getByName('Price', 4)); // Or whatever name or row you want
}