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

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

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.

In this way , you are able to get sheet values by columns. Provide the header names. //accept header by 1d array ['ID','Name','Date','Title','SupervisorName','SupervisorEmail'] //using sheet API to get values by columns.

const target_header = ['ID', 'Name', 'Date', 'Title', 'SupervisorName', 'SupervisorEmail'];

function get_dataBycolumns(target_header) {
    const values = Sheets.Spreadsheets.Values.batchGet(
        'YOUR SHEET ID', {
            ranges: [
                "Sheet1",
                // you can specify the header range. by getting the header .
                //or just go through each columns first cell , then decide return or not.
                "Sheet1!A1:CE1"
            ],
            majorDimension: "COLUMNS"
        }
    );
    const header = values.valueRanges[1].values.flat();
    const sheet_values = values.valueRanges[0].values;
    const arr_index = [];
    for (i = 0; i < target_header.length; i++) {
        header.indexOf(target_header[i]) > -1 ? arr_index.push(header.indexOf(target_header[i])) : '';
    }
    //methods 1, go through each colms first cell
    const filtered_values = sheet_values.filter((col, index) => {
        if (target_header.indexOf(col[0]) > -1) {
            return col;
        }
    });
    //methods 2, get the header first and then ge the index of header.
    const filtered_values1 = sheet_values.filter((col, index) => {
        if (target_header.indexOf(index) > -1) {
            return col;
        }
    });
}

Here's an efficient solution that avoids getting unnecessary extra data:

function getColumnData(headerName) {
  const sheet = SpreadsheetApp.getActiveSheet();
  // Get column index of headerName (starts at 1)
  const columnIndex = sheet.getRange('1:1').getValues()[0].indexOf(headerName) + 1;
  // Return only headerName data
  return sheet.getRange(2, columnIndex, sheet.getLastRow() - 1, 1).getValues();
}

If you want to flatten out the array that is returned, then change the return statement to this:

return sheet.getRange(2, columnIndex, sheet.getLastRow() - 1, 1).getValues().flat();

If your columns are uneven (e.g., column A is filled to row 10, column B is filled to row 7), then your shorter columns will have empty array items. To filter these out, change the return statement to this:

return sheet.getRange(2, columnIndex, sheet.getLastRow() - 1, 1).getValues().flat().filter(Boolean);