Google-таблицы
В этой статье разберем все возможности взаимодействия ботов с Google-таблицами
Внимание! Устаревшее!
Для лучшей работы с данными загрузите их в Таблицы Salebot, тогда Ваши боты будут работать намного быстрее и без ошибок, связанных с запросами к google-таблицам.
Для работы вам потребуется:
Google-таблица, открытая на редактирование для всех, у кого есть ссылка
URL-функции запроса
Параметры запроса
ОБЩАЯ ИНФОРМАЦИЯ
Как подготовить Google-таблицу
Как написать json-запрос
Переходим в настройки блока, в котором у нас будет осуществляться запись данных таблицу.
Добавляем раздел API-запрос.
Выбираем тип запроса "POST-json"
Переходим к заполнению полей запроса:
URL запроса - путь к функции для выполнения запроса
Сохраняемые значения - перечень параметров ответа запроса с указанием имен переменных, в которые следует сохранить результат в формате: параметр_из_запроса -> ваша_переменная
Если в ответ получаем параметры сложной структуры, то разбираем их так:
"cell_number":{"row":4,"col":2} cell_number|row -> Строка; cell_number|col -> Столбец
Заголовок запроса - заполняется при необходимости. Чаще всего здесь передаются форматы передаваемых данных и/или токен доступа
JSON-параметры - тело запроса, в котором прописываются параметры передаваемых данных в формате:
{"id": "ид таблицы", "действие":{"имя_1":"значение", "имя_2": "#{переменная}"},"creds_path": "путь к вашему файлу с данными для авторизации"}
Для понимания в какой структуре запрос возвращает ответ, напишите в поле 'Сообщение' вывод значения переменной #{custom_answer}.
Где взять идентификатор id таблицы
Идентификатор таблицы содержится в ссылке (выделено цветом, после d/ и до /edit):
Где взять ключ доступа к данным таблицы creds_path
Подробно создание сервисного аккаунта, получение и использование ключа доступа к данным таблицы рассмотрено тут
КАК РАБОТАТЬ С GOOGLE-ТАБЛИЦАМИ
Основные действия с таблицами:
Запись
Чтение
Удаление
Поиск
Далее подробно разберем каждое из действий над таблицами.
Как записать данные
1. Построчная запись в определенные столбцы
Вы можете собрать множество данных от пользователя и записать их в первую свободную в таблице строку. Осуществляется с помощью функции mapping.
В таблице должна быть заполнена шапка (хотя бы одна ячейка в первой строке)
! URL функции: https://store.salebot.pro/function/gsheets
! JSON-Параметры запроса
{"id": "ид таблицы", "mapping":{"a":"#{переменная}", "b": "#{еще}", "c": "#{еще}", "d":"просто текст"}}
Если вы хотите записывать строки не на первом листе, то надо добавить в запрос параметр list_name:
{"id": "ид таблицы", "mapping":{"a":"просто текст", "b": "#{переменная}"}, "list_name": "Название листа"}
Параметры:
id - идентификатор таблицы* a, b, c, d - это как раз имена столбцов list_name - название вашего листа (например, "Лист2")
Пример ответа: {"number_row":8}
Если проблем при выполнении запроса не возникло, то в ответ приходит номер строки, который можно сохранить и использовать для дальнейшей работы.
! Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными. Подробнее об этом ниже в пункте Работа через свой аккаунт:
{"id": "ид таблицы", "mapping":{"a":"#{переменная}", "b": "#{еще}", "c": "#{еще}", "d":"просто текст"},"creds_path": "путь к вашему файлу с данными для авторизации"}
2. Запись данных в определенные ячейки
! URL функции: https://store.salebot.pro/function/gsheets
! JSON-Параметры запроса:
{"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": "путь к вашему файлу с данными для авторизации"}
3. Запись в первую пустую ячейку строки
Вы можете записать данные в указанную строку, запись произойдет в пустую ячейку, справа от последней заполненной ячейки. Осуществляется с помощью функции append_in_row.
URL функции: https://store.salebot.pro/function/gsheets
JSON-Параметры запроса:
{"id": "ид таблицы", "append_in_row":"номер строки", "value": "записываемое значение"}
Если вы хотите записывать строки не на первом листе, то надо добавить в запрос параметр list_name:
{"id": "ид таблицы", "append_in_row":"8", "value": "записываемое значение", "list_name": "Название листа"}
Параметры:
id - это id вашей Google-таблицы. Вы можете получить его из ссылки на вашу таблицу (то, что выделено жирным в ссылке ниже):
https://docs.google.com/spreadsheets/d/1aUbbUaw2SRnJFAavv06Noa1EzumhyShKDm7ie6lYKc4/edit#gid=0
append_in_row - номер строки, в которую производится запись
value - значение, которое запишется в ячейку
Если проблем при выполнении запроса не возникло, то в ответ приходит {"number_col":10,"col_name":"J3"}. Вы можете сохранить эти номера и использовать для дальнейшей работы.
Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными.
Подробнее об этом Работа через свой аккаунт:
{"id": "ид таблицы", "mapping":{"a":"#{переменная}", "b": "#{еще}", "c": "#{еще}", "d":"просто текст"},"creds_path": "путь к вашему файлу с данными для авторизации"}
Как осуществлять чтение данных из таблицы
! URL функции: https://store.salebot.pro/function/gsheets
! JSON-Параметры запроса:
{"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": "путь к вашему файлу с данными для авторизации"}
Как удалить данные
1. Удаление записи из конкретных ячеек
! URL функции: https://store.salebot.pro/function/gsheets
! JSON-Параметры запроса:
{"id": "ид таблицы", "remove":["a1", "b3", "c2"]}.
Удалит значения из конкретных ячеек, которые вы укажете (в нашем примере a1, b3, c2)
Нумерация колонок начинается с единицы.
Если вы хотите удалить ячейки не на первом листе, то надо добавить в запрос параметр list_name:
{"id": "ид таблицы", "remove":["a1", "b3", "c2"], "list_name": "Название листа"}
Если проблем при выполнении запроса не возникло, то в ответ не приходят никакие параметры.
Если вы не хотите рисковать и желаете ограничить доступ к своей таблице лишь своей учетной записью, то надо передавать параметр creds_path с url вашего файла с данными.
Подробнее об этом Работа через свой аккаунт:
{"id": "ид таблицы", "remove":["a1", "b3", "c2"], "list_name": "Название листа","creds_path": "путь к вашему файлу с данными для авторизации"}
2. Как удалить строку полностью (со смещением)
! URL функции: https://store.salebot.pro/function/cellerase
! JSON-Параметры запроса:
{"id": "ид таблицы", "delrows": "номер строки", "list": "номер листа"}
Для использования данного функционала передавайте в функцию следующие параметры:
id - id гугл таблицы
delrows - номер строки (начиная с 1)
list - порядковый номер листа или list_name - имя листа
Пример:
{"id": "1JysraU1Bzn3Etyt6_TYSwz1Y7_mEPfgkQnb7BHEm3_0", "delrows": "1", "list": "2" }
или
{"id": "1JysraU1Bzn3Etyt6_TYSwz1Y7_mEPfgkQnb7BHEm3_0", "delrows": "1", "list_name": "Лист2" }
3. Как удалить колонку полностью (со смещением)
! URL функции: https://store.salebot.pro/function/cellerase
! JSON-Параметры запроса:
{"id": "ид таблицы", "delcols": "номер колонки", "list": "номер листа"}
Аналогично удалению строки, только нужно передать параметр delcols
id - id гугл таблицы delcols - номер столбца (начиная с 1) list - порядковый номер листа либо list_name - имя листа
Пример:
{"id": "1JysraU1Bzn3Etyt6_TYSwz1Y7_mEPfgkQnb7BHEm3_0", "delcols": "1", "list": "2" }
или
{"id": "1JysraU1Bzn3Etyt6_TYSwz1Y7_mEPfgkQnb7BHEm3_0", "delcols": "1", "list_name": "Лист2" }
Как искать данные
1. Как искать текст в таблице (вывод первой подходящей строки)
! URL функции: https://store.salebot.pro/function/findcell
! JSON-Параметры запроса:
{"id": "ид таблицы", "find": "текст для поиска", "col": номер колонки, "return": номер колонки,"creds_path": "путь к вашему файлу с данными для авторизации"}
Часто бывает необходимо по ключу найти ответ в таблице и вывести в боте. Примеры использования: поиск наличия на складе товара, отображение стоимость услуги.
Нумерация строк и колонок идет с единицы.
Существует четыре способа: 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 - найденная ячейка.
По умолчанию поиск идет на полное совпадение, но вы можете использовать и другие алгоритмы поиска. Об этом читайте ниже в пункте Другие алгоритмы поиска.
2. Как осуществлять поиск по всем листам и выводить результат поиска в виде списка
Если возникла необходимость искать значения по всем листам в таблице, то нужно использовать следующую функцию sheet_search_in_all_sheets(workbook_id, search_value)
workbook_id - это идентификатор вашей гугл таблицы
search_value - это значение, которое надо найти
В данной функции поиск идет по полному совпадению значений.
Пример:
result = sheet_search_in_all_sheets('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', '256')
Результат:
Если проблем при выполнении запроса не возникло, то в ответ приходит результат поиска - список словарей, где каждый словарь имеет следующие ключи:
sheet_name — название листа, где было найдено искомое значение
cell_id — идентификатор ячейки, где было найдено искомое значение
search_value — само значение которое надо было найти
3. Как вывести список всех совпадений из таблицы
Для нахождения всех заданных значений в колонке используйте функцию findcell с параметром 'find_all', при этом она найдет все значения параметра 'find_all' в колонке 'col' и выведет строкой список уникальных значений из колонки 'return'.
! URL функции: https://store.salebot.pro/function/findcell
! JSON-Параметры запроса:
{"id": "id таблицы", "find_all": "что ищем", "list_name": "название листа", "col": "номер колонки в которой ищем", "return": "номер колонки из которой возвращаем" , "find": "!"}
Пример ответа:
{"status":1,"spisok":"\u0412\u0430\u0441\u0438\u043b\u0438\u0439\n+79873238217\n \u0448\u043f\u044b\u0446\n8906796808", "quantity": 5}
Разбор ответа: spisok -> Список; quantity -> количество
4. Поиск по нескольким колонкам
Если возникла необходимость искать по нескольким колонкам сразу, то нужно использовать следующий запрос. ! URL функции: https://store.salebot.pro/function/findcell
! JSON-Параметры запроса:
{"id": "id таблицы", "find_all": "ok", "list_name": "название листа", "columns": {"2":"что ищем", "4": "еще что ищем", "3": "то что ищем"}, "return": "номер колонки из которой возвращаем", "with_index": "1"}
Обратите внимание на параметр: "columns": {"2":"что ищем", "4": "еще что ищем", "8": "то что ищем"}
цифры (2, 4, 8), это пример номеров колонок в каких нужно искать, а после двоеточия тот текст, который ищем
вспомогательный параметр "find_all": "ok" также надо передавать
with_index - если указан с значением 1, то функция вернет список с указанием номера строки, в которых найдены ячейки, например: белый - 3 красный - 7 желтый - 8 где 3,7,8 это номера строк, если этот параметр не передавать, то вернется:
with_index - если указан с значением 2, то функция вернет список с указанием номера строки, в которых найдены ячейки, например: 3 - белый 7 - красный 8 - желтый где 3,7,8 это номера строк
with_index - если указан с значением 3, то функция вернет словарь значений с ключом от 0 и далее, в которых найдены ячейки, например: {"0":"белый", "1":"красный", "2":"желтый"} который можно разобрать используя функцию get()
Такой запрос вернет список значений, если в строке совпадут все значения по столбцам в параметре columns.
Пример ответа: {'status': 1, 'spisok': белый\nкрасный\nжелтый\n', 'quantity': 3, 'rows_index': [4, 9, 13]}
Разбор ответа: spisok -> список; quantity -> количество rows_index -> stroki
Если в return указать 0, тогда вернется первая найденная строка со всеми значениями, которые можно сохранить: {"status":"1","data":{"0":"значение 1 ячейки","1":"значение 1 ячейки","2":"значение 2 ячейки","3":"значение 3 ячейки","4":"значение 4 ячейки"},"row":5}
data - словарь со значениями ячеек в строке
row - номер найденной строки
Пример:
4. Другие алгоритмы поиска
Для использования новых алгоритмов поиска нужно, к стандартному вызову, добавить дополнительные параметры.
Добавлено 4 новых алгоритма поиска (‘alg’) по:
Левенштейну - ‘L’ используя регулярное выражение - ‘R’ частичному Левенштейну - ‘PL’ по наличию ключевых слов - ‘K’
Пример запроса: {"id": "ид таблицы", "find": "текст для поиска", "alg":"L"}
Особенности использования и назначение:
Алгоритмы поиска по Левенштейну и по частичному Левенштейну используются при поиске с допустимыми орфографическими ошибками. Процент допустимых ошибок указывается в параметре “value”. Минимальный порог похожести 90%, при этом не учитывается регистр. Пример JSON-параметров: {"id": "ид таблицы", "find": "текст для поиска", "alg": "L", "value": "90"}
Поиск по регулярному выражению - это поиск по соответствию заданной маске. Маска поиска указываеся в обязательном параметре: “value”: “тут ваше регулярное выражение”
Регулярное выражение необходимо экранировать. Перед каждым символом \ необходимо указать такой же \, то есть исходное выражение "^\d\d$" должно выглядеть как "^\\d\\d$"
Поиск по наличию ключевых слов вам уже знаком из практики применения блоков с условиями - это поиск на вхождение указанного слова в строку
Примеры
Поиск по Левенштейну, порог вхождения 90 и выше. 1) Поиск по всей таблице и вывод текста из соседней ячейки {"id": "#{sheet_id}", "find": "что-то, что я ищу", "alg": "L", "value": "90"}
Поиск с использованием регулярного выражения
2) Поиск по колонке и вывод текста из указанной колонки в той же строке. Где 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. Для ботов, поддерживающих кнопки, вставляйте ее в поле "Расширенные настройки кнопок"
Для мессенджеров без кнопок выводите ее в блоке с ответом
Как работать с буквами колонок
Если вам надо перевести букву колонки в цифру, можете воспользоваться функцией калькулятора c2n, если нужно перевести цифру в букву, то используйте n2c. Также вы можете прибавлять к буквам колонок смещение через функцию addCols, которая принимает первым параметром строку с буквой колонки, а второй - смещение. Передайте отрицательное смещение, чтобы идти влево.
Пример:
Результат работы:
Как задать переменные в именах колонок
Вы можете переменной указать адрес ячейки для считывания и по этой же переменной достать из ответа в переменную.
Как добавить лист в таблицу
URL функции: https://store.salebot.pro/function/gsheets
Пример запроса:{"id": "ид таблицы", "new_list_name": "New list", "rows": "1000", "cols": "26"}
id - айди таблицы
new_list_name - имя нового листа
rows - количество строк в листе
cols - количество столбцов в листе
При удачном создании листа, вернет status - ok и ид созданного листа list_id
пример: {"status": "ok", "list_id": 384046608}
Внимание ограничение на размер всей таблицы (не листа) 5000000 ячеек. Это сумма всех ячеек во всех листах одной таблицы.
Возможные ошибки, выводимые в custom_answer
{'code': 400, 'message': 'Invalid requests[0].addSheet: This action would increase the number of cells in the workbook above the limit of 5000000 cells.', 'status': 'INVALID_ARGUMENT'} При попытке создания листа превышается лимит в 5000000 ячеек на одну таблицу. {'code': 400, 'message': 'Invalid requests[0].addSheet: A sheet with the name "ZZZZZZZZZZZ" already exists. Please enter another name.', 'status': 'INVALID_ARGUMENT'} Лист с таким названием уже существует
Возможные ошибки
Если таблица в версии .XLSX, то интеграция работать не будет. Таблицу нужно конвертировать в формат Google-таблиц.
Видеоверсия:
Видео-примеры
Работа с гугл-таблицами языком программиста
Функции (API) для работы с Google-таблицей
Last updated
Was this helpful?