使用"or"逻辑使用Apps脚本创建Google Sheets FILTER/SEARCH公式

Question

示例页链接：https://docs.google.com/spreadsheets/d/1Sqt7_Ev9iagbE9CZT1F2lAUJqc1fX4WfbyaOBGEfqyQ/edit?usp=sharing

由于对上一个问题的回答，当关键字输入到资源数据库的输入单元格时，我有应用脚本自动更新谷歌表格中的公式。目前，公式使用“和”逻辑（即结果必须满足所有条件）。

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

是否有一个公式使用多个SEARCH公式作为使用或逻辑（即，至少满足一个条件）的FILTER公式中的条件？

我尝试在条件中添加外括号并使用""，但它返回相同的结果。

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

如果是这样，如何添加什么脚本来返回公式？我希望有一个细胞与数据验证与

// 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();
    }
  }
}

更新的代码试图使用基于C6的单独的simpleAnd和simpleOR函数。

// 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

您可以使用QUERY函数代替，使用其中和逻辑运算符或。例如，如果填满了B2、B3和B5，则在A8中的公式可以是：

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

正如您所看到的，这个公式的一个优点是，您只需要为所有三个输出列调用它一次。

有趣的是，将或替换为和将生成一个使用和运算符的公式，如您所愿。您只需检查相应的单元格（C6）是否具有值和或非，并在构建公式时使用该信息：

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

然后，为了在onEdit函数中动态构建和设置此公式，您可以执行以下操作：

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();
    }
  }
}

• 你会注意到我已经简化了你的代码，减少了重复的数量。例如，用于指定哪些单元格（B2...B6）连接到哪些列（J，K，L等）。这样，您可以在循环中执行相应的操作。我希望这对你有用。