Python / Работа с Google таблицами с помощью Sheets API

Python / Работа с Google таблицами с помощью Sheets API

Sheets API - одна из самых непонятных апишек, здесь я опишу понятное руководство по этой апишке на примере задачи обновления ссылок в таблице

Чтобы работать с Гугл шитс апи, надо авторизоваться, а потом можно запросики кидать.

Но сначала пререквизиты:

Пререквизиты

Создать проект в Google Cloud + включить Sheets API

Поставить клиентские либы:

pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib

Авторизация

Чтобы авторизоваться можно использовать OAuth, можно использовать сервисный аккаунт
OAuth нужен когда разные пользователи могут взаимодействовать с гугл таблицей
Обычно нам это не надо, обычно у нас один пользователь какие-то операции над таблицей делает и все (мы просто апи методы хотим покидать, нам похуй ваще как это делать, авторизоваться через гугл - нахуя?)
Плюс это не наш вариант, потому что на сервере ты не будешь авторизовываться через гугл: открывать окошко, входить в гугл и все такое - на сервере нет такой возможности
Соответсвенно, мы используем сервисный аккаунт

Сервисный аккаунт

Сервисный аккаунт - это специальный пользователь, которому даем доступы к гугловым сервисам (не только гугл шитс)

То есть создаем пользователя

Создание сервисного акка - достаточно только имя ввести

Создаем апи-ключ для этого пользователя - с помощью ключа мы будем вызывать методы от имени сервисного акка

После создания автоматически начнется скачивание JSON-а с ключом

Даем доступ к гугл таблице для этого пользователя

И в одну строчечку получаем С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:

Создание доступов: тип - Desktop app, название - произвольное

Скачиваем доступы, получаем файлик, типа 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,
)

Запускать это дело можно из редактора:

Apps Script редактор

Плагины

Как я говорил, можно плагины писать для тех же Гугл таблиц
Вот пример работы с Youtube API, который добавляет в Гугл таблицу кастомную кнопулю, которая подгружает данные о YT-канале:

Кастомная кнопочка в Google Sheets
Возможно, это не так удобно как из Питона, но суть в том, что такой инструмент существует, и мб для каких-то задач он пригоден