Google-таблицы

В этой статье разберем все возможности взаимодействия ботов с Google-таблицами

Для работы вам потребуется:

  1. Google-таблица, открытая на редактирование для всех, у кого есть ссылка

  2. URL-функции запроса

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

ОБЩАЯ ИНФОРМАЦИЯ

Как подготовить Google-таблицу

Как написать json-запрос

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

  1. Добавляем раздел API-запрос.

  2. Выбираем тип запроса "POST-json"

  3. Переходим к заполнению полей запроса:

URL запроса - путь к функции для выполнения запроса

Сохраняемые значения - перечень параметров ответа запроса с указанием имен переменных, в которые следует сохранить результат в формате: параметр_из_запроса -> ваша_переменная

Если в ответ получаем параметры сложной структуры, то разбираем их так:

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

Заголовок запроса - заполняется при необходимости. Чаще всего здесь передаются форматы передаваемых данных и/или токен доступа

JSON-параметры - тело запроса, в котором прописываются параметры передаваемых данных в формате:

{"id": "ид таблицы", "действие":{"имя_1":"значение", "имя_2": "#{переменная}"},"creds_path": "путь к вашему файлу с данными для авторизации"}

Для понимания в какой структуре запрос возвращает ответ, напишите в поле 'Сообщение' вывод значения переменной #{custom_answer}.

Получение результата выполнения API-запроса в виде сообщения

Где взять идентификатор 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"}. Вы можете сохранить эти номера и использовать для дальнейшей работы.

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

Пример записи данных в первуюпустую ячейку 8 строки

Как осуществлять чтение данных из таблицы

! 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"}

Подробнее о работе через свой аккаунт написано здесь:

{"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": "Название листа"}

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

Подробнее об этом Работа через свой аккаунт:

