create a Google Sheets FILTER/SEARCH formula with Apps Script using "or" logic

Question

example sheet link: https://docs.google.com/spreadsheets/d/1Sqt7_Ev9iagbE9CZT1F2lAUJqc1fX4WfbyaOBGEfqyQ/edit?usp=sharing

Thanks to a response to a previous question, I have Apps Script automatically updating formulas in Google Sheets when keywords are entered into input cells for a resources database. Currently, the formulas work with "and" logic (i.e., results must meet all conditions).

=FILTER(resources!B2:B,SEARCH(B2,resources!J2:J),SEARCH(B3,resources!K2:K))

Is there a formula using multiple SEARCH formulas as conditions within a FILTER formula using "or" logic (i.e., at least one of the conditions is met)?

I tried adding outer braces to the conditions and using "+" but it returns the same results.

=FILTER(resources!B2:B,(SEARCH(B2,resources!J2:J))+(SEARCH(B3,resources!K2:K)))

If so, how what script could be added to return the formula? My hope is to have a cell with data validation for AND & OR that will be used as a reference for an IF statement to determine which end formulas are set into the sheet. This is the current working code that uses "and" logic.

// global variables
const ss = SpreadsheetApp.getActiveSpreadsheet();
const fcSheet = ss.getSheetByName('filterCode');
  const fcOption = fcSheet.getRange('B1').getValue();
  const fcInput = fcSheet.getRange('B2:B5').getValues();
const frSheet = ss.getSheetByName('resources');

var fcTabs = ['filterCode'];
var frTabs = ['resources']


// set simpleFilter formulas on edit
function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();

  if (fcTabs.indexOf(activeSheet.getName()) !== -1 &&
    e.range.columnStart == 2 &&
    e.range.columnEnd == 2 &&
    e.range.rowStart >= 2 &&
    e.range.rowEnd <= 6) {
    var cell = e.range;

    if (ss.getActiveSheet().getRange("B2").getValue() != "") {
      var b2 = 'SEARCH(B2,resources!J2:J)'
    }
    else { var b2 = "" }

    if (ss.getActiveSheet().getRange("B3").getValue() != "") {
      var b3 = 'SEARCH(B3,resources!K2:K)'
    }
    else { var b3 = "" }

    if (ss.getActiveSheet().getRange("B4").getValue() != "") {
      var b4 = 'SEARCH(B4,resources!L2:L)'
    }
    else { var b4 = "" }

    if (ss.getActiveSheet().getRange("B5").getValue() != "") {
      var b5 = 'SEARCH(B5,resources!M2:M)'
    }
    else { var b5 = "" }

     if (ss.getActiveSheet().getRange("B6").getValue() != "") {
      var b6 = 'SEARCH(B6,resources!Q2:Q)'
    }
    else { var b6 = "" }

    if (b2 == '' && b3 == '' && b4 == '' && b5 == '' && b6 == '') {
      fcSheet.getRange(['C2']).setValue(' ⭠ set at least one search key');
      fcSheet.getRange(['A8:C8']).clearContent()
    }

    else {
      var fcInputList = [b2, b3, b4, b5, b6]
      var concatenated = fcInputList.filter(Boolean).join(',');
      fcSheet.getRange(['A8:C8']).setFormulas([['=FILTER(resources!B2:B,' + concatenated + ')', '=FILTER(resources!H2:H,' + concatenated + ')', '=FILTER(resources!I2:I,' + concatenated + ')']]);
      fcSheet.getRange(['C2']).clearContent();
    }
  }
}

updated code attempting to use separate simpleAnd and simpleOr functions based on C6.

// global variables
const ss = SpreadsheetApp.getActiveSpreadsheet();
const fcSheet = ss.getSheetByName('filterCode');
  const fcOption = fcSheet.getRange('B1').getValue();
  const fcInput = fcSheet.getRange('B2:B5').getValues();
const frSheet = ss.getSheetByName('resources');

var fcTabs = ['filterCode'];
var frTabs = ['resources']


// set simpleFilter formulas on edit
function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();

  if (fcTabs.indexOf(activeSheet.getName()) !== -1 && 
        e.range.columnStart == 2 && e.range.columnEnd == 2 &&
        e.range.rowStart >= 2 && e.range.rowEnd <= 6) {
    var cell = e.range;
    if (fcTabs.indexOf(activeSheet.getName()) !== -1 &&
    e.range.columnStart == 2 &&
    e.range.columnEnd == 2 &&
    e.range.rowStart >= 2 &&
    e.range.rowEnd <= 6 ) {
    var cell = e.range;
      if (fcSheet.getRange('C6') == 'OR') {
      simpleOr();
      }
    else simpleAnd();
    }
  }
}

