среда, 13 мая 2020 г.

Google таблицы. Функция QUERY

QUERY

Синтаксис

QUERY(данные; запрос; [заголовки])

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

Каждый столбец данных может содержать только логические, числовые (включая типы дата/время) или строковые значения.
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.

запрос – запрос на выполнение, записанный на языке запросов API визуализации Google.

Значение параметра запрос должно быть заключено в кавычки или представлять собой ссылку на ячейку, содержащую соответствующий текст.
На странице https://developers.google.com/chart/interactive/docs/querylanguage можно получить более подробную информацию о языке запросов.

заголовки – [ НЕОБЯЗАТЕЛЬНО ] – количество заголовочных строк в верхней части раздела данных. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных.

справка по функции (https://support.google.com/docs/answer/3093343?hl=ru)

С диапазоном ячеек все понятно. Перейдем к запросу.

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

пункт	Применение
select	Выбирает, какие столбцы возвращать и в каком порядке. Если опущено, возвращаются все столбцы таблицы в порядке по умолчанию.
where	Возвращает только те строки, которые соответствуют условию. Если опущено, возвращаются все строки.
group by	Агрегирует значения по строкам.
pivot	Преобразует отдельные значения в столбцах в новые столбцы.
order by	Сортирует строки по значениям в столбцах.
limit	Ограничивает количество возвращаемых строк.
offset	Пропускает указанное количество первых строк.
label	Устанавливает метки столбцов.
format	Форматирует значения в определенных столбцах, используя заданные шаблоны форматирования.
options	Устанавливает дополнительные параметры.
from	from Пункт был исключен из языка.

Справка от Google по языку запросов API находится по ссылке: https://developers.google.com/chart/interactive/docs/querylanguage

Начнем с SELECT, введем в ячейку G2 Лист 1 формулу

=QUERY($A$1:$E$49;"select A";1)

мы вывели данные из диапазона $A$1:$E$49 первый столбец - А

Можно вывести все столбцы

=QUERY($A$1:$E$49;"select *";1)

Можно ограничить количество строк

=QUERY($A$1:$E$49;"select * limit 5";1)

Google таблицы. Объединение нескольких листов Google (рабочих книг) в файл основных данных

Исходные данные:

Есть два файла Google таблиц (Точка 1, Точка 2). Структура данных в таблицах одинаковая, разные значения.

Таблица Точка 1

Таблица Точка 2

Для анализа сводных данных из таблиц (Точка 1, Точка 2), создадим новую Google таблицу и назовем ее итоговая таблица

Сформируем сводные данные, для этого воспользуемся функцией

IMPORTRANGE

Синтаксис

IMPORTRANGE(ключ_таблицы; диапазон)

ключ_таблицы – URL таблицы, из которой импортируются данные.

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

диапазон – строка в формате "[название_листа!]диапазон" (например, "Лист1!A2:B6" или "A2:B6"). Параметр определяет диапазон, который нужно импортировать.

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

справка по функции (https://support.google.com/docs/answer/3093340?hl=ru)

Импортируем заголовок из таблицы Точка 1, в ячейку A1 Листа 1 введем формулу

=IMPORTRANGE("1MoMimtMoxhSE3OXn4LzA6BpVIb_WiznOldsFvvraZkQ";"Лист1!a1:e1")

Как мы видим, чтобы получить ключ_таблицы, переходим в строку с адресом нашей страницы

Ключ находится между слешем (/) после имени раздела документа (d) и следующем слешем (/). Выделяем и копируем в нашу функцию.

=IMPORTRANGE("1MoMimtMoxhSE3OXn4LzA6BpVIb_WiznOldsFvvraZkQ";

Далее(следующий параметр функции) указываем имя листа и диапазон для импорта

"Лист1!a1:e1")

Все вместе

=IMPORTRANGE("1MoMimtMoxhSE3OXn4LzA6BpVIb_WiznOldsFvvraZkQ";"Лист1!a1:e1")

При первом импорте возможно будет запрос на доступ к таблице Точка 1.

Для импорта из нескольких google таблиц воспользуемся пользовательсим массивом.

Как работать с массивами в Google Таблицах (справка)

(https://support.google.com/docs/answer/6208276?hl=ru)

Введем в ячейку A2 Лист 1 формулу:

={IMPORTRANGE("1qaIDBtTZ9Jhavv84BTeKT5pfbW0Rl6L_7Zcuhxzhsyw";"Лист1!a2:e25"); IMPORTRANGE("1MoMimtMoxhSE3OXn4LzA6BpVIb_WiznOldsFvvraZkQ";"Лист1!a2:e25")}

Для анализа данных можно воспользоваться функцией

QUERY

Синтаксис

QUERY(данные; запрос; [заголовки])

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

Каждый столбец данных может содержать только логические, числовые (включая типы дата/время) или строковые значения.
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.

запрос – запрос на выполнение, записанный на языке запросов API визуализации Google.

Значение параметра запрос должно быть заключено в кавычки или представлять собой ссылку на ячейку, содержащую соответствующий текст.
На странице https://developers.google.com/chart/interactive/docs/querylanguage можно получить более подробную информацию о языке запросов.

заголовки – [ НЕОБЯЗАТЕЛЬНО ] – количество заголовочных строк в верхней части раздела данных. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных.

справка по функции (https://support.google.com/docs/answer/3093343?hl=ru)

Введем в ячейку A2 Лист 1 формулу

=QUERY({IMPORTRANGE("1MoMimtMoxhSE3OXn4LzA6BpVIb_WiznOldsFvvraZkQ";"Лист1!a2:e25");IMPORTRANGE("1qaIDBtTZ9Jhavv84BTeKT5pfbW0Rl6L_7Zcuhxzhsyw";"Лист1!a2:e25")};"SELECT * ORDER BY Col2")

Второй параметр функции

"SELECT * ORDER BY Col2"

Выводим все графы с сортировкой по 2 столбцу

четверг, 16 апреля 2020 г.

Google sheet (гугл таблицы). Простые триггеры. onOpen

Создадим функцию, чтобы при открытие нашей таблицы - создавалось наше пользовательское меню.

Переходим к работе со скриптами - откроем из основного меню редактор скриптов

Создадим функцию onOpen, для этого добавим нижеприведенный код

function onOpen() {

//--- Вызываем пользовательскую функцию - myInetMenu

myInetMenu();

}

Проверим результат

Дополнительные материалы:

Google sheet (гугл таблицы). Добавляем пользовательское меню

https://www.blogger.com/blogger.g?tab=mj&blogID=7883270052383344055#editor/target=post;postID=3590620841618191787;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

Google Apps Script – Сценарии приложений Google

https://www.blogger.com/blogger.g?tab=rj&blogID=7883270052383344055#editor/target=post;postID=5148176325424190896;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=4;src=postname

gSheet.Вызываем нашу функцию

https://www.blogger.com/blogger.g?tab=rj&blogID=7883270052383344055#editor/target=post;postID=2632549296076487782;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=3;src=postname

Google sheet (гугл таблицы). Добавляем пользовательскую функцию

https://www.blogger.com/blogger.g?tab=rj&blogID=7883270052383344055#editor/target=post;postID=8665288378382703975;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

Пользовательские Функции. Из даты Месяц прописью.date_to_string()

https://www.blogger.com/blogger.g?tab=rj&blogID=7883270052383344055#editor/target=post;postID=2925434793158182919;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=2;src=postname

Google таблицы. Простая работа на примере учета Моих расходов.

https://www.blogger.com/blogger.g?tab=rj&blogID=7883270052383344055#editor/target=post;postID=7109108564318758662;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=1;src=postname

среда, 15 апреля 2020 г.

Google sheet (гугл таблицы). Добавляем пользовательское меню

Переходим к работе со скриптами - откроем из основного меню редактор скриптов

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

function myInitMenu() {

//--- Возвращает экземпляр среды пользовательского интерфейса электронной таблицы, которая позволяет сценарию добавлять такие функции, как меню, диалоговые окна и боковые панели.

//--- Справка - https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=ru#getUi()

var my_ui = SpreadsheetApp.getUi();

//--- Создает меню

//--- Справка - https://developers.google.com/apps-script/reference/base/menu

var my_menu = my_ui.createMenu("Мои функции");

//--- Добавляет элемент в меню

//--- Справка - https://developers.google.com/apps-script/reference/base/menu

my_menu.addItem("Моя функция - Все идет по плану", "myGoToPlan");

//--- Вставляет меню в экземпляр пользовательского интерфейса редактора.

//--- Справка - https://developers.google.com/apps-script/reference/base/menu

my_menu.addToUi();

}