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?

Parlance

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
} 

Tucson

Why the if (col != -1) ? In what cases could columns be -1 if it's 0 indexed? – Placet

@Placet -1 if a column with that name is not present. – Knowall

Answer 2

Building up on top of the other answers to provide you with a comprehensive collection of functions.

getCellRangeByColumnName

function getCellRangeByColumnName(sheet, columnName, row) {
  let data = sheet.getDataRange().getValues();
  let column = data[0].indexOf(columnName);
  if (column != -1) {
    return sheet.getRange(row, column + 1, 1, 1);
  }
}

getCellValueByColumnName

function getCellValueByColumnName(sheet, columnName, row) {
  let cell = getCellRangeByColumnName(sheet, columnName, row);
  if (cell != null) {
    return cell.getValue();
  }
}

getColumnRangeByName

function getColumnRangeByName(sheet, columnName) {
  let data = sheet.getRange("A1:1").getValues();
  let column = data[0].indexOf(columnName);
  if (column != -1) {
    return sheet.getRange(2, column + 1, sheet.getMaxRows());
  }
}

getColumnValuesByName

function getColumnValuesByName(sheet, columnName) {
  let column = getColumnRangeByName(sheet, columnName);
  if (column != null) {
    return column.getValues();
  }
}

Morganica

Answer 3

One might also need a function that returns the entire column of a given name, not just a single cell. This variation of the other answer worked well for that purpose:

function getByName(colName, sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getRange("A1:1").getValues();
  var col = data[0].indexOf(colName);
  if (col != -1) {
    return sheet.getRange(2,col+1,sheet.getMaxRows()).getValues();
  }
}

Karlmarxstadt

In my case i modified sheet.getMaxRows() to sheet.getLastRow()-1, then i got the intended column values without the header – Vindictive

Answer 4

Using the method .getDataRange() is an expensive operation. It is more performant to find the column via the headers row and then get the range of the appropriate column and return the value of the cell.

The fuction getValueByColumnName is about 26 times faster than the accepted answer in my tests. In my testing using .getSheetByName(ssName) didn't seem to affect the time meaningfully.

function perfTest(numRuns, func, args) {
  sumTimes = 0
  for (let i = 0; i < numRuns; i++) {
    const start = new Date().getTime()
    const val = func(...args)
    const finish = new Date().getTime()
    sumTimes += finish - start
  }
  avgTime = sumTimes / numRuns
  return avgTime
}

function getValueByColumnName(ssName, colName, row) {
    const wk = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(ssName);
    const headers = wk.getRange("A1:1").getValues()[0]
    const colNum = headers.indexOf(colName) + 1
    const value = wk.getRange(row, colNum).getValue()
    return value
}

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];
  }
}

result0 = perfTest(1000, getValueByColumnName, ["TODO", "COPIES", 1])
result1 = perfTest(1000, getByName, ["COPIES", 1])

Logger.log(`Avg. Time for 1000 tests: getValueByColumnName ${result0}ms, getByName ${result1}ms. `)
// Avg. Time for 1000 tests: getValueByColumnName 9.53ms, getByName 245.322ms. 

Mckeehan