How to specify a dynamic condition from another cell in a QUERY formula?

Question

Google Sheets

Good afternoon! There is a problem with the QUERY function. I display the data using the following formula
=ARRAYFORMULA(IF(A$2:$2<>"";UNIQUE(QUERY(OUTPUT_DATA;"select C where (E='"&A$2:$2&"' and (D<="&RIGHT( $B$1;2)&"))"));""))

there is a selecte, and there are conditions specified by the where clause. I want additional ones to be formed in another cell and loaded into this formula.

For example to where (E='"&A$2:$2&"' and (D<="&RIGHT($B$1;2)& "+ condition (for example "and A="Yandex"") " ")

I'm sure I know that such a possibility exists, but I can not find it on the Internet.I would be grateful for the help.

Answer 1

You can conjure a formula, but a very simple script solves the problem more gracefully

/**
 * @OnlyCurrentDoc
 */

/**
 *
 * The RAINREPORT function
 *
 * @param {rain} rain Two-column data array
 * @param {boolean} skipEmpty
 * @return {any[][]}
 * @customfunction
 */
function RAINREPORT(rain, skipEmpty = true) {
  const _rain_ = rain.map((row) => row[0]);
  const _cloud_ = rain.map((row) => row[1]);
  const _report_ = {};
  _rain_.forEach((item, i) => {
    if (_cloud_[i] === '' && skipEmpty === true) return;
    if (!Object.prototype.hasOwnProperty.call(_report_, _cloud_[i]))
      _report_[_cloud_[i]] = {
        data: [],
        name: _cloud_[i],
      };
    if (_report_[_cloud_[i]].data.indexOf(item) === -1)
      _report_[_cloud_[i]].data.push(item);
  });
  return Object.keys(_report_)
    .sort()
    .map((key) => [key, ..._report_[key].data.sort()]);
}

Formula

=TRANSPOSE(RAINREPORT(
  QUERY(
    ДАННЫЕ_ВЫДАЧИ;
    "select C,E where " & 
      TEXTJOIN(" and ";1;IF(A1="ВСЕГО";"";"A='" & A1 & "'");"D < " & REGEXEXTRACT(B1;"\d+")))
))

Spreadsheet with example https://docs.google.com/spreadsheets/d/11Fp2Tx8BGX...