Что такое query параметры

Пишем документацию API при помощи RAML

Удобство работы с любым API во многом зависит от того, как написана и оформлена его документация. Cейчас мы ведём работу по стандартизации и унификации описания всех наших API, и вопросы документирования для нас особенно актуальны.
После долгих поисков мы решили оформлять документацию в формате RAML. Так называется специализированный язык для описания REST API. О его возможностях и преимуществах мы расскажем в этой статье.

Почему RAML

Как нужно документировать API? Вопрос этот не так прост, как может показаться на первый взгляд.
Первый и самый простой вариант, который приходит на ум — представить описание к API в виде обычного текстового документа. Так делают очень многие (в том числе и очень известные компании). Мы тоже не раз пользовались этим способом. При всей своей простоте он обладает следующими недостатками:

На описанные выше трудности обращали внимание многие пользователи популярного инструмента Swagger. Вскоре разработчики Swagger решили упростить работу по написанию спецификаций и создали фирменный редактор с поддержкой формата YAML.

Конечно, YAML гораздо удобнее, чем JSON. Но и его использование сопряжено с определёнными трудностями. Дело в том, что в описаниях API всегда имеются повторяющиеся элементы (например, схема ответа, которая может быть одинаковой для разных типов HTTP-запросов), которые приходится всякий раз прописывать вручную. Вот если бы можно их было раз и навсегда прописать в отдельном файле и ссылаться на него в случае небходимости… Но, увы, пока что такой возможности нет.

Что касается формата Markdown (он используется, например, в API BluePrint), то предназначен в первую очередь для оформления текста, а не для использования в качестве основы для генерирования. Приспособить его под документирование API очень сложно. По этой же причине не привели к каким-либо заметным результатам попытки cоздать формат описания API на базе XML — например, язык WADL (Web Application Desription Language), разработанный компанией Sun Microsystems ещё в 2009 году, но так и не получивший широкого распространения.

Создатели проекта RAML (эта аббревиатура означает RESTful API Modeling Language — язык для моделирования REST API ) предприняли попытку разработать язык, предназначенный исключительно для описания API и исправить недочёты, свойственные другим форматам. Первая версия спецификации RAML была опубликована в 2013 году. Основным разработчиком RAML является компания MuleSoft; в проекте также принимают участие представители таких известных компаний, как Cisco, PayPal, ВoxInc. и других.

Краткое введение в RAML

Структура документа

Файл спецификаций на RAML состоит из следующих структурных элементов:

Вводная часть

Каждый документ на RAML начинается с вводной части, которая включает четыре обязательных элемента:

Вводная часть может также включать различную дополнительную информацию — например, сведения об используемом протоколе для связи с API:

Можно во вводной части прописать и метаданные файла документации:

Схемы безопасности

Чтобы начать работать с любым API, нужно пройти процедуру авторизации. Она может осуществляться разными способами: через OAuth, с помощью токенов, либо посредством простой HTTP-аутентификации. Для описания этой процедуры в RAML используются схемы безопасности (security schemes).
Рассмотрим в качестве примера, как описывается авторизация с использованием протокола OAuth2:

Приведённый фрагмент содержит следующую информацию:

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

Почитать более подробно о схемах безопасности и ознакомиться с конкретными примерами можно здесь(раздел Security).

Объекты и методы

Далее перечисляются основные объекты и пути к ним, а также HTTP-методы, которые используются с этими объектами:

В приведённом примере описывается API, с помощью которого можно работать с документами. Мы можем скачивать документы на локальную машину (GET), изменять cуществующие документы (PUT) и загружать новые (POST). С каждым отдельным документом () мы можем также выполнять следующие операции: загрузка на локальную машину (GET) и удаление (DELETE).
HTTP-заголовки, используемые с тем или иным методом, описываются при помощи свойства headers, например:

Обратите внимание на свойство required: оно указывает, является ли заголовок обязательным (true) или необязательным (false).
В описании объектов и методов могут использоваться многочисленные дополнительные параметры. Рассмотрим следующий пример:

Здесь мы указываем, что каждый из документов, доступ к которым можно получить через API, имеет собственный идентификационный код в виде строки (type: string), а также описываем формат этого кода с помощью регулярных выражений.

Свойства description, type и pattern можно использовать и в описаниях методов, например:

В описании метода POST мы указываем параметры, которые нужно передать в теле запроса для добавления нового документа: ID, имя и имя автора. Каждый из этих параметров является строкой (type: string). Обратите внимание на свойство required: оно указывает, является ли параметр обязательным (true) или необязательным (false).

Для каждого метода можно прописать индивидуальную схему безопасности:

Query-параметры

Для каждого метода в документации можно указать query-параметры, которые будут использоваться в запросе. Для каждого query-параметра указываются следующие характеристики: имя, тип, описание и пример:

Профили

Чтобы избежать лишних повторений в описаниях, в RAML используются профили (traits), которые прописываются во вводной части документа:

В дальнейшем к профилю можно обращаться при описании любых методов:

Более подробно о профилях и особенностях их использования можно почитать в официальной документации (раздел Traits).

Описание ответа

В описании ответа обязательно указывается его код. Также в описание можно добавить схему (schema) — перечисление входящих в ответ параметров и их типов. Можно также привести пример конкретного ответа (example).

Визуализация и генерация документации

RAML2HTML и другие конвертеры

Несмотря на то, что RAML — формат относительно новый, для него уже разработано достаточное количество конвертеров и парсеров. В качестве примера можно привести ram2htmtl, генерирующий на основе описания в формате RAML статическую HTML-страницу.
Устанавливается он при помощи менеджера пакетов npm:

Чтобы сконвертировать RAML-файл в HTML, достаточно выполнить простую команду:

Поддерживается возможность создания собственных шаблонов для HTML-файлов (подробнее об этом см. в документации на GitHub по ссылке выше).
Из других конвертеров рекомендуем также обратить внимание на RAML2Wiki и RAML2Swagger.

API Designer

Компания Mulesoft (один из активных участников проекта RAML) создала специальный онлайн-инструмент, с помощью которого можно упростить работу по написанию документации и последующему тестированию API. Называется он API Designer.
Чтобы начать им пользоваться, нужно сначала зарегистрироваться на сайте. После этого можно приступать к работе. API designer предоставляет, во-первых, удобный интерактивный редактор для написания документации онлайн, а во-вторых — платформу для тестирования.
Выглядит всё это так:

В правой части страницы автоматически генерируется интерактивная документация. Здесь же можно и протестировать API: для этого достаточно просто развернуть описание нужного запроса и нажать на кнопку Try it.

API Designer позволяет также загружать RAML-файлы с локальной машины. Поддерживается импорт файлов описаний API для Swagger.
Кроме того, API Designer хранит статистику тестовыx запросов к API.

API console

API console — полезный инструмент, разработанный всё той же компанией MuleSoft. С его помощью можно прямо в браузере генерировать документацию к API. Файлы спецификаций можно как загрузить с локальной машины, так и указать ссылку на внешний источник:

В состав API Console входит несколько файлов-образцов, представляющих собой описания API популярных веб-сервисов: Twitter, Instagram, Box.Com, LinkedIn. Мы попробовали сгенерировать на основе одного из низ документацию — выглядит вполне симпатично:

Документация, получаемая на выходе, является интерактивной: в ней можно не только прочитать описание API, но и выполнить тестовые запросы.

Заключение

В этой статье мы рассмотрели основные возможности RAML. Его несомненными преимуществами являются простота и логичность. Надеемся, что в скором будущем RAML получить ещё более широкое распространение и займёт достойное место в ряду инструментов для документирования API.
Если у вас есть вопросы — добро пожаловать в комментарии. Будем также рады, если вы поделитесь собственным опытом использования RAML на практике.

Если вы по тем или иным причинам не может оставлять комментарии здесь — добро пожаловать в наш блог.

Источник

Шаг 3 «Параметры (Описание API)»

Параметрами являются опции, которые можно передать конечной точке (например, указать формат ответа или возвращаемую сумму), чтобы повлиять на ответ. Существует четыре типа параметров:

Различные типы параметров часто документируются в отдельных группах на одной странице. Не все конечные точки могут содержать каждый тип параметра.

Примеры параметров

Скриншот ниже является примером раздела параметров с Box API:

Пример параметра BOX API

В этом примере параметры сгруппированы по типу:

Конечная точка также выделяет параметр path collab_id распознаваемым образом в определении конечной точки.

Часто параметры просто перечислены в таблице или списке определений, как в этом примере:

