How can I get data by column name (header) in app script google sheet

Question

How can I get column values by column name (header) in app script. I don't want to use column indexes/column number. I want to retrieve whole column data using headers. I have tried multiple ways but couldn't get proper solution. I am using app script JavaScript for google sheet.

Answer 1

Here's a possible workflow.

Solution:

• Get all sheet values via getDataRange.
• Extract the first row (values) with shift.
• Use indexOf to get the column index where your desired header is located.
• Use map to get the values corresponding to that column.

Code sample:

function getColumnValues() {
  const sheet = SpreadsheetApp.getActiveSheet(); // Change this according to your preferences
  const header = "My header"; // Change this according to your preferences
  const values = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
  const headers = values.shift();
  const columnIndex = headers.indexOf(header);
  const columnValues = values.map(row => row[columnIndex]);
  return columnValues;
}