{"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”: “тут ваше регулярное выражение”

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

Примеры

Поиск по Левенштейну, порог вхождения 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-таблицей

API Google-таблиц

Подробно о подключении и использовании google-таблиц ТУТ

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

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

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

Если статус true, то могут присутствовать и другие параметры, которые можно получить воспользовавшись функцией get()

Если статус false, то присутствует параметр err, в котором описание ошибки.

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

Словарь - некоторые функции принимают параметром словарь с набором определенных данных. Есть ряд правил, которые нужно соблюдать.

  • весь словарь заключается в одинарные кавычки '{}'

  • ключи и значения (текст или переменная) в словаре заключаются в двойные кавычки '{"key": "value", "key2": "#{email}"}'

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

По умолчанию конструктор работает с собственными сервисными аккаунтами для доступа к вашим таблицам. Поэтому вам необходимо выдавать доступ на редактирование по ссылке.

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

Для этого в настройках проект в “Константы проекта”, нужно добавить переменную sheet_json_keys с массивом ваших ключей.

Он может содержать как ключи json из файла, так и url адреса на файл ключа.

Ключ json из файла - получаем файл с ключом (как его получить - читайте ниже), открываем любым текстовым редактором и копируем содержимое и добавляем в массив.

Url адреса на файл ключа - самый простой путь получения такого url - загрузить файл с данными в конструктор. Для этого нужно создать блок не состояние, во вложения - загрузить файл с сервисными данными (как его получить - читайте ниже). После этого нужно кликнуть правой кнопкой по названию файла и выбрать пункт Копировать адрес ссылки.

Четыре примера переменной sheet_json_keys:

  1. ["url адрес на файл ключа", "url адрес на файл ключа 2"]

2. ["url адрес на файл ключа", "url адрес на файл ключа 2", {json ключ из файла}, {json ключ из файла}]

3. [{json ключ из файла}]

(ключ на скриншоте укорочен, реальный ключ на порядок больше)

4. ["url адрес на файл ключа"]

Подробнее о том, как работать через свой аккаунт с Google - таблицами и получении ключа читайте в этой статье:

Как создать новую таблицу из Salebot

Salebot поддерживает создание новых таблиц. Для этого у Вас должна быть переменная sheet_json_keys (как ее создать можно посмотреть тут)

Функция для создания таблиц:

sheet_create_spreadsheet(new_worksheet_title, google_email), где new_worksheet_title - имя вашей новой Google-таблицы. google_email - почта вашего Google-аккаунта.

Пример: new = sheet_create_spreadsheet('ЧЕМОДАНчик', '[email protected]')

После обновления страницы Google-таблиц в Вашем аккаунте Вы найдете новую таблицу:

В списке переменных клиента появится переменная google_spreadsheet_id, в которой будет записан идентификатор новой таблицы.

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь содержащий статус и уведомление: {'status': True, 'message': Google worksheet ‘ЧЕМОДАНчик’ is created'}

status - результат копирования result - сообщение об операции Если возникла ошибка, то вернется статус false и описание ошибки.

Как добавить новый лист в Google-таблицу

Осуществляется с помощью функции:

sheet_create_worksheet(sheet_id, list_name, cols=None, rows=None)

Функция возвращает словарь с идентификатором нового листа и статусом True ({"status":true,"list_id":1063146761}) или описание ошибки, если статус False ({"status":false,"err":"Ошибка или описание"})

Пример:

result = sheet_create_worksheet('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', "New list")

status = get(result, 'status')

Добавление нового листа в Google-таблицу

Как удалить лист в Google-таблице

Чтобы удалить лист в таблице, воспользуйтесь функцией sheet_delete_worksheet(sheet_id, worksheet_name_or_index), где: sheet_id - это идентификатор вашей Google-таблицы, worksheet_name_or_id_or_index - название листа или идентификатор листа, который нужно удалить

Пример:

deleted_sfh = sheet_delete_worksheet('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', 'Лист14')

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

Функция вернет ответ по типу {"status":true,"result":"worksheet Лист14 is deleted"}. В случае возникновения ошибки, функция вернет ее описание.

Как получить количество листов в Google-таблице

Чтобы получить количество листов, а также список пар “номер листа” - “название листа” , используйте функцию sheet_worksheet_indexes(sheet_id), где sheet_id - это идентификатор вашей Google-таблицы

Пример:

worksheet_indexes = sheet_worksheet_indexes('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ')

Получение общего числа листов в Google-таблице

Функция вернет ответ по типу {"status":true,"worksheets_count":10,"worksheet_titles":{"1":"Лист1","2":"Лист2","3":"cvtyf","4":"Лист4","5":"о да","6":"Это новый лист","7":"вль и я","8":"таракан","9":"чемодан","10":"БАЗА ДАННЫХ"}}.

В случае ошибки функция вернет ответ с ее описанием

Как переименовать лист в Google-таблице

Чтобы переименовать лист, используйте функцию sheet_rename_worksheet(sheet_id, worksheet_name_or_index, new_tile), где: sheet_id - это идентификатор вашей Google-таблицы, worksheet_name_or_id_or_index - название листа или идентификатор листа, который надо переименовать, new_tile - новое название

Пример:

worksheet_title_change = sheet_rename_worksheet('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ','Лист5','о да')

Переименование листа в Google-таблице

Как осуществить построчную запись в определенные столбцы

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

Осуществляется с помощью функции:

sheet_mapping_cells(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

cell_data - словарь с параметрами. '{"A": "value", "D": "#{email}"}' - где ключ это буква колонки

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер или идентификатор листа

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

Пример:

result = sheet_mapping_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '{"a":"aaaaa", "B": "#{email}"}')

status = get(result, 'status')

number_row = get(result, 'number_row')

Пример с указанием идентификатора листа:

list_name = Рейтинг

result = sheet_mapping_cells('#{gsheet_id}', '{"a":"#{client_id}", "B": "#{ball}"}','#{list_name}')

Как осуществить запись в первую пустую строку

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

Осуществляется с помощью функции:

sheet_append_row(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

cell_data - словарь с параметрами. '{"A": "value", "D": "#{email}"}' - где ключ это буква колонки

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер или идентификатор листа

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

Пример:

result = sheet_append_row('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '{"a":"aaaaa", "B": "#{email}"}')

status = result["status"]

number_row = result["number_row"]

Пример записи на первый лист таблицы

Пример с указанием идентификатора листа:

list_name = 'результаты викторины' gs_id = '12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT'

result = sheet_append_row(gs_id, '{"a":"#{client_id}", "B": "#{ball}"}',list_name)

Пример с указанием названия листа для записи

Как осуществить запись данных в определенные ячейки

Осуществляется с помощью функции:

sheet_write_cells(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

cell_data - словарь с параметрами. '{"A1": "value", "D4": "#{email}"}' - где ключ это буква колонки и номер строки

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

Пример cell_data:

'{"a1":"#{переменная}", "b3": "#{переменная}", "c1": "12545", "d20":"просто текст"}'

Запись осуществляется в конкретные ячейки, которые вы укажете (в нашем примере a1, b3, c1, d20)

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь {"status":true} или описание ошибки {"status":false,"err":"Ошибка или описание"}

Пример sheet_write_cells:

C указанием идентификатора листа 123456789:

result = sheet_write_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a3", "b3": "#{email}"}', '123456789')

result = sheet_write_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a3", "b3": "#{email}"}')

status = get(result, 'status')

Как удалить запись из конкретных ячеек

Осуществляется с помощью функции:

sheet_remove_cells(sheet_id, cell_list, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

cell_list - массив ячеек. Пример: '["A1", "D4"]' - где буква колонки и номер строки

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь {"status":true} или описание ошибки {"status":false,"err":"Ошибка или описание"}

Пример:

C указанием идентификатора листа 123456789:

result = sheet_remove_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '["a1", "b3", "c2"]', '123456789')

result = sheet_remove_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '["a1", "b3", "c2"]')

status = get(result, 'status')

Удалит значения из конкретных ячеек, которые вы укажете (в нашем примере a1, b3, c2)

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

Как осуществить запись в первую пустую ячейку строки

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

sheet_append_cell_in_row(sheet_id, row, value, worksheet_name_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

row - номер строки, в которую производится запись

value - значение, которое запишется в ячейку

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

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

В случае ошибки придет словарь со статусом false и описанием ошибки {"status":false,"err":"Error value"}

Пример:

Запись в пустую ячейку первой строки.

result = sheet_append_cell_in_row('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', 1, "Значение")

Как читать данные из таблицы

Осуществляется с помощью функции:

sheet_read_cells(sheet_id, cell_data, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

cell_data - словарь с диапазонами.

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

Пример cell_data: '{"a1":"a1", "a3": "b4", "c1": "c3"}'

В примере "a1":"a1" возвращает значение одной ячейки, а "c1":"c3" - 3 значения из колонки C.

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь содержащий статус и все ячейки со значениями {"status":true,"A1":"значение","A3":"значение","B3":"значение","A4":"значение", "B4":"значение", "C1":"","C2":"значение","C3":"ddddddd"} или описание ошибки {"status":false,"err":"Ошибка или описание"}

Пример:

C указанием идентификатора листа 123456789:

result = sheet_read_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a1", "a3": "b4", "c1": "c3"}', '123456789')

result = sheet_read_cells('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzTl', '{"a1":"a1", "a3": "b4", "c1": "c3"}')

status = get(result, 'status')

Как удалить диапазон ячеек

Осуществляется с помощью функции:

sheet_remove_range(sheet_id, del_range, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

del_range - в виде "A1:B2" - диапазон ячеек которые нужно стереть

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь {"status":true} или описание ошибки {"status":false,"err":"Ошибка или описание"}

Пример:

удалить ячейки A1, B1, A2, B2 с первого листа

result = sheet_remove_range('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', 'A1:B2')

Как полностью удалить строку, со смещением

Осуществляется с помощью функции:

sheet_remove_row(sheet_id, row_number, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

row_number - номер строки, который нужно стереть (нумерация с 1)

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь {"status":true} или описание ошибки {"status":false,"err":"Ошибка или описание"}

Пример:

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

result = sheet_remove_row('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '14')

Как полностью удалить колонку, со смещением

Осуществляется с помощью функции:

sheet_remove_col(sheet_id, col_number, worksheet_name_or_id_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

col_number- номер столбца, который нужно стереть (нумерация с 1)

worksheet_name_or_id_or_index - необязательный параметр, название листа, порядковый номер (начиная с 1) или идентификатор листа

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь {"status":true} или описание ошибки {"status":false,"err":"Ошибка или описание"}

Пример:

удалить 6-ой столбец с первого листа

result = sheet_remove_col('12sSVR3Wk-1kNb9CsjyJ2gjLb_PiRl5DhbF4YcD1VzT', '6')

Как осуществлять поиск текста в таблице

Для каждого из вариантов поиска доступно четыре режима поиска algorithm:

  1. полное совпадение - F

  2. по ключевым словам - K (латиница)

  3. расстояние Левенштейна - 80 (значение от 1 до 100 уровень “похожести”)

  4. используя регулярное выражение - R

Чтобы выбрать какой-то из режимов нужно передать значение в соответствующее поле метода (подробный пример в функции sheet_search_in_col_return_cell).

Как осуществлять поиск по колонке и выводить текст из указанной колонки в той же строке

Поиск по колонке вернет первое найденное значение.

Поиск по колонке осуществляется с помощью функции:

sheet_search_in_col_return_cell(sheet_id, query, col_number, return_col, algorithm, worksheet_name_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

query - поисковый запрос, то что нужно найти

col_number - номер колонки, в которой искать (нумерация с 1)

return_col - номер колонки из которой вернуть значение

algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше))

worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Пример использования

Рассмотрим все алгоритмы поиска на примере этой функции.

Условие: ищем в листе с названием Лист1, в колонке 2 ячейку с фразой "поисковый запрос", если такая будет найдена, то вернется значение в этой строке из колонки 5 1. Полное совпадение - F

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "F", "Лист1")

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

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5)

Таблица для поиска

Поиск фразы “Привет мир” в 1 листе во 2 колонке (колонка B). Запрашиваем содержимое ячейки этой же строки в 5 колонке (колонка E):

Результат поиска - ячейка B9

2. По ключевым словам - K (латиница)

Условия те же, что в первом примере.

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "K", "Лист1")

Например, если в столбце есть ячейка с текстом: “Передан поисковый запрос в службу”, то будет найдена эта ячейка, так как поисковый запрос входит в фразу: “Передан поисковый запрос в службу”

Поиск ключевого слова “сосед” в 1 листе в 1 колонке (колонка А). Запрашиваем содержимое ячейки этой же строки во 2 колонке (колонка B):

Результат поиска - ячейка А9

3. Расстояние Левенштейна

Условия те же, что в первом примере.

sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "80", "Лист1")

В примере передали значение 80 - это минимальный порог похожести, регистр не учитывается (значение может быть от 1 до полного совпадения 100). Возвращает наилучшее совпадение.

Поиск фразы “Привет мир” (мы записали с ошибками “превет мор”) в 1 листе во 2 колонке (колонка B). Запрашиваем содержимое ячейки этой же строки в 5 колонке (колонка E)

4. Используя регулярное выражение - R

Условие: ищем в листе с названием Лист1, в колонке 1 ячейку по регулярному выражению "^\d\d\d$" (значение в ячейке состоит из 3 цифр), если такая будет найдена, то вернется значение в этой строке из колонки 5

sheet_search_in_col_return_cell("#{sheet_id}", "^\d\d\d$", 1, 5, "R", "Лист1")

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

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

status - результат поиска

find - значение из выбранное колонки

row - номер строки

col - номер столбца

cell - полное имя ячейки

Если возникла ошибка, то вернется статус false и описание ошибки {"status":false,"err":"Ошибка или описание"}

Как осуществлять поиск по колонке и вывод текста из всей строки

Осуществляется с помощью функции:

sheet_search_in_col_return_row(sheet_id, query, col_number, algorithm, worksheet_name_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

query - поисковый запрос, то что нужно найти

col_number - номер колонки, в которой искать (нумерация с 1)

algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше))

worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Вернет строку первого найденного значения

Пример:

result = sheet_search_in_col_return_row("#{sheet_id}", "222", '1', 'F')

поиск строки 222 в колонке 1 и возврат всей строки.

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

status - результат поиска

row_data - словарь с данными строки

row - номер строки

col - номер столбца

cell - полное имя ячейки

Если возникла ошибка, то вернется статус false и описание ошибки {"status":false,"err":"Ошибка или описание"}

Как осуществлять поиск по колонке и вывод текста из всей строки с выводом результата в виде массива

sheet_search_in_col_return_row_array(sheet_id, query, col_number, algorithm, worksheet_name_or_index=None), где: sheet_id - это идентификатор вашей Google-таблицы. query - поисковый запрос, указание того, что нужно найти col_number - номер колонки, в которой искать (нумерация с 1) algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше)) worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Функция полностью аналогична функции sheet_search_in_col_return_row. Разница только в выводе конечного результата поиска.

При успешном поиске в ответ будет получен массив по типу: ["222","ddddddd","то, что ищем",”еще что ищем”,"44444555"]. Если в найденной строке были пустые ячейки, то в массиве будут возвращены пустые значения.

Как осуществлять поиск по колонке и вывод всех значений

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

sheet_search_in_col_return_cells_list(sheet_id, query, col_number, return_col, algorithm, worksheet_name_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

query - поисковый запрос, то что нужно найти

col_number - номер колонки, в которой искать (нумерация с 1)

return_col - указывает, из какого столбца вернуть значения, если в столбце col_number что-то нашлось

algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше))

worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Пример:

result = sheet_search_in_col_return_cells_list("#{sheet_id}", "ффф", '1', '2', "F")

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

status - результат поиска

rows_index - массив с номерами найденных строк

quantity - количество найденных строк

list - строка со всеми значениями из выбранного столбца

Если возникла ошибка, то вернется статус false и описание ошибки {"status":false,"err":"Ошибка или описание"}

Как осуществлять поиск по нескольким колонкам и вывод всех значений

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

sheet_search_in_multiple_cols_return_list(sheet_id, columns, return_col, with_index, delimiter, algorithm, worksheet_name_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

columns - поисковый запрос, то что нужно найти

return_col - номер колонки, в которой искать (нумерация с 1)

with_index - индекс или номер в списке найденных значений (0 - нумерация (1,2,3,…); 1 - индекс строки; "" - список значений с новой строки без индексов и нумерации)

delimiter - разделитель между индексом и значением

algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше))

worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Пример:

result = sheet_search_in_multiple_cols_return_list("#{sheet_id}", '{"1":"ффф", "4": "ффф"}', '2', '1', ' - ', "F")

status = get(result, 'status')

Результат

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

{"status":true,"rows_index":[11,22,15],"quantity":3,"list":"11 - 111\n15 - 222\n22 - 111\n"}

status - результат поиска

rows_index - массив с номерами найденных строк

quantity - количество найденных строк

list - строка со всеми значениями из выбранного столбца

Если возникла ошибка, то вернется статус false и описание ошибки {"status":false,"err":"Ошибка или описание"}

Как осуществлять поиск по нескольким колонкам и вывод текста из всей строки

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

sheet_search_in_multiple_cols_return_row(sheet_id, columns, algorithm, worksheet_name_or_index=None)

sheet_id - это идентификатор вашей гугл таблицы.

columns - поисковый запрос, то что нужно найти

algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше))

worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Пример

result = sheet_search_in_multiple_cols_return_row("#{sheet_id}", '{"1":"ффф", "4": "ффф"}', "F")

status = get(result, 'status')

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

{"status":true,"rows_index":[11,22,15],"quantity":3,"row_data":{"1":"ффф","2":"111","3":"","4":"ффф","5":"sds"},"row":11}

