
Python / Работа с Google таблицами с помощью Sheets API
Sheets API - одна из самых непонятных апишек, здесь я опишу понятное руководство по этой апишке на примере задачи обновления ссылок в таблице
Содержимое
Чтобы работать с Гугл шитс апи, надо авторизоваться, а потом можно запросики кидать.
Но сначала пререквизиты:
Пререквизиты
- Создать проект в Google Cloud + включить Sheets API
- Поставить клиентские либы:
pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib
Авторизация
- Чтобы авторизоваться можно использовать OAuth, можно использовать сервисный аккаунт
- OAuth нужен когда разные пользователи могут взаимодействовать с гугл таблицей
- Обычно нам это не надо, обычно у нас один пользователь какие-то операции над таблицей делает и все (мы просто апи методы хотим покидать, нам похуй ваще как это делать, авторизоваться через гугл - нахуя?)
- Плюс это не наш вариант, потому что на сервере ты не будешь авторизовываться через гугл: открывать окошко, входить в гугл и все такое - на сервере нет такой возможности
- Соответсвенно, мы используем сервисный аккаунт
Сервисный аккаунт
Сервисный аккаунт - это специальный пользователь, которому даем доступы к гугловым сервисам (не только гугл шитс)
- То есть создаем пользователя
- Создаем апи-ключ для этого пользователя - с помощью ключа мы будем вызывать методы от имени сервисного акка
- Даем доступ к гугл таблице для этого пользователя
- И в одну строчечку получаем
Сredentials
, с помощью которых мы можем кидать запросики
from google.oauth2 import service_account
creds = service_account.Credentials.from_service_account_file(
# Путь к json-у, полученному выше
'yaxxxta-b337157255d8.json',
# scopes - необходимые права доступа - в данном случае чтение и редактирование гугл-таблиц
scopes=['https://www.googleapis.com/auth/spreadsheets']
)
Если все-таки интересует OAuth, то вот:
OAuth
- Заходим на страницу создания доступов для API и создаем доступы:
Create credentials > OAuth client ID
:
- Скачиваем доступы, получаем файлик, типа
client_secret_***.json
, и закидываем его в директорию с проектом:
-
Генерируем токен, который нужен для работы с апи, используя функцию
get_creds
:import os.path from typing import List from google.auth.transport.requests import Request from google.oauth2.credentials import Credentials from google_auth_oauthlib.flow import InstalledAppFlow def get_creds(secrets_file: str, scopes: List[str]) -> Credentials: """ Генерирует токен, необходимый для работы с апи Токен читается из файла Если этого файла нет, то используются доступы из {secrets_file} После чтения доступов из файла в консоли появится ссылка, перейдя по которой надо будет авторизоваться Если возникает надпись "This app isn't verified", то надо прожать Advanced > Go to {Project Name} (unsafe) https://developers.google.com/sheets/api/quickstart/python#this_app_isnt_verified После авторизации токен сохранится на диск :param secrets_file: путь к файлу с доступами, напр. 'client_secret_406798162311-63us552o41nrs0ashvt87h58gbgivjsh.apps.googleusercontent.com.json' :param scopes: права доступа, необходимые для работы с апи; в данном случае будет достаточно [`https://www.googleapis.com/auth/spreadsheets`] - это права и на чтение и на запись данных в таблицу """ creds = None if os.path.exists('token.json'): creds = Credentials.from_authorized_user_file('token.json', scopes) if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file(secrets_file, scopes) creds = flow.run_local_server(port=0) with open('token.json', 'w') as token: token.write(creds.to_json()) return creds
-
Ну и вызываем метод выше, чтобы получить
Credentials
:
creds = get_creds(
secrets_file='client_secret_406798162311-63us552o41nrs0ashvt87h58gbgivjsh.apps.googleusercontent.com.json',
scopes=['https://www.googleapis.com/auth/spreadsheets']
)
Кидание запросиков
-
Используя
Credentials
, создаем апи-клиент, который будет кидать запросы:from googleapiclient.discovery import build service = build('sheets', 'v4', credentials=creds)
-
Запросы кидаются аналогично путям методов, напр. для запроса
spreadsheets.get
вызов будет выглядеть так:resp = service.spreadsheets().get(**params).execute()
Данные, передаваемые в параметрах
Таблица и листы
Для любого запроса необходим айди таблицы и айди листа; причем айди листа может быть как строковым, так и числовым.
Получить эти данные можно из ссылки на таблицу. Например, для такой ссылки:
https://docs.google.com/spreadsheets/d/e3b0c44298fc1c149afbf4c8996fb92427/edit#gid=1340597691
- e3b0c44298fc1c149afbf4c8996fb92427 - айди таблицы
- 1340597691 - численный айди листа
- Строковой айди листа — это собственно название листа
Вынесем это в константы, которые будем юзать в примерах запросов:
TABLE = 'e3b0c44298fc1c149afbf4c8996fb92427'
SHEET = "Лист 1"
SHEET_ID = 1340597691
Диапазон ака range
Диапазон ячеек, над которыми будут проводиться операции, определяется в 2 форматах:
- Строка вида "Лист 1!A1:B2" - классический формат диапазонов, включающий название листа, название колонки в виде буквы, и номер строки
- Объект GridRange, использующий индексы строк и колонок
Аналогично определим их в виде констант:
from openpyxl.utils.cell import column_index_from_string
# Строка вида "Лист 1!D5:GJ396"
START_COLUMN, START_ROW = "D", 5
END_COL, END_ROW = "GJ", 396
RANGE_STR = F'{SHEET}!{START_COLUMN}{START_ROW}:{END_COL}{END_ROW}'
# Объект GridRange
# Индексы колонок и строк начинаются с 0,
# то есть для ячейки A1 будут индексы 0 и 0, для ячейки D5 будут индексы 3 и 4
START_COL_INDEX = column_index_from_string(START_COLUMN) - 1
START_ROW_INDEX = START_ROW - 1
END_COL_INDEX = column_index_from_string(END_COL) - 1
END_ROW_INDEX = END_ROW - 1
# GridRange наполовину инклюзивный - [startIndex, endIndex),
# т.е. включает начальный индекс диапазона и не включает конечный индекс,
# т.е. чтобы включить последний индекс нужно плюсануть 1
RANGE = {
"sheetId": SHEET_ID,
"startRowIndex": START_ROW_INDEX,
"startColumnIndex": START_COL_INDEX,
"endRowIndex": END_ROW_INDEX + 1,
"endColumnIndex": END_COL_INDEX + 1,
}
Для перевода буквенных колонок в число, можно использовать метод
column_index_from_string
из библиотеки openpyxl
Пример: обновление ссылок в ячейках
Рассмотрим как пользоваться методами апи на примере задачи обновления ссылок.
Суть задачи — проверить есть ли в ячейке ссылка, если есть, то нужно заменить в ссылке домен.
spreadsheets.get
Метод spreadsheets.get позволяет получить всю информацию о таблице, то есть помимо значений ячеек можно получить информацию о стилях и ссылках.
Пример вызова:
resp = service.spreadsheets().get(
spreadsheetId=TABLE,
ranges=[RANGE_STR],
# Нужно, чтобы приходила инфа о ячейках
includeGridData=True,
).execute()
rows = resp["sheets"][0]["data"][0]["rowData"]
resp
- это большой объект, включающий в себя, помимо данных о ячейках, другие данные, напр. данных о листахrows = resp["sheets"][0]["data"][0]["rowData"]
- таким образом получаем данные о строкахcells = rows[0]["values"]
- таким образом получаем ячейки строки
Алгоритм замены ссылок
Теперь напишем алгоритм замены ссылок, который будет создавать массив обновлений ячеек — массив RowData:
old_url = 'https://google.com/'
new_url = "https://yandex.ru/"
update_rows = []
for row in rows:
update_cells = []
for cell in row["values"]:
link = (
# Если ссылка выставлена для всей ячейки, то берем hyperlink
cell.get("hyperlink") or
# Если ссылка выставлена для части ячейки, например "*Тут* хорошо", то ссылка берется из textFormatRuns.0.format.link.uri
value_by_path(cell, "textFormatRuns.0.format.link.uri") or
# Еще ссылка может быть выставлена для другой части ячейки, например "Хорошо *тут*"
# в таком случае ссылка берется из textFormatRuns.1.format.link.uri
value_by_path(cell, "textFormatRuns.1.format.link.uri")
)
# Задавать ссылку для ячейки можно 3 способами
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData.FIELDS.hyperlink
# Будем использовать textFormatRun.format.link, т.к. он самый рабочий из тех, что я пробовал
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#TextFormat.FIELDS.link
update_link = (
{"userEnteredFormat": {"textFormat": {"link": {"uri": new_url + link[len(old_url):]}}}}
if link and link.startswith(old_url)
else {}
)
update_cells.append(update_link)
update_rows.append({"values": update_cells})
Текст функции
value_by_path
будет приведен в итоговом коде скрипта
spreadsheets.batchUpdate
Наконец нужно вызвать метод обновления данных таблицы - spreadsheets.batchUpdate.
Чтобы вызвать метод spreadsheets.batchUpdate
нужно передать в него массив Request. Объект Request
олицетворяет операцию над таблицей, такую как вставка графиков, удаление ячеек, добавление листов и другие. Нас интересует обновление ячеек — то есть UpdateCellsRequest:
service.spreadsheets().batchUpdate(
spreadsheetId=TABLE,
body={
"requests": [
{
"updateCells": {
"rows": update_rows,
# Здесь указывается какие поля обновляем
"fields": "userEnteredFormat.textFormat.link.uri",
"range": RANGE
}
}
]
}
).execute()
Все, можно запускать код.
Код выгрузит ячейки таблицы в диапазоне и заменит ссылки, содержащие google.com, на yandex.ru.
Apps Script
- А еще есть Apps Script - это JS-подобный язык (бтв ему 12 лет)))), только там из коробки гугловые апишки + можно напрямую взаимодействовать с гугловыми сервисами, типа плагины писать для гугл-шитс
- И самое главное - не нужно вообще писать код для авторизации / делать какие-либо телодвижения по этому поводу
- То есть для Apps Script часть с Авторизацией можно пропустить и сразу юзать методы апи:
// Метод spreadsheets.get
const resp = Sheets.Spreadsheets.get(
TABLE,
{
ranges: [RANGE_STR],
includeGridData: true
},
);
const rows = resp.sheets[0].data[0].rowData;
// Метод spreadsheets.batchUpdate
service.spreadsheets().batchUpdate(
{
requests: [
{
updateCells:{
rows: update_rows,
fields: "userEnteredFormat.textFormat.link.uri",
range: RANGE
}
}
]
},
TABLE,
)
- Запускать это дело можно из редактора:
Плагины
- Как я говорил, можно плагины писать для тех же Гугл таблиц
- Вот пример работы с Youtube API, который добавляет в Гугл таблицу кастомную кнопулю, которая подгружает данные о YT-канале:
- Возможно, это не так удобно как из Питона, но суть в том, что такой инструмент существует, и мб для каких-то задач он пригоден