Variable number of dynamic filter conditions applied to an array-CodePudding

I'm trying to filter an array with a variable number ( 0 to three ) of filter conditions that are dynamic themselves. For example, one filter condition is based on a name a user selects from a dropdown list on a Google Sheet. If the filter conditions aren't dynamic, such as the name being hard coded, it works. See line below.

const filterFn1 = "x => x[0] === 'John Doe'";

I tried this, but it seems Google Apps Script doesn't recognize new Function

const filterFn1 = new Function("x => x[0] === '"   name   "'");

Full code:

function myFilter() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shtDash = ss.getSheetByName('Dashboard');
  const shtDB = ss.getSheetByName('Database');

  const lastDBRow = shtDB.getLastRow();
  const arrData = shtDB.getRange(2,1,lastDBRow-1,4).getValues();

  let arr = [];

  const name = shtDash.getRange('B1').getValue();
  if (name !== '') {
    const filterFn1 = "x => x[0] === '"   name   "'";
    arr.push(filterFn1);
  } 

  const category = shtDash.getRange('B2').getValue(); 
  if (category !== '') {
    const filterFn2 = "x => x[1] === '"   category   "'";
    arr.push(filterFn2);
  } 
  
  const software = shtDash.getRange('B3').getValue(); 
  if (software !== '') {
    const filterFn3 = "x => x[2] === '"   software   "'";
    arr.push(filterFn3);
  } 

  const filtered = arrData.filter(x => arr.every(f => f(x)));
}

CodePudding user response：

Try:

function myFilter() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shtDash = ss.getSheetByName('Dashboard');
  const shtDB = ss.getSheetByName('Database');

  let arrData = shtDB.getRange(2, 1, shtDB.getLastRow()-1, 4).getValues()

  const filters = shtDash.getRange(`B1:B3`).getValues().flat()
  
  filters.forEach((filter, index) => {
    if (filter.length) arrData = arrData.filter(row => row[index] === filter)
  })

  return arrData

}

This will check if the filter value isn't blank, then will filter the arrData by index of each filter at the index of each row.

For instance, if the first filter is X, arrData will be filtered for all rows containing X at index 0. If the second filter is Y, arrData will be filtered for all rows containing Y at index 1, etc.

If you need more explanation or modifications, please let me know!

Non-Index Version:

function myFilter() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shtDash = ss.getSheetByName('Dashboard');
  const shtDB = ss.getSheetByName('Database');

  let arrData = shtDB.getRange(2, 1, shtDB.getLastRow()-1, 4).getValues()

  const filters = shtDash.getRange(`B1:B3`).getValues().flat()
  
  filters.forEach(filter => {
    if (filter.length) arrData = arrData.filter(row => row.includes(filter))
  })

  return arrData

}

Learn More:

CodePudding user response：

Try

function myFilter() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shtDash = ss.getSheetByName('Dashboard');
  const shtDB = ss.getSheetByName('Database');

  const lastDBRow = shtDB.getLastRow();
  let arrData = shtDB.getRange(2,1,lastDBRow-1,4).getValues();

  let arr = [];

  const name = shtDash.getRange('B1').getValue();
  if (name !== '') {
    const filterFn1 = "x => x[0] === '"   name   "'";
    arr.push(filterFn1);
  } 

  const category = shtDash.getRange('B2').getValue(); 
  if (category !== '') {
    const filterFn2 = "x => x[1] === '"   category   "'";
    arr.push(filterFn2);
  } 
  
  const software = shtDash.getRange('B3').getValue(); 
  if (software !== '') {
    const filterFn3 = "x => x[2] === '"   software   "'";
    arr.push(filterFn3);
  } 

  console.log(arr)

  arr.forEach(f => arrData = arrData.filter(eval(f)))
  console.log(arrData)
}

main modification is arr.forEach(f => arrData = arrData.filter(eval(f))) so that each filterFnx contaned in arr will be applied after eval on it.

by the way, you can simplify by using Template Literals : use back-ticks (``) rather than the quotes ("") to define a string

const filterFn1 = `x => x[0] === '${name}'`;