Question 275722 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Need to return a dynamic set of columns from 2d array

I need to return several columns from an array pulled out of Google Sheets. Currently my code is

let out = data.map(function (row, index) { 
    return [row[1], row[0], row[36], row[6], row[45], row[46], row[47], row[7]]});

This code works well. But the needs have changed such that the report will have different columns each week. It will always have the columns I need, but the position of those columns will be changing. I can get the column numbers with

const colHeaders = ["Full Name","EID","Email - Work","Management Level"];
...
let sheetHeaders = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().flat();
let out = [];
for (let i in colHeaders){
 out.push(sheetHeaders.indexOf(colHeaders[i]));
}

but I don’t know how to then use the list of header positions to return the full columns.

Tags:

2

Answers


  1. 0

    You can map the array of column numbers to the values in those columns.

    let colnums = colHeaders.map(h => sheetHeaders.indexOf(h));
//...
let out = data.map((row, index) => colnums.map(col => row[col]))
    Login or Signup to reply.
  2. 0 
    function gettingDataWithHeaderNames() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();//or whatever you wish
  const [hs,...vs] = sh.getDataRange().getValues();
  const pos = hs.flat().reduce((a, h, i) => { a.col[h] = i + 1; a.idx[h] = i; return a; }, {col:{},idx:{}});//idx returns index into array and col returns column number
  let out = vs.map(function (row, index) {
    return [row[pos.idx["COL2"]], row[pos.idx["COL1"]], row[pos.idx["COL10"]], row[pos.idx["COL7"]]]
  });
  //let end ="is near";
}

    ActiveSheet Example:

    COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
    1 10 19 11 19 0 1 13 0 14
    17 8 12 15 19 17 19 16 4 3
    18 1 19 5 15 5 3 19 13 15
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search