Google-таблицы

Все возможности взаимодействия бота с гугл таблицами.

В этой статье разберем как боты могут взаимодействовать с гугл таблицами.

Для того, чтобы использовать данный функционал, вам необходимы:

  1. Google таблица, открытая на редактирование по ссылке (рисунок 1-4)

  2. URL функции

  3. Параметры запроса

Подготовка Google таблицы

Рисунок 1
Рисунок 2
Рисунок 3
Рисунок 4

Написание json запроса

Переходим в настройки блока, в котором у нас будет осуществляться запись. Выбираем тип запроса POST-json, в строку URL функции вставляешь нужный нам URL, в поле JSON POST-параметров вставляем параметры (Рисунок 5)

Чтобы увидеть какой ответ приходит на ваш запрос напишите в поле Ответ {custom_answer}, сохраните и пройдите по цепочке. Далее можно каждое значение из ответа сохранить в переменные в поле Сохранение значения из JSON-ответа, используя синтаксис:

параметр_из_запроса -> ваша_переменная

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

  • "cell_number":{"row":4,"col":2} cell_number|row -> Строка; cell_number|col -> Столбец

Теперь разберем подробнее что же все-таки боты умеют делать с гугл таблицами.

Построчная запись в определенные столбцы.

Вы можете собрать множество данных от пользователя и записаь их в первую свободную в таблице строку. Осуществляется с помощью функции mapping.

В таблице должна быть заполнена шапка (хотя бы одна ячейка в первой строке)

URL функции: https://store.salebot.pro/function/gsheets

Параметры запроса {"id": "ид таблицы", "mapping":{"a":"#{переменная}", "b": "#{еще}", "c": "#{еще}", "d":"просто текст"}}

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

{"id": "ид таблицы", "mapping":{"a":"просто текст", "b": "#{переменная}"}, "list_name": "Название листа"}

Где id это id вашей гугл таблицы. Вы можете получить его из ссылки на вашу гугл таблицу (то что выделено жирным в ссылке ниже). https://docs.google.com/spreadsheets/d/1aUbbUaw2SRnJFAavv06Noa1EzumhyShKDm7ie6lYKc4/edit#gid=0

a, b, c, d - это как раз имена столбцов list_name - название вашего листа (например, "Лист2")

Если проблем при выполнении запроса не возникло, то в ответ приходит {"number_row":8}. Вы можете сохранить этот номер строки и использовать для дальнейшей работы.

Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными. Подробнее об этом ниже в пункте Работа через свой аккаунт:

{"id": "ид таблицы", "mapping":{"a":"#{переменная}", "b": "#{еще}", "c": "#{еще}", "d":"просто текст"},"creds_path": "путь к вашему файлу с данными для авторизации"}

Рисунок 5

Запись данных в определенные ячейки

URL функции: https://store.salebot.pro/function/gsheets

Параметры {"id": "ид таблицы", "write":{"a1":"#{переменная}", "b3": "#{еще}", "c1": "#{еще}", "d20":"просто текст"}}. Запись осуществляется в конкретные ячейки, которые вы укажете (в нашем примере a1, b3, c1, d20)

Нумерация колонок идет с единицы.

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

{"id": "ид таблицы", "write":{"a1":"#{переменная}", "b3": "#{еще}", "c1": "#{еще}", "d20":"просто текст"}, "list_name": "Название листа"}

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

Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными. Подробнее об этом ниже в пункте Работа через свой аккаунт:

{"id": "ид таблицы", "write":{"a1":"#{переменная}", "b3": "#{еще}", "c1": "#{еще}", "d20":"просто текст"}, "list_name": "Название листа","creds_path": "путь к вашему файлу с данными для авторизации"}

Рисунок 6

Чтение данных из таблицы

URL функции: https://store.salebot.pro/function/gsheets

{"id": "ид таблицы", "read":{"a1":"a1", "b3": "b4", "c1": "c10", "d20":"a1"}} Параметрами передавайте диапазоны. В примере "a1":"a1" возвращает одну ячейку, а "c1":"c10" 10 значений из колонки c. Возвращаемое значение хеш из адреса ячейки и значения из него.

Нумерация колонок идет с единицы.

Если вы хотите прочитать значения не на первом листе, то надо добавить в запрос параметр list_name:

{"id": "ид таблицы", "read":{"a1":"a3", "b3": "b6"}, "list_name": "Название листа"}

