Для лучшей работы с данными загрузите их в Таблицы
Salebot, тогда Ваши боты будут
работать намного быстрее и без ошибок, связанных с запросами к
google-таблицам.
- Как подготовить Google
таблицу - Где взять идентификатор id
таблицы - Как работать с буквами
колонок - Как задать переменные в именах
колонок - Возможные
ошибки - Функции для работы с
Google-таблицей
ОБЩАЯ ИНФОРМАЦИЯ
Как подготовить Google-таблицу
Где взять идентификатор id таблицы
Идентификатор таблицы содержится в ссылке (выделено цветом, после d/ и
до /edit):
Как работать с буквами колонок
Если вам надо перевести букву колонки в цифру, можете воспользоваться
функцией калькулятора c2n, если нужно перевести цифру в букву, то
используйте n2c. Также вы можете прибавлять к буквам колонок
смещение через функцию addCols, которая принимает первым параметром
строку с буквой колонки, а второй - смещение. Передайте отрицательное
смещение, чтобы идти влево.
Пример:
Результат работы:
Как задать переменные в именах колонок
Вы можете переменной указать адрес ячейки для считывания и по этой же
переменной достать из ответа в переменную.
Возможные ошибки
Если таблица в версии .XLSX, то интеграция работать не будет. Таблицу
нужно конвертировать в формат Google-таблиц.
Видеоверсия:
Видео-примеры
Построчная запись в таблицы
Запись и чтение из таблицы
Поиск информации по таблице
Функции для работы с Google-таблицей
<details>
<summary>
Функции Google-таблиц
</summary>
Подробно о подключении и использовании 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:
- ["url адрес на файл ключа", "url адрес на файл ключа 2"]
2. ["url адрес на файл ключа", "url адрес на файл ключа 2", {json ключ
из файла}, {json ключ из файла}]
3. [{json ключ из файла}]
(ключ на скриншоте укорочен, реальный ключ на порядок больше)
4. ["url адрес на файл ключа"]
Ключи прописываем в Константах проекта.
В функциях в калькуляторе передавать дополнительный параметр не нужно,
ключи подтянутся автоматически.
Если в переменной sheet_json_keys указано ****несколько
сервисных**** аккаунтов, то ****настройки доступа к
таблице**** на стороне Google делаем ****на****
****ВСЕ УКАЗАННЫЕ**** в переменной sheet_json_keys
****СЕРВИСНЫЕ АККАУНТЫ****.
Подробнее о том, как работать через свой аккаунт с Google - таблицами и
получении ключа читайте в этой статье:
Как создать новую таблицу из Salebot
Salebot поддерживает создание новых таблиц. Для этого у Вас должна быть
переменная sheet_json_keys (как ее создать можно посмотреть
тут)
Функция для создания таблиц:
sheet_create_spreadsheet(new_worksheet_title, google_email), где
new_worksheet_title - имя вашей новой Google-таблицы.
google_email - почта вашего Google-аккаунта.
Переменная google_email необходима для передачи указанной почте
прав редактора созданной таблицы, т.к google автоматически присваивает
права владельца вашему зарегистрированному ранее Google-проекту. К
сожалению, на данный момент это единственная возможность создавать
Google-таблицы через Api.
Пример:
new = sheet_create_spreadsheet('ЧЕМОДАНчик',
'amadeoartista1992@gmail.com')
После обновления страницы Google-таблиц в Вашем аккаунте Вы найдете
новую таблицу:
В списке переменных клиента появится переменная google_spreadsheet_id, в
которой будет записан идентификатор новой таблицы.
Если проблем при выполнении запроса не возникло, то в ответ приходит
словарь содержащий статус и уведомление:
{'status': True, 'message': Google worksheet 'ЧЕМОДАНчик' is created'}
status - результат копирования
result - сообщение об операции
Если возникла ошибка, то вернется статус false и описание ошибки.
Как добавить новый лист в **Google-**таблицу <a href="#docs-internal-guid-f5d2c48c-7fff-2b4d-af8d-f769cccc0be3" id="docs-internal-guid-f5d2c48c-7fff-2b4d-af8d-f769cccc0be3">{=html}</a>{=html}
Осуществляется с помощью функции:
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-таблице
Чтобы удалить лист в таблице, воспользуйтесь функцией
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')
Функция вернет ответ по типу {"status":true,"result":"worksheet Лист14
is deleted"}.
В случае возникновения ошибки, функция вернет ее описание.
Как получить количество листов в Google-таблице
Чтобы получить количество листов, а также список пар "номер листа" -
"название листа" , используйте функцию
sheet_worksheet_indexes(sheet_id), где
sheet_id - это идентификатор вашей Google-таблицы
Пример:
worksheet_indexes =
sheet_worksheet_indexes('1oP8Ax4A7FKUY7Mv9eAYAM8q0J0ddlKaD8dOa076AtvQ')
Функция вернет ответ по типу
{"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','о
да')<br>{=html}
Как осуществить построчную запись в определенные столбцы
Вы можете собрать множество данных от пользователя и записать их в
первую свободную в таблице строку.
В таблице должна быть заполнена шапка (хотя бы одна ячейка в первой
строке)
Осуществляется с помощью функции:
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')
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 --- само значение которое надо было найти
Как осуществлять поиск текста в таблице
Для каждого из вариантов поиска доступно четыре режима поиска
algorithm:
- полное совпадение - F
- по ключевым словам - K (латиница)
- расстояние Левенштейна - 80 (значение от 1 до 100 уровень
"похожести") - используя регулярное выражение - 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):
2. По ключевым словам - K (латиница)
Условия те же, что в первом примере.
sheet_search_in_col_return_cell("#{sheet_id}", "поисковый запрос", 2, 5, "K", "Лист1")
Например, если в столбце есть ячейка с текстом: "Передан поисковый
запрос в службу", то будет найдена эта ячейка, так как поисковый запрос
входит в фразу: "Передан поисковый запрос в службу"
Поиск ключевого слова "сосед" в 1 листе в 1 колонке (колонка А).
Запрашиваем содержимое ячейки этой же строки во 2 колонке (колонка B):
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{=tex}$" (значение в ячейке состоит из
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":"Ошибка или описание"}
Как осуществлять поиск по колонке и вывод текста из всей строки <a href="#docs-internal-guid-1b653c44-7fff-519c-27e0-2ca0a28dd1e9" id="docs-internal-guid-1b653c44-7fff-519c-27e0-2ca0a28dd1e9">{=html}</a>{=html}
Осуществляется с помощью функции:
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":"Ошибка или описание"}
Как осуществлять поиск по колонке и вывод текста из всей строки с выводом результата в виде массива <a href="#docs-internal-guid-1b653c44-7fff-519c-27e0-2ca0a28dd1e9" id="docs-internal-guid-1b653c44-7fff-519c-27e0-2ca0a28dd1e9">{=html}</a>{=html}
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"].
Если в найденной строке были пустые ячейки, то в массиве будут
возвращены пустые значения.
Как осуществлять поиск по колонке и вывод всех значений <a href="#docs-internal-guid-9aa9affe-7fff-9421-7005-d65619b45de7" id="docs-internal-guid-9aa9affe-7fff-9421-7005-d65619b45de7">{=html}</a>{=html}
Для нахождения всех заданных значений в колонке используйте функцию
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":"Ошибка или описание"}
Как осуществлять поиск по нескольким колонкам и вывод всех значений <a href="#docs-internal-guid-6abb672c-7fff-e2fc-a839-1826a20b2505" id="docs-internal-guid-6abb672c-7fff-e2fc-a839-1826a20b2505">{=html}</a>{=html}
Если возникла необходимость искать по нескольким колонкам сразу и
получить список значений из колонки в строках, в которых будут найдены
все значения, то нужно использовать следующую функцию
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":"Ошибка или описание"}
Как осуществлять поиск по нескольким колонкам и вывод текста из всей строки <a href="#docs-internal-guid-96b3ea35-7fff-a806-89f6-07b5496fff8c" id="docs-internal-guid-96b3ea35-7fff-a806-89f6-07b5496fff8c">{=html}</a>{=html}
Если возникла необходимость искать по нескольким колонкам сразу и
получить первую найденную строку, то нужно использовать следующую
функцию
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], состоящий из четырех цифр, обозначающих
номера начальной и конечной строк, начального и конечного столбцов
соответственно.
Нумерация столбцов и строк в таблице начинается с нуля, т.е.:
столбец A будет иметь номер 0, строка 1 - тоже номер 0;
столбец С - 2, строка 2 - 1.Например, запись [0,1,0,3] - это диапазон ячеек A1:C1 (A1 - [0,1],
C1 - [0,3]).Обратите внимание: номера конечной строки, столбца не входят в
диапазон
Пример диапазона ячеек в таблице:
Блок данных, находящийся в блоке с концами в ячейках A1-А5-F1-F5 будет
иметь координаты [0,5,0,6]:
При полном совпадении размеров диапазонов-источника данных
sourseSheet_data и приемника destinationSheet_data данные будут
скопированы полностью в указанных рамках;
Если размер диапазона-источника sourseSheet_data больше
диапазона-приемника destinationSheet_data, то данные cкопируются
полностью (превышая рамки диапазона-приемника);
Если размер диапазона-приемника destinationSheet_data больше источника
sourseSheet_data, то пустые ячейки будут дозаполнены в случае, если
диапазон-источник поместится в полном объеме, иначе оставшиеся ячейки
останутся пустыми.
Разберем на примерах:
1) Если размер диапазона-источника sourseSheet_data больше размеров
диапазона-приемника destinationSheet_data:
![диапазон-источник данных sourseSheet_data [0,5,0,6]](../../.gitbook/assets/image%20(219).png)
![диапазон-приемник данных destinationSheet_data [0,3,0,2]](../../.gitbook/assets/image%20(220).png)
2) Если размер диапазона-приемника данных destinationSheet_data больше
размера диапазона-источника sourseSheet_data:
![диапазон-приемник данных destinationSheet_data [1,10,1,7]](../../.gitbook/assets/image%20(221).png)
Пример:
Итак, разберем копирование диапазона данных 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]](../../.gitbook/assets/image%20(225).png)
Если проблем при выполнении запроса не возникло, то в ответ приходит
словарь содержащий статус и уведомление:
{"status":true, 'result': spreadsheet block from 'Лист1' copied to
'Лист2'}, где:
status - результат копирования
result - сообщение об операции
Если возникла ошибка, то вернется статус false и описание ошибки.
</details>