CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kelli Roddy
Kelli Roddy

Reputation: 503

Get column values by column name not column index

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?

Upvotes: 45

Views: 71447

Answers (4)

TYPKRFT
TYPKRFT

Reputation: 326

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.

Upvotes: 3

Paul Razvan Berg
Paul Razvan Berg

Reputation: 21528

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();
  }
}

Upvotes: 23

Krastanov
Krastanov

Reputation: 6549

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();
  }
}

Upvotes: 15

user3717023
user3717023

Reputation:

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
}

Upvotes: 62

Related Questions