Если проблем при выполнении запроса не возникло, то в ответ приходит название ячейки и ее содержимое:{"A1":"\u043f\u0440\u0438\u0432\u0435\u0442","A2":"\u043f\u0435\u0440\u0435\u043c\u0435\u043d\u043d\u0430\u044f","A3":"\u043f\u0435\u0440\u0435\u043c\u0435\u043d\u043d\u0430\u044f","B3":"\u0444\u0443\u043d\u043a\u0446","B4":"\u0444\u0443\u043d\u043a\u0446","B5":"\u0444\u0443\u043d\u043a\u0446"}

Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными. Подробнее об этом ниже в пункте Работа через свой аккаунт:

{"id": "ид таблицы", "read":{"a1":"a3", "b3": "b6"}, "list_name": "Название листа","creds_path": "путь к вашему файлу с данными для авторизации"}

Рисунок 7

Поиск текста в таблице (вывод первой подходящей строки)

URL функции: https://store.salebot.pro/function/findcell

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

Нумерация строк и колонок идет с единицы.

Существует четыре способа: 1) Поиск по всей таблице и вывод текста из соседней ячейки {"id": "ид таблицы", "find": "текст для поиска"} Ответ: {"status":"1","data":"\u0440\u0430\u0441\u0441\u0432\u0435\u0442","cell_number":{"row":4,"col":1, "col_letter":"A"}} Разбор ответа: data -> Ответ; cell_number|row -> Строка; cell_number|col -> Столбец

2) Поиск по колонке и вывод текста из соседней ячейки. Где col номер колонки для поиска. {"id": "ид таблицы", "find": "текст для поиска", "col": 2} Ответ: {"status":"1","data":"\u043a\u0440\u044b\u0448\u0430","cell_number":{"row":4,"col":2, "col_letter":"B"}} Разбор ответа: data -> Ответ; cell_number|row -> Строка; cell_number|col -> Столбец

3) Поиск по колонке и вывод текста из указанной колонки в той же строке. Где return номер колонки для вывода. {"id": "ид таблицы", "find": "текст для поиска", "col": 2, "return": 4} Ответ: {"status":"1","data":"\u043f\u0435\u0440\u0435\u043c\u0435\u043d\u043d\u0430\u044f","cell_number":{"row":6,"col":2, "col_letter":"B"}} Разбор ответа: data -> Ответ; cell_number|row -> Строка; cell_number|col -> Столбец

4) Поиск по колонке и вывод текста из всей строки. Параметр return необходимо передать со значением 0. {"id": "ид таблицы", "find": "текст для поиска", "col": 2, "return": 0} Ответ: {"status":"1","data":{"0":"\u0441\u043e\u043b\u043d\u0446\u0435","1":"\u0440\u0430\u0441\u0441\u0432\u0435\u0442","2":"\u043a\u0440\u044b\u0448\u0430","3":"","4":"\u043d\u0435\u0431\u043e"},"cell_number":{"row":4,"col":1, "col_letter":"A"}} Разбор ответа: data -> Ответ; data|0 -> Ячейка1; data|1 -> Ячейка2; data|2 -> Ячейка3; data|3 -> Ячейка4; cell_number|row -> Строка; cell_number|col -> Столбец

Если вы хотите прочитать значения не на первом листе, то надо добавить в запрос параметр list_name: {"id": "ид таблицы", "find": "текст для поиска", "col": 2, "return": 4, "list_name": "Название листа"}

Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными. Подробнее об этом ниже в пункте Работа через свой аккаунт:

{"id": "ид таблицы", "find": "текст для поиска", "col": 2, "return": 4,"creds_path": "путь к вашему файлу с данными для авторизации"}

Параметр ответа "status":"1" означает, что найдено значение, а "status":"0" - не найдено. data - найденное значение, cell_number - найденная ячейка.

Рисунок 8

По умолчанию поиск идет на полное совпадение, но вы можете использовать и другие алгоритмы поиска. Об этом читайте ниже в пункте Другие алгоритмы поиска.

Вывод списка всех совпадений из таблицы

Для нахождения всех заданных значений в колонке используйте функцию findcell с параметром 'find_all', при этом она найдет все значения параметра 'find_all' в колонке 'col' и выведет строкой список уникальных значений из колонки 'return'.

URL функции: https://store.salebot.pro/function/findcell Пример параметров:{"id": "id таблицы", "find_all": "что ищем", "list_name": "название листа", "col": "номер колонки в которой ищем", "return": "номер колонки из которой возвращаем" , "find": "!"}вспомогательный параметр "find": "!" также надо передавать. Пример ответа: {"status":1,"spisok":"\u0412\u0430\u0441\u0438\u043b\u0438\u0439\n+79873238217\n \u0448\u043f\u044b\u0446\n8906796808", "quantity": 5} Разбор ответа: spisok -> Список; quantity -> количество

