Reputation: 503
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
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
Reputation: 21528
Building up on top of the other answers to provide you with a comprehensive collection of functions.
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);
}
}
function getCellValueByColumnName(sheet, columnName, row) {
let cell = getCellRangeByColumnName(sheet, columnName, row);
if (cell != null) {
return cell.getValue();
}
}
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());
}
}
function getColumnValuesByName(sheet, columnName) {
let column = getColumnRangeByName(sheet, columnName);
if (column != null) {
return column.getValues();
}
}
Upvotes: 23
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
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