Вот пример документации API Yelp

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

Четыре типа параметров

REST API обладают 4 типами параметров:

Что следует отметить в документировании параметров

Независимо от типа параметра, определите следующее для каждого параметра:

Типы данных параметров

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

Вот типы данных наиболее распространенные в REST API:

Максимальное и минимальное значения параметров

Помимо указания типа данных, параметры должны указывать максимальные, минимальные и допустимые значения. Например, если API сервиса погоды допускает только координаты долготы и широты конкретных стран, эти ограничения должны быть описаны в документации в разделе параметров.

Параметры заголовка

Параметры заголовка включаются в заголовок запроса. Обычно заголовок включает в себя только параметры авторизации, которые являются общими для всех конечных точек; в результате параметры заголовка обычно не документируются для каждой конечной точке. Детали авторизации в параметрах заголовка документированы в разделе Аутентификация и авторизация.

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

Параметры path

Параметры path являются частью конечной точки. Например, в следующей конечной точке и являются обязательными параметрами path:

Параметры path обычно устанавливаются с помощью фигурных скобок. Но в некоторых API документациях стили прописывают перед значением двоеточие или используют вообще иной синтаксис. При документировании параметров path указываются значения по умолчанию, допустимые значения и другие сведения.

Цветовая кодировка параметра path

При перечислении параметров path в конечной точке, может помочь цветовое кодирование параметров, для их легкой идентификации. Цветовое выделение параметров дает понять, что является параметром path, а что нет. Посредством цвета мы создаем непосредственную связь между конечной точкой и определениями параметров.

Например, если выделить цветом параметры и в конечной точке:

То позже можно использовать этот же цвет при описании этих же параметров.

Использование цвета для выделения параметров позволяет легко выделить определяемый параметр и его связь с определением конечной точки.

Параметры строки запроса

Эта строка запроса:

вернут один и тот же результат.

Однако в параметрах path порядок имеет значение. Если параметр является частью фактической конечной точки (не добавляется после строки запроса), это значение обычно описывается в описании самой конечной точки.

Параметры тела запроса

Часто с запросами POST (где мы что-то создаем) мы отправляем объект JSON в теле запроса. Этот параметр и есть тело запроса. Обычно форматом тела запроса является JSON. Этот JSON объект может быть длинным списком пар ключ-значение с несколькими уровнями вложенности.

Например, конечной точкой может быть что-то простое, например /surfreport/ . Но в тело запроса мы можем включить объект JSON со многими парами ключ-значение, например:

Документирование параметров тела сложного запроса

Документирование данных JSON (как в параметрах тела запроса, так и в ответах) является одной из самых сложных частей документации API. Документирование JSON объекта будет легким, если этот объект прост, с несколькими парами ключ-значение на одном уровне. Но если у нас есть JSON объект с несколькими объектами внутри объектов, множественными уровнями вложенности и большими объемными данными, это может быть сложно. И если объект JSON занимает более 100 строк, или 1000, нам необходимо тщательно продумать, как представить информацию.

Таблицы хороши для документирования JSON, но в них трудно различать элементы верхнего уровня и подуровня. Объект, который содержит объект, который также содержит объект, и другой объект и т. Д., Может сбивать с толку.

Безусловно, если объект JSON относительно мал, таблица, вероятно, является лучшим вариантом. Но есть и другие дизайнерские подходы.

Взгляните на ресурс eBay findItemsByProduct. Вот параметр тела запроса (в данном случае формат XML):

Ниже параметра тела запроса находится таблица, которая описывает каждый параметр:

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

Подход Swagger к параметрам тела запроса

Пользовательский интерфейс Swagger, который мы рассмотрим позже, а также его демо, предоставляет другой подход к документированию параметра тела запроса. Swagger UI показывает параметры тела запроса в формате, который вы видите ниже. Интерфейс Swagger позволяет переключаться между представлением «Пример значения» и представлением «Модель» для ответов и параметров тела запроса.

Посмотрим на Swagger Petstore для изучения. “Пример значения” показывает образец синтаксиса вместе с примерами. При нажатии на ссылку “Модель””, видим пример параметра тела запроса и описания каждого элемента.

Модель включает в себя переключатели «развернуть / свернуть» со значениями. (Демо Petstore не имеет множество описаний параметров в модели, но если включить описания, они будут отображаться в модели, а не в примере значения.)