Другие алгоритмы поиска.

Для использования новых алгоритмов поиска нужно, к стандартному вызову, добавить дополнительные параметры.Добавлено 4 новых алгоритма поиска (‘alg’) по:

Левенштейну - ‘L’ используя регулярное выражение - ‘R’ частичному Левенштейну - ‘PL’ по наличию ключевых слов - ‘K’

Пример запроса: {"id": "ид таблицы", "find": "текст для поиска", "alg":"L"}

Регулярное выражение принимает обязательный параметр: “value”: “тут ваше регулярное выражение”

Регулярное выражение необходимо экранировать. Перед каждым символом \ необходимо указать такой же \, то есть исходное выражение "^\d\d$" должно выглядеть как "^\\d\\d$"

Левенштейн и частичный Левенштейн, принимает дополнительный параметр: “value”: ”95” - минимальный порог похожести, регистр не учитывается (от 1 до полного совпадения 100, по умолчанию 80). Возвращает наивысшее совпадение.

Примеры

Поиск по Левенштейну, порог вхождения 90 и выше. 1) Поиск по всей таблице и вывод текста из соседней ячейки {"id": "ид таблицы", "find": "текст для поиска", “alg”: “L”, “value”: “90”}

Поиск с использованием регулярного выражения 3) Поиск по колонке и вывод текста из указанной колонки в той же строке. Где return номер колонки для вывода. {"id": "ид таблицы", "col": 2, "return": 4, “alg”: “R”, “value”: “^\\d\\d$”} Параметр "find": "текст для поиска" в этом режиме поиска не нужен.

Кнопки для выбора листа в таблице

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

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

URL функции: https://store.salebot.pro/function/sheetchoice

функция принимает следующие параметры

{"id": "id таблицы", "client_type": "#{client_type}", "show": "количество результатов для вывода"}

где id - это id таблицы с которой вы работаете , client_type - тип мессенджера и show - количество результатов для вывода

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

{"vibor": "[{\"type\": \"reply\", \"text\": \"22.11.2019\", \"line\": 0, \"index_in_line\": 0}, {\"type\": \"reply\", \"text\": \"20.11.2019\", \"line\": 1, \"index_in_line\": 0}]"}

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

{"vibor": "0 - 22.11.2019\n1 - 20.11.2019\n"}

Сохраните ответ функции в нужную вам переменную, например, так vibor->vibor. Для ботов, поддерживающих кнопки, вставляйте ее в поле "Расширенные настройки кнопок" (Рисунок 4)

Рисунок 4

Для мессенджеров без кнопок, выводите ее в блоке с ответом (Рисунок 5)

Рисунок 5

Работа через свой аккаунт

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

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

Каждая функция по работе с таблицами принимает: creds_path - необязательный параметр путь или url к вашему файлу содержащему сервисные данные для авторизации в таблице (https://habr.com/ru/post/483302/ - здесь можно почитать, как получить свой сервисный аккаунт гугл для работы с таблицами). Вкратце: для этого необходимо зарегистрировать свой аккаунт в сервисах Google (https://console.developers.google.com/cloud-resource-manager), скачать файл с секретными данными, загрузить его на хостинг или в конструктор Salebot и передавать ссылку на него в параметрах запросов, передавая полученный URL в параметре creds_path.

Видеопримеры

Построчная запись в таблицы
Чтение из таблицы
Поиск информации по таблице

Работа с гугл-таблицами, языком программиста

Работа с буквами колонок

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

Пример:

Результат работы:

Переменные в именах колонок

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

Удаление из таблицы

Также добавлена вспомогательная функция https://store.salebot.pro/function/cellerase с помощью нее можно стереть сделанную запись. Принимает параметры вот такого вида. Ничего не возвращает, просто стирает данные в выбранной ячейке {"id": "1JysraU1Bzn3Etyt6_TYSwz1Y7_mEPfgkQnb7BHEm3_0", "row": "#{row}", "col": "#{col}", "list": "2", "list_id": "1727441620" }

Добавлена возможность стирать диапазон ячеек. Для использования данного функционала передавайте в функцию следующие параметры id - id гугл таблицы и параметр "delrange" в виде "A1:B2" - диапазон ячеек которые нужно стереть. Например: {"id": "1aPWgvExUzBz0R1gXowLfP-D8z9HVNGr2xWfBbT6VcPk", "delrange": "A1:B2"} Также как и раньше вы можете передавать параметры "list" или "list_id" для выбора нужного листа в таблице.