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.

CodePudding user response：

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.getDataRange().getValues();
  const headers = values.shift();
  const columnIndex = headers.indexOf(header);
  const columnValues = values.map(row => row[columnIndex]);
  return columnValues;
}

CodePudding user response：

If you want to retrieve the informations from a csv file, try

function getColumnValues() {
  var id = 'id of your csv file';
  var csv = DriveApp.getFileById(id).getBlob().getDataAsString();
  const [headers, ... rows] = Utilities.parseCsv(csv);
  console.log (rows.map(row => row[headers.indexOf('yourColumnHeader')]));
}

Array.prototype.indexOf()

Spread Operator : ... (three dots in JavaScript) is called the Spread Syntax or Spread Operator. This allows an iterable such as an array expression or string to be expanded or an object expression to be expanded wherever placed.