status - результат поиска

rows_index - массив с номерами найденных строк

quantity - количество найденных строк

list - строка со всеми значениями из выбранного столбца

Если возникла ошибка, то вернется статус false и описание ошибки {"status":false,"err":"Ошибка или описание"}

Как осуществить поиск по ячейке, а затем сделать запись в таблицу

Если требуется найти какое-либо значение в колонке, а затем сделать запись на листе, то нужно использовать следующую функцию: sheet_search_in_col_write_cells(sheet_id, cell_data, query, col_number=None, algorithm=None, worksheet_name_or_index=None), где: sheet_id - это идентификатор вашей Google-таблицы. cell_data - словарь с параметрами '{"A": "value", "D": "#{email}"}' - где ключ - это буква колонки query - поисковый запрос, то что нужно найти col_number - номер колонки, в которой искать (нумерация начинается с 1) algorithm - алгоритм поиска (F - полное совпадение, K - наличие ключевых слов, R - регулярное выражение, 1-100 - процент похожести (подробнее выше)) worksheet_name_or_id_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_search_in_col_write_cells('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', '{"t":"Пишем пишем"}','А вот и ячейка', 2, "F"), где: ‘А вот и ячейка’ - значение для поиска, которое найдется по аналогии с функцией sheet_search_in_col_return_cell, 2 - номер столбца, а {"t":"Пишем пишем"} - фраза, которая запишется в таблицу по аналогии с функцией sheet_mapping_cells