Можно заметить, что существует множество вариантов документирования JSON и XML в параметрах тела запроса. Правильного способа документировать информацию нет. Как всегда, выбираем метод, который отображает параметры нашего API наиболее простым и легким для чтения способом.

Если у нас относительно простые параметры, наш выбор не будет иметь большого значения. Но если сложные, громоздкие параметры, то, возможно, придется прибегнуть к пользовательским стилям и шаблонам, чтобы представить их более четко. Исследуем эту тему более подробно в разделе Пример и схема ответа.

Параметры конечной точки SurfReport

Давайте посмотрим доступные параметры и создадим таблицу с описанием параметров для нашего нового ресурса surfreport. Вот пример, как может выглядеть информация о параметрах:

Параметры

Параметры path

Параметры строки запроса

Следующие шаги

После того, как мы задокументировали параметры пора посмотреть на Пример запроса к ресурсу

Источник

Всемогущая функция Query — подробное руководство

Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач.

Я считаю Query одной из наиболее полезных функций Google Таблиц. Но в справке Google она описывается очень поверхностно, и вся мощь данной функции не раскрыта. При более детальном знакомстве становится ясно, что она способна заменить большую часть существующих функций.

Знания возможностей функции Query помогают в построении аналитических инструментов для бизнеса. Если хотите сэкономить своё время, мы можем построить аналитический инструмент для вас:

Для работы с QUERY вам понадобятся базовые знания SQL. Для тех, кто не в курсе: пугаться не надо, функция QUERY на самом деле поддерживает самые простые возможности SQL.

Синтаксис QUERY

Для максимального восприятия дальнейшей информации предлагаю открыть и скопировать себе следующую Google Таблицу. Для того, чтобы создать копию, воспользуйтесь меню «Файл» и выберите в нем пункт «Создать копию».

В Google Docs, копию которого вы только что создали, существует несколько листов. Лист DB — это база данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.

План SQL-запроса в функции Query

Любой SQL-запрос состоит из отдельных блоков, которые часто называют кляузами. В SQL для функции Query заложен синтаксис языка запросов API визуализации Google, который поддерживает следующие кляузы:

Hello World для функции Query (Select)

Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.

Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100» содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100» мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.

Используем фильтры и сортировку (Where, Order by)

Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order. Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select.

Обращение к полям базы данных осуществляется через названия столбцов рабочего листа, на котором располагается база данных.

В нашем случае данные, расположенные на листе DB, и обращение к определенным полям прописываются как название столбцов листа. Таким образом, нужные поля располагается в следующих столбцах:

Соответственно, часть запроса, отвечающая за перечень выводимых в результате данных, будет выглядеть так:

Далее в запросе идет кляуза Where. При написании запроса кляузы обязательно должны располагаться в таком порядке, в котором были описаны в первом разделе этой статьи. После объявления Where нам необходимо перечислить условия фильтрации.

В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date.

Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

Мы разбили с помощью скобок фильтрацию данных на две логических части: первая фильтрует по датам, вторая — по названию кампании. На данном этапе формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных, выглядит так:

Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат:

Помимо обычных логических операторов (=, ) блок WHERE поддерживает дополнительные операторы фильтрации:

Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by. По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:

Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:

Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Переходим на лист Level_3 и усложняем задачу. В запросах вы можете не только делать выборки, но также проводить различные вычисления и агрегации данных. Для этого в SQL функции Query существует ряд агрегирующих функций и кляуза Group by. Агрегирующие функции:

Итак, давайте посчитаем данные по каждой кампании:

Для решения этой задачи нам понадобятся данные только из двух полей: Campaign (находится в столбце B) и Sessions (находится в столбце G). Все агрегирующие функции прописываются вместе со списком полей для вывода данных в кляузе Select. В случае применения агрегирующих функций все поля, к которым не применяется этот тип функций, являются группирующими полями. Их необходимо перечислить в кляузе Group by. Агрегирующие функции работают обязательно в паре с Group by. Описание кляузы Select будет следующим:

Далее необходимо сгруппировать данные: в нашем случае требуется группировка только по одному полю Campaign, но вы можете осуществлять группировку по любому количеству столбцов.

Описание кляузы Group by очень простое:

В кляузе достаточно указать только столбец B, содержащий информацию о названии кампании. Потому что он единственный, к которому мы не применили никакой агрегирующей функции. Наша формула:

