Google QUERY — Пишем запросы с использованием заголовков полей

Потребовалось мне для произвольной таблицы, загружаемой в гугловскую таблицу с внешнего ресурса, написать запрос. И всё было хорошо до тех пор, пока в таблице администраторами стороннего ресурса не стали меняться местами поля...

Решение напрашивалось простое - написать запрос с использованием названий полей, а не их положения в таблице. Но стандартная функция Google QUERY названия полей в данных не поддерживает. Перерытие интернета, всевозможных манов - готового решения не дали, и пришлось изобретать своё.

Самое адекватное решение, которое относительно сразу просилось в голову - это использование некой функции-прокладки, которая будет на лету перехватывать написанный запрос с использованием заголовков и переделывать его уже в запрос, который будет восприниматься функцией QUERY.

Виделось это мне как-то так: =QUERY(Q(диапазон; запрос))

И всё бы хорошо, на так заставить работать конструкцию не представлялось возможным, т.к. все перепробованные способы передать обратно параметры в таком виде, чтоб их корректно восприняла функция QUERY - не прокатывали.

Были ещё варианты.

Например можно было использовать конструкцию вида:
=QUERY(диапазон; Q(диапазон; запрос))

но мне лично такое использовать не хотелось, т.к. код получался некрасивым.

В итоге было найдено решение, которое мне понравилось - и хочу им с вами поделиться. Возможно кому-то ещё это окажется полезным.

В Google.Docs есть такая штука, как именованные функции: https://support.google.com/docs/answer/12504534

Через именованные функции я сделал свою:M_QUERY(диапазон, заголовки, запрос)которую описал как:=QUERY({диапазон}; Q(заголовки; запрос))

а в Apps Script описал функцию Q, которая и обрабатывает передаваемые запросы:

function Q(table, query){
  if (table[0].map){
    table = table[0];
    for(var key in table){
      query = query.replaceAll('`'+table[key]+'`', 'Col'+(1+Number(key)));
    }
  }
  return query;
}

Собственно все подготовительные операции проведены и можно в таблицах гугл использовать созданную функцию M_QUERY

Чтоб проверить как всё это работает вживую - открываем таблицу в Google.Docs:

• в Apps Script добавляем функцию Q из примера выше

• создаём именованную функцию M_QUERY (как создавать именованные функции подробно показано по ссылке в начале статьи)

• на листе "Лист 1" делаем таблицу, как показано ниже:

где первая строка содержит наименования полей

- в ячейке А1 листа "Лист 2" пишем формулу:=M_QUERY( 'Лист 1'!1:1000; 'Лист 1'!1:1; "select `Id`, `LastName`,`FirstName` where `FirstName` like '%Иванов%'")

Профит!

И в завершение.

Кому такой метод использования кажется чересчур мудрёным - вполне можно пользоваться просто вариантом:
=QUERY({диапазон}; Q(заголовки; запрос))