Гугл-таблицы — мой любимый инструмент. Люблю нагородить в них многоэтажных формул, что всё само считалось и откуда-нибудь подтягивалось. Недавно выяснила, что городить далеко не всегда нужно: есть волшебная функция Query, которая заменяет собой многие ВПРы с ГПРами. Расскажу на примере реальной задачи, как я научилась работать с Query и ускорила работу с гугл-таблицами.
Задачка про мероприятия
В нашем офисе проходят разные мероприятия, данные по ним собирают разные люди в разные таблицы. Где-то это просто список участников, где-то выгрузка с Таймпада. Сделала две таких таблицы с придуманными посетителями для примера, чтобы не светить персональные данные: одна про курс по QA, вторая про лекцию по гугл-таблицам.
Хочу видеть данные по этим событиям в одном месте, а руками копировать — не хочу! Когда мероприятий будет не два, а двадцать, можно будет полностью устать, пусть машина всё делает за меня.
Подготовка
Что я сделала, прежде чем приступить к работе:
1. Прочитала статью «Всемогущая функция Query». Потыкала в примеры, поняла примерно половину.
2. Освежила знания про функцию IMPORTRANGE. Она простая.
3. Скопировала ключи таблиц с фейковыми посетителями в одно место. Ключ — это кусочек ссылки.
Что делает Query
Справка по гугл-таблицам говорит, что Query «выполняет запросы на базе языка запросов API визуализации Google».
И она правда запрашивает данные из указанной таблицы, плюс немножко их обрабатывает и фильтрует, если надо. Собственно, «query» и переводится с английского как «запрос».
Язык запросов основан на SQL и довольно прост, но всё равно это уже немножко программирование! :)
Решение задачи
Я создала новый файл, открыла чистый лист и написала там несколько строк кода:
=query({importrange("1Kznknv0fncm1xT0YYxPInbLjdV8rnL1BC6Nsqg2vD5I"; "Лист1!A1:X100");
importrange('Список мероприятий'!C3; "Лист1!A2:X100")};"
select Col17, Col18, Col1, Col19, Col10
where Col17!=''
order by Col17 asc")
И всё! Правда, на «открыла и написала» у меня ушло на не меньше часа — разбиралась, что как устроено. Вы тоже можете посмотреть: вот таблица, в которой я работала. Скопируйте её на свой диск, если хотите посмотреть формулы.
Разберемся с каждой строчкой.
importrange()
Эта функция вытаскивает данные из других гугл-таблиц. Фигурные скобки помогают «склеить» несколько таблиц (они для этого должны быть одинаковыми по структуре)
"1Kznknv0fncm1xT0YYxPInbLjdV8rnL1BC6Nsqg2vD5I"; "Лист1!A1:X100"
Здесь скопирован ключ первой таблицы с фальшивыми пользователями — кусочек из ссылки, и указан диапазон ячеек.
importrange('Список мероприятий'!C3; "Лист1!A2:X100")
Не обязательно копировать сам ключ — можно взять из какой-нибудь ячейки, в которую вы его заранее положили.
where Col17!=''"
Прошу не показывать пустые строки.
order by Col17 asc
Указываю, по какому столбцу надо всё это отсортировать. Параметр Asc сортирует по возрастанию — в нашем случае по алфавиту, от А до Я, если бы надо было в обратном порядке — надо было бы поставить «desc».
Кажется, это самое простое применение для Query. Впереди увлекательные приключения! Спасибо Юле Жуковой, которая рассказала про чудо-функцию (и тоже теперь использует повсеместно).
Это немножко программирование
«Сходи вот в эту таблицу, возьми такие-то столбцы и покажи непустые строки». Говоришь машине, что делать — и она делает. Каждый раз удивительно, когда всё наконец-то начинает работать так, как задумано!
Подобные задачки помогают не только ускорять работу, но и хотя бы ненадолго снять с себя ярлык гуманитария.
Дверь в мир разработки всё еще открыта. Всегда можно бросить вот это всё и пойти в программистки :)
***
Собственно, если вам тоже нравится городить формулы в таблицах и вы всерьёз подумываете о профессии программиста — приходите в нашу бизнес-школу на любой базовый курc от айтишников Good Line. Там научат)