CODEWITHSUNDEEP
csvgoogle-apps-scriptgoogle-sheets
majid asad
majid asad

Reputation: 409

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

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.

Upvotes: 3

Views: 10101

Answers (4)

thdoan
thdoan

Reputation: 19067

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

Upvotes: 1

Iamblichus
Iamblichus

Reputation: 19309

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

Upvotes: 6

liquidkat
liquidkat

Reputation: 576

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

Upvotes: 0

Mike Steelson
Mike Steelson

Reputation: 15308

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.

Upvotes: 1

Related Questions