function simpleAnd() {
  if (ss.getActiveSheet().getRange("B2").getValue() != "") {
    var b2 = 'SEARCH(B2,resources!J2:J)'
    }
    else { var b2 = "" }

    if (ss.getActiveSheet().getRange("B3").getValue() != "") {
      var b3 = 'SEARCH(B3,resources!K2:K)'
    }
    else { var b3 = "" }

    if (ss.getActiveSheet().getRange("B4").getValue() != "") {
      var b4 = 'SEARCH(B4,resources!L2:L)'
    }
    else { var b4 = "" }

    if (ss.getActiveSheet().getRange("B5").getValue() != "") {
      var b5 = 'SEARCH(B5,resources!M2:M)'
    }
    else { var b5 = "" }

     if (ss.getActiveSheet().getRange("B6").getValue() != "") {
      var b6 = 'SEARCH(B6,resources!Q2:Q)'
    }
    else { var b6 = "" }

    if (b2 == '' && b3 == '' && b4 == '' && b5 == '' && b6 == '') {
      fcSheet.getRange(['C2']).setValue(' ⭠ set at least one search key');
      fcSheet.getRange(['A8:C8']).clearContent()
    }

    else {
      var fcInputList = [b2, b3, b4, b5, b6]
      var concatenated = fcInputList.filter(Boolean).join(',');
      fcSheet.getRange(['A8:C8']).setFormulas([['=FILTER(resources!B2:B,' + concatenated + ')', '=FILTER(resources!H2:H,' + concatenated + ')', '=FILTER(resources!I2:I,' + concatenated + ')']]);
      fcSheet.getRange(['C2']).clearContent();
  }
}


function simpleOr() {
const activeSheet = e.source.getActiveSheet();
  const rangeReferences = [ // Attach input cells to columns
      { source: "B2", column: "J" },
      { source: "B3", column: "K" },
      { source: "B4", column: "L" },
      { source: "B5", column: "M" },
      { source: "B6", column: "Q" }
    ];
    let fcInputList = rangeReferences.map(reference => { // Loop through input cells
      const ref = reference.source;
      const col = reference.column;
      if (activeSheet.getRange(ref).getValue() != "") {
        return col + "='\"&" + ref + "&\"'"; // Build formula: eg. K='"&B3&"'
      } else return '';
    });
    const c2 = fcSheet.getRange('C2');
    if (fcInputList.every(input => input == "")) {
      c2.setValue(' ⭠ set at least one search key');
      fcSheet.getRange(['A8:C8']).clearContent()
    } else {
      fcInputList = fcInputList.filter(Boolean);
      const formula = '=QUERY(resources!B2:P26, "SELECT B,H,I WHERE ' + fcInputList.join(" OR ") + '")'; // Build rest of formula
      fcSheet.getRange("A8").setFormula(formula);
      c2.clearContent();
  }
}

Answer 1

Formula - use QUERY:

You could use the QUERY function instead, with the where and the logical operator or. For example, if B2, B3 and B5 were filled up, your formula in A8 could be:

=QUERY(resources!B2:P26, "SELECT B,H,I WHERE J='"&B2&"' OR K='"&B3&"' OR M='"&B5&"'")

As you can see, a good thing about this formula is that you only need to call it once for all three output columns.

Update:

Interestingly, replacing OR with AND will result in a formula that uses the and operator instead, as you want. You'd just need to check whether the corresponding cell (C6) has the value AND or NOT, and use that information when building the formula:

=QUERY(resources!B2:P26, "SELECT B,H,I WHERE J='"&B2&"' AND K='"&B3&"' AND M='"&B5&"'")

Code snippet:

Then, in order to build and set this formula dynamically in your onEdit function, you could do the following:

function onEdit(e) {
  const cell = e.range;
  const activeSheet = e.source.getActiveSheet();
  const OR_AND_CELL = "C6";
  if (fcTabs.indexOf(activeSheet.getName()) !== -1 && 
        ((e.range.columnStart == 2 && e.range.columnEnd == 2 &&
        e.range.rowStart >= 2 && e.range.rowEnd <= 6) ||
        cell.getA1Notation() === OR_AND_CELL)) {
    const OR_AND = activeSheet.getRange(OR_AND_CELL).getValue(); // Retrieve OR or AND
    const rangeReferences = [ // Attach input cells to columns
      { source: "B2", column: "J" },
      { source: "B3", column: "K" },
      { source: "B4", column: "L" },
      { source: "B5", column: "M" },
      { source: "B6", column: "P" }
    ];
    let fcInputList = rangeReferences.map(reference => { // Loop through input cells
      const ref = reference.source;
      const col = reference.column;
      if (activeSheet.getRange(ref).getValue() != "") {
        return col + "='\"&" + ref + "&\"'"; // Build formula: eg. K='"&B3&"'
      } else return '';
    });
    const c2 = fcSheet.getRange('C2');
    if (fcInputList.every(input => input == "")) {
      c2.setValue(' ⭠ set at least one search key');
      fcSheet.getRange(['A8:C8']).clearContent()
    } else {
      fcInputList = fcInputList.filter(Boolean);
      const formula = '=QUERY(resources!B2:P26, "SELECT B,H,I WHERE ' + fcInputList.join(" " + OR_AND + " ") + '")'; // Build rest of formula
      fcSheet.getRange("A8").setFormula(formula);
      c2.clearContent();
    }
  }
}

Example:

Note:

• You will notice I've simplified your code a bit, reducing the amount of repetition. For example rangeReferences is used to specify which cells (B2...B6) are connected to which columns (J,K,L, etc.). This way, you can execute the corresponding actions in a loop. I hope this is useful to you.