status = get(result, 'status')

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь, содержащий статус и все ячейки со значениями, вида: {"status":true,"number_row":24}

Можно сохранить номер строки number_row и использовать для дальнейшей работы. Если возникла ошибка, то вернется статус false и описание ошибки.

Арифметические действия в таблицах

К арифметическим действиям относятся сложение, вычитание, умножение и деление. Разберем подробнее:

- Деление

sheet_div_to_acell(sheet_id, cell, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы cell - делимое, адрес ячейки в стиле A1 (буква столбца (латиница) и номер строки ), например: B2, C333. Если значение ячейки - пустое значение, то по умолчанию значение будет приравнено 0 value - делитель, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_div_to_acell("#{sheet_id}", 'A1', 10) - деление значения в ячейке А1 на 10. Результат будет записан в ту же ячейку.

Для записи адреса ячейки в стиле R1C1: sheet_div_to_cell(sheet_id, row, col, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы row - номер строки (начинается с 1) col - номер колонки (начинается с 1) value - делитель, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Если значение ячейки, заданной параметрами row и col - пустое значение, то по умолчанию значение будет приравнено 0

Пример: result = sheet_div_to_cell("#{sheet_id}", 1, 1, 10) - деление содержимого ячейки R1C1 (или А1) на 10. Результат будет записан в ту же ячейку

- Умножение

sheet_mul_to_acell(sheet_id, cell, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы cell - множитель1, адрес ячейки в стиле A1 (буква столбца (латиница) и номер строки ), например: B2, C333. Если значение ячейки - пустое значение, то по умолчанию значение будет приравнено 0 value - множитель2, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_mul_to_acell("#{sheet_id}", 'a1', 10) - умножение содержимого ячейки A1 на 10. Результат будет записан в ту же ячейку

Для записи адреса ячейки в стиле R1C1: sheet_mul_to_cell(sheet_id, row, col, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы row - номер строки (начинается с 1) col - номер колонки (начинается с 1) value - множитель2, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Если значение ячейки, заданной параметрами row и col - пустое значение, то по умолчанию значение будет приравнено 0

Пример: result = sheet_mul_to_cell("#{sheet_id}", 1, 2, 10) - умножение содержимого ячейки R1C2 (или А2) на 10. Результат будет записан в ту же ячейку

- Вычитание

sheet_sub_to_acell(sheet_id, cell, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы cell - уменьшаемое, адрес ячейки в стиле A1 (буква столбца (латиница) и номер строки). Если значение ячейки - пустое значение, то по умолчанию значение будет приравнено 0 value - вычитаемое, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_sub_to_acell("#{sheet_id}", 'a1', 10)

Для записи адреса ячейки в стиле R1C1: sheet_sub_to_cell(sheet_id, row, col, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы row - номер строки (начинается с 1) col - номер колонки (начинается с 1) value - вычитаемое, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Если значение ячейки, заданной параметрами row и col - пустое значение, то по умолчанию значение будет приравнено 0

Пример: result = sheet_sub_to_cell("#{sheet_id}", 1, 1, 10)

- Сложение

sheet_add_to_acell(sheet_id, cell, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы cell - слагаемое1, адрес ячейки в стиле A1 (буква столбца (латиница) и номер строки ).Если значение ячейки - пустое значение, то по умолчанию значение будет приравнено 0 value - слагаемое2, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_add_to_acell("#{sheet_id}", 'a1', 10)

Для записи адреса ячейки в стиле R1C1: sheet_add_to_cell(sheet_id, row, col, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы row - номер строки (начинается с 1) col - номер колонки (начинается с 1) value - слагаемое2, число worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Если значение ячейки, заданной параметрами row и col - пустое значение, то по умолчанию значение будет приравнено 0

Пример: result = sheet_add_to_cell("#{sheet_id}", 1,1, 10)

- Конкатенация (сложение строк)

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

sheet_append_to_acell(sheet_id, cell, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы cell - слагаемое1, строка, адрес ячейки в стиле A1 (буква столбца (латиница) и номер строки). value - слагаемое2, строка worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_append_to_acell("#{sheet_id}", 'a1', 'дописать') Результат: Если ячейка содержала значение "Привет", то результатом выполнения функции конкатенации будет: "Приветдописать"

Для записи адреса ячейки в стиле R1C1: sheet_append_to_cell(sheet_id, row, col, value, worksheet_name_or_index), где: sheet_id - ид Google-таблицы row - номер строки (начинается с 1) col - номер колонки (начинается с 1) value - слагаемое2, строка worksheet_name_or_index - необязательный параметр, название листа или идентификатор листа

Пример: result = sheet_append_to_cell("#{sheet_id}", 1, 1, 'дописать') Результат: Если ячейка содержала значение "Привет", то результатом выполнения функции конкатенации будет: "Приветдописать"

Как скопировать все данные с листа на другой лист

Чтобы скопировать всю информацию с листа на другой, воспользуйтесь функцией sheet_copy_list(sheet_id, sourceSheet_Name, destinationSheet_Name), где: sheet_id - это идентификатор вашей Google-таблицы. sourceSheet_Name - имя листа, который будет скопирован destinationSheet_Name - имя листа, куда будут скопированы данные

Пример: result = sheet_copy_list('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ', 'Лист1','Лист5')

Если проблем при выполнении запроса не возникло, то в ответ приходит словарь содержащий статус и уведомление: {"status":true, 'result': spreadsheet ‘Лист1’ copied to ‘Лист5’} status - результат копирования result - сообщение об операции Если возникла ошибка, то вернется статус false и описание ошибки

Как скопировать часть данных (диапазон ячеек) с одного листа на другой

Чтобы скопировать часть информации (диапазон ячеек) с листа на другой, воспользуйтесь функцией sheet_copy_data_block(sheet_id, sourceSheet_Name, destinationSheet_Name, sourseSheet_data, destinationSheet_data), где sheet_id - это идентификатор вашей Google-таблицы. sourceSheet_Name - имя листа, данные которого будут скопированы destinationSheet_Name - имя листа, куда будут скопированы данные sourseSheet_data - список с номерами ячеек диапазона-источника (откуда копируем данные) destinationSheet_data - список с номерами ячеек диапазона-приемника (куда поместить данные)

Переменные sourseSheet_data и destinationSheet_data должны представлять собой список вида [0,6,0,6], состоящий из четырех цифр, обозначающих номера начальной и конечной строк, начального и конечного столбцов соответственно.

Пример диапазона ячеек в таблице: Блок данных, находящийся в блоке с концами в ячейках A1-А5-F1-F5 будет иметь координаты [0,5,0,6]:

Разберем на примерах:

1) Если размер диапазона-источника sourseSheet_data больше размеров диапазона-приемника destinationSheet_data:

диапазон-источник данных sourseSheet_data [0,5,0,6]
диапазон-приемник данных destinationSheet_data [0,3,0,2]

2) Если размер диапазона-приемника данных destinationSheet_data больше размера диапазона-источника sourseSheet_data:

диапазон-приемник данных destinationSheet_data [1,10,1,7]

Пример:

Итак, разберем копирование диапазона данных A1:F5 с Лист1 в Лист2 со смещением на 1 строку и 1 столбец: data_ag2 = [0, 5, 0, 6] data_ag3 = [1, 6, 1, 7] result=sheet_copy_data_block('1xeG0NcC5xaXVnXqgGY9z3uUkVlZX-vsx1xOTfK2Wnd4', 'Лист1', 'Лист2', '#{data_ag2}', '#{data_ag3}')

диапазон-приемник данных destinationSheet_data [1,6,1,7]

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

{"status":true, 'result': spreadsheet block from ‘Лист1’ copied to ‘Лист2’}, где: status - результат копирования result - сообщение об операции Если возникла ошибка, то вернется статус false и описание ошибки.

Last updated

Was this helpful?