создать формулу ФИЛЬТРА / ПОИСКА в Google Таблицах с помощью скрипта приложений, используя логику «или»

Question

Благодаря ответу на предыдущий вопрос у меня есть скрипт приложений, который автоматически обновляет формулы в Google Таблицах, когда ключевые слова вводятся во входные ячейки для базы данных ресурсов. В настоящее время формулы работают с логикой «и» (т. Е. Результаты должны соответствовать всем условиям).

      =FILTER(resources!C2:C,SEARCH(B2,resources!K2:K),SEARCH(B3,resources!L2:L))

Существует ли формула, использующая несколько формул ПОИСКА в качестве условий в формуле ФИЛЬТРА с использованием логики «или» (т. Е. Выполняется хотя бы одно из условий)?

Я попытался добавить к условиям внешние фигурные скобки и использовать знак «+», но он вернул те же результаты.

      =FILTER(resources!C2:C,(SEARCH(B2,resources!K2:K))+(SEARCH(B3,resources!L2:L)))

Если да, то как можно добавить сценарий для возврата формулы? Я надеюсь, что у меня будет ячейка с проверкой данных для И и ИЛИ, которая будет использоваться в качестве ссылки для оператора IF, чтобы определить, какие конечные формулы установлены на листе. Это текущий рабочий код, в котором используется логика «и».

Answer 1

Формула - используйте QUERY:

Вместо этого вы можете использовать функцию QUERY с <tcode id="53632735"></tcode> и логический оператор or. Например, если B2, B3 и B5 были заполнены, ваша формула в A8 может быть:

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

Как видите, эта формула хороша тем, что вам нужно вызвать ее только один раз для всех трех выходных столбцов.

Фрагмент кода:

Затем, чтобы динамически построить и установить эту формулу в вашем onEdit функции, вы можете сделать следующее:

      function onEdit(e) {
  const 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;
    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 ") + '")'; // Build rest of formula
      fcSheet.getRange("A8").setFormula(formula);
      c2.clearContent();
    }
  }
}

Пример:

Примечание:

• Вы заметите, что я немного упростил ваш код, уменьшив количество повторений. Например rangeReferences используется, чтобы указать, какие ячейки ( B2...B6) связаны с какими столбцами ( J, K, L, так далее.). Таким образом, вы можете выполнять соответствующие действия в цикле. Надеюсь, это будет вам полезно.