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?

回答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
} 

回答2:

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

回答3:

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