Is there something similar to HLOOKUP in Google Apps Script?

Question

I need something similar to HLOOKUP in Google apps script. But rather than returning one value (as is the case with HLOOKUP), i need a range of values. I searched and saw some similar stuff but didn't get what i need. Basically i have data like in the image below.

If the value I am looking up is 102, then I need the range of values from F2:F6 (which are the names of participants in the program 102). Similarly, if the ProgramIDis 104, i need the range of values in the range H2:H5. Note that the number of participants varies in each program.

In reality, have over one hundred ProgramIDs or rows (and hence also those many columns with participant names; among these there is one appropriate column from where I need to the range of values). What is the best way to do this?

Appreciate any help. Thanks a lot!

Answer 1

I believe your goal is as follows.

• From the cells "E2:H", you want to retrieve the a1Notation of rows by searching the header value.
• For example, when you use 102 as the header value using your sample Spreadsheet, you want to retrieve the a1Notation of F2:F6.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp, letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  const values = sheet.getRange("E1:H").getValues();
  const obj = values[0].map((_, c) => values.map(r => r[c])).reduce((o, [h, ...v], i) => {
    const rows = v.filter(String);
    const col = columnToLetter(i + 5);
    o[h] = `${col}2:${col}${rows.length + 1}`;
    return o;
  }, {});


  // When "102" is used as the header value, the A1Notation is returned.
  const searchHeader = "102";
  console.log(obj[searchHeader])
}

If you want to achieve this using a custom function, you can also the following script.

function SAMPLE(values, search) {
  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp, letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  const obj = values[0].map((_, c) => values.map(r => r[c])).reduce((o, [h, ...v], i) => {
    const rows = v.filter(String);
    const col = columnToLetter(i + 5);
    o[h] = `${col}2:${col}${rows.length + 1}`;
    return o;
  }, {});
  return obj[search];
}

• In this case, when =SAMPLE(E:H,102) is put to a cell using your sample Spreadsheet, you can retrieve F2:F6.

References:

• map()
• reduce()