Получим следующий результат:

В принципе, мы получили желаемый результат, но названия столбцов можно подкорректировать с помощью кляузы Label. Результат будет лучше отображаться, если мы отсортируем отчет по названию кампании. Описание кляузы Order by мы уже рассмотрели выше.

Для нужной сортировки достаточно добавить следующую строку после описания:

Чтобы переименовать столбцы в таблице, которую возвращает запрос, необходимо добавить описание кляузы Label. Синтаксис достаточно прост: сначала указываете выводимый столбец или функцию, которая будет возвращать значения в результирующую таблицу, и далее в одинарных кавычках указываете нужное название.

Это будет выглядеть так:

А результат, возвращаемый формулой, выглядит так:

Все поля названы соответствующим описанию кляузы Label образом. Последнее, что режет глаз в возвращаемой таблице, — формат, в котором выводятся данные в столбце «Среднее». Для корректировки форматов, выводимых запросом данных, требуется описать кляузу Format. Ее описание схоже с описанием Label, но вместо названия поля следует прописать маску вывода данных (также в одинарных кавычках).

Округлим числа в столбце «Среднее» до двух знаков после запятой. Для округления выводимых данных до двух знаков после запятой маска должна выглядеть как ‘0.00’.

Описание кляузы Format

Соответственно, окончательная формула на листе Level_3 выглядит так:

Создание перекрестных таблиц (Pivot, скалярные функции)

Чтобы за считанные секунды с помощью функции QUERY создать перекрестную таблицу, следует добавить в запрос описание кляузы Pivot. Построим отчет, в котором в строках будет номер дня недели, в столбцах ˜— тип устройства, а в качестве выводимых значений рассчитаем показатель отказов. Если вы внимательно изучили структуру базы данных, находящейся на листе DB, то наверняка заметили, что у нас нет поля, содержащего информацию о дне недели, как и поля, содержащего информацию о показателе отказов.

Чтобы вычислить день недели, придется воспользоваться одной из множества скалярных функций. В нашей базе есть вся необходимая информация для расчета показателя отказов. Дальше достаточно просто применить арифметический оператор «Деление».

Скалярные функции

На момент написания статьи SQL в Google Таблицах поддерживает 14 скалярных функций.

Арифметические операторы

Для решения нашей задачи потребуется использовать скалярную функцию dayOfWeek для вычисления дня недели, а также арифметический оператор «/» для подсчета показателя отказов.

Давайте определим поля, которые будем использовать в запросе:

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

Именно так будет выглядеть описание нужных нам полей. Теперь с помощью кляузы Group by сгруппируем строки по дням недели. Для этого допишем в запрос следующую строку:

Уже видно, как меняется показатель отказов в зависимости от дня недели:

Чтобы построить перекрестную таблицу, достаточно прописать кляузу Pivot с указанием столбца, значения из которого будут добавлены в виде столбцов. В нашем случае это столбец E, потому что именно он содержит информацию о типе устройств. Дописываем строку:

Теперь запрос возвращает результат:

Нам остается только добавить последние штрихи: изменить названия столбцов и формат чисел с помощью пунктов LABEL и FORMAT.

Окончательная формула на листе Level_4:

Строкой LABEL sum(H)/sum(G) ‘’ мы убрали из подписей столбца надпись, содержащую формулу расчета. Строкой FORMAT sum(H)/sum(G) ‘0.00%’ мы передали процентный формат показателю отказов в отчете. В целом, описанного в примерах выше синтаксиса вполне достаточно, чтобы начать активно использовать функцию QUERY, но в завершении статьи хочу показать еще несколько интересных приемов, которые можно взять на вооружение.

Импорт данных с помощью QUERY из другой Google Таблицы

С помощью QUERY вы можете использовать в качестве базы данных другую Google Таблицу. Это можно сделать с помощью сочетания функций ImportRange и QUERY. Я создал новую Google Таблицу, в которую продублировал данные с листа DB из приведенного в начале статьи документа. Чтобы в качестве базы данных использовать данные из другой Google таблицы, в качестве первого аргумента функции Query выступит импортируемый функцией ImportRange диапазон.

