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)));
}
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!
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:
Try with const filterFn1 = function(x) {return x[0] === name}
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 = function(x) {return x[0] === name}
arr.push(filterFn1);
}
const category = shtDash.getRange('B2').getValue();
if (category !== '') {
const filterFn2 = function(x) {return x[1] === category}
arr.push(filterFn2);
}
const software = shtDash.getRange('B3').getValue();
if (software !== '') {
const filterFn3 = function(x) {return x[2] === software}
arr.push(filterFn3);
}
const filtered = arrData.filter(x => arr.every(f => f(x)));
console.log(filtered)
}