Get column values by column name not column index
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
}
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();
}
}
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();
}
}