Разница в том, что при написании запроса к данным, импортируемым функцией ImportRange, вместо названия столбцов мы указываем их порядковый номер в возвращаемом функцией ImportRange диапазоне. На листе DataImport перепишем запрос, представленный в Level_4 таким образом, чтобы он обращался к данным, находящимся в новой таблице на листе DB_Transfer. Синтаксис функции ImportRange достаточно прост:

Где ключ — часть URL Google Таблицы:

А диапазон — это ссылка на лист и (простите за каламбур) диапазон. В нашем случае диапазоном будет DB_Transfer!A1:L1143. Формула ImportRange:

Именно ее мы должны указать в качестве данных функции Query. Далее остается переписать запрос так, чтобы ссылаться на столбцы базы данных не по названию, а по порядковому номеру столбца. Определим, к каким столбцам мы обращались с помощью запроса на листе Level_4.

Текст запроса после замены названий столбцов на их порядковый номер:

Как видите, текст запроса практически не изменился, но вместо столбца A мы теперь указываем Col1, вместо столбца E — Col5, вместо G — Col7 и вместо H, соответственно, Col8. Получаем формулу:

В качестве первого аргумента функции Query выступает функция ImportRange с ссылкой на ключ нужной Google таблицы, которую вы можете скопировать из URL Google Таблицы, и ссылки на диапазон, включающий название листа, а также первой и последней ячейки нужного диапазона.

Номера столбцов в запросе идут не со столбца A, а с того, который является первым в указанном вами диапазоне в функции ImportRange. Например, если бы в качестве импортируемого диапазона выступал DB_Transfer!C1:L1143, то данные из столбца C запрашивались ссылкой Col1, поскольку в импортируемом массиве этот столбец — первый.

Окончательную формулу в работе вы можете посмотреть на листе DataImport.

Строим запрос на основе объединения данных из нескольких таблиц с одинаковой структурой

Ещё одна довольно мощная возможность функции QUERY — построение запрос на основе нескольких массивов данных.

Единственное условие для объединения данных — одинаковая структура входящих таблиц.

Принцип объединения входящих данных заключается в том, что первый аргумент функции QUERY на вход может принимать либо ссылку на диапазон либо описание массива.

Массив — это виртуальная таблица, которая содержит строки и столбцы.

Массив всегда описывается внутри фигурных скобок, при этом необходимо соблюдать следующую пунктуацию:

Таким образом вы можете два и более диапазона описать в одном массиве, например:

В данном случае мы обращаемся с запросом к трём диапазонам данных, находящимся на разных листах, объединив их с помощью «;» в массив так, что вторая таблица становится продолжением первой, а третья таблица — продолжением второй.

Посмотрите этот пример по ссылке.

Запрос с динамическими параметрами

Синтаксис запросов в функции QUERY сложен для неподготовленного пользователя. Поэтому вы можете добавить на рабочий лист различные интерактивные элементы в виде выпадающего списка, созданного с помощью функции «Проверка данных».

А в тексте запроса — делать ссылки на ячейки, содержащие нужные данные. Например, мы можем динамически задать диапазон дат, который хотим вывести в динамическую таблицу, либо сделать возможность динамически добавлять и убирать различные поля результирующей таблицы. Посмотреть, как это выглядит наглядно, вы можете на листе DinamicQuery.

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

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

Укажите интересующий вас интервал дат в пределах от 24.09.2015 по 25.10.2015, поскольку данные, сгенерированные для тестовой базы и хранящиеся на листе DB, содержат только этот диапазон.

Далее в конструкторе отчетов вы можете изменить название полей и оно будет отображаться в финальной таблице. Также можете указать, какие поля требуется вывести в отчет. Еще раз напомню, что необходимо указать как минимум одну меру и одно измерение.

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

Формула, которая изменяет запрос в зависимости от настроенных параметров, выглядит так:

На самом деле функция Query может разрастаться до размеров программы и динамически изменять выводимую информацию в зависимости от указанных на рабочем листе данных.

Надеюсь, у меня получилось объяснить, как пользоваться одной из наиболее сложных и в тоже время полезных функций Google Таблиц. Если хотите познакомиться со всеми возможностями Google Таблиц, рекомендую обратить внимание на курс «Google Sheets» от Choice31: он будет полезен проджектам, маркетологам, финансовым аналитикам и всем, кто давно собирался разобраться в инструменте.

Готов отвечать на вопросы в комментариях 🙂

Источник