Google Sheets API + Python. Чтение и запись с Сервисным Аккаунтом Google Cloud

Google Sheet и Sheets API - классный (простой, бесплатный и универсальный) способ организовать хранение и анализ данных получаемых от своего сервиса. При условии что этих данных не то чтобы очень много и поступают они не то чтобы очень часто.

У меня таких задач хватает и решаю я их либо на Python, либо на PHP. Сегодня возьму в руки Python.

Доступ к Google API

Чтобы писать из кода в Google Sheet нам нужен доступ к Google Sheets API, которое входит в библиотеку API Google Cloud Platform. Для этого нужно:

1. нужно создать проект в Google Cloud Platform

2. подключить к проекту в Google Cloud Platform Sheets API

3. заполнить OAuth consent screen ( пользовательское соглашение )

4. создать либо OAuth Client ID, либо Service Account

5. я использую Сервисный Аккаунт, следовательно мне нужно дать сервисному аккаунту доступ к Google Sheet (электронной таблице) с уровнем Редактор, тогда я смогу писать из кода в эту таблицу

6. получить JSON файл с токеном доступа к сервисному аккаунту и положить его куда-то откуда код сможет его читать

В тексте это выглядит просто, однако в первый раз интерфейс Google Cloud Console выглядит как рубка звездолета. Куда жать не понятно, иные операции нельзя отменить, а некоторые важные данные можно получить только сразу после нажатия на кнопку и никак после (например json файл токена доступа сервисного доступа). Поэтому страаашно!!!

Но разобраться можно, попробуйте. А если не получится, то вот Пост только про токены Google Cloud.

Как дать доступ к таблице Сервисному Аккаунту

Просто беру емейл сервисного аккаунта и расшариваю доступ к электронной таблице Google Sheets, как если бы это был емейл гуглоаккаунта любого другого живого человека. И этому доступу нужно выдать права Редактора, чтобы с Сервисным аккаунтом писать в этот Google Sheet. На стороне Google Cloud ничего дополнительно делать не надо.

Уведомлять Сервисный Аккаунт тоже не обязательно - он же робот, вы чего ;)

Лимиты Google Sheets API

Google почти ко всем своим API доступ выдает БЕСПЛАТНО и без премодерации (в тестовом режиме). Однако ограничивает. К некоторым апи ограничения сильные, а к Sheets API вполне приемлемые.

Read requests per minute 300
Read requests per minute per user 60
Read requests per day Unlimited

Write requests per minute 300
Write requests per minute per user 60
Write requests per day Unlimited

Каждый execute() тратит эти лимиты, даже если запрос не успешный. Остатки лимитов нельзя узнать программно, из кода. Только посмотреть в браузере в https://console.cloud.google.com/apis/api/sheets.googleapis.com/quotas?project=<id проекта>.

Зависимости для доступа к Sheets API из Python

Мой req.txt выглядит вот так:

google-api-python-client
google-auth-httplib2
google-auth-oauthlib
oauth2client

Для Python есть пакеты специально для работы с Google Sheets API. Говорят они даже удобные и это, наверняка, так. Но я работаю не только с Sheets API, поэтому предпочитаю использовать большой и не очень удобный google-api-python-client

build ресурса для доступа к Google Sheets API на Python

В google-api-python-client есть универсальный метод googleapiclient.discovery.build , который создает ресурс для работы с любым API Google и с Sheets API в частности. Но сначала нужно куда то в проекте сложить json файл токена доступа сервисного аккаунта.

build у меня в проекте выглядит вот так:

def get_service_sacc():
    creds_json = os.path.dirname(__file__) + "/creds/sacc1.json"
    scopes = ['https://www.googleapis.com/auth/spreadsheets']

    creds_service = ServiceAccountCredentials.from_json_keyfile_name(creds_json, scopes).authorize(httplib2.Http())
    return build('sheets', 'v4', http=creds_service)

scopes - это уровень доступа, который хочет наше приложение от Google API. Для задач чтения/записи/форматирования в Google Sheet с Сервисным Аккаунтом https://www.googleapis.com/auth/spreadsheets достаточно.

Такое приложение (с Сервисным Аккаунтом) не будет ничего просить от пользователя через браузер, как было бы с OAuth авторизацией. Удобно.

Как узнать spreadsheet_id Google Sheet

Чтобы писать в электронную таблицу, нужно знать её идентификатор. Также нам может понадобиться (при форматировании уж точно) идентификатор листа в электронной таблице. В браузере это все выглядит просто:

# https://docs.google.com/spreadsheets/d/1IfE0sBAkKvhB6F8zHkEozEE0jpwhAU_G4UubwKTV1Bk/edit#gid=758897038
# https://docs.google.com/spreadsheets/d/<ID электронной таблицы (spreadsheet ID)>/edit#gid=<ID листа (sheet ID) >

Как читать из Google Sheet с API на Python?

Для чтения у нас есть два метода spreadsheets().values().get() и spreadsheets().values().batchGet() . В аргументы им передается идентификатор электронной таблицы Google Sheet и диапазон для чтения.

Диапазон для чтения - это строка вида "Лист!A1:Z999". Диапазоны, как видно, могут быть на разных листах электронной таблицы.

batchGet в отличии от get может запросить сразу, за один execute(), несколько диапазонов данных, что экономит квоты. Рекомендую сразу и везде использовать только батчевые методы.

resp = get_service_sacc().spreadsheets().values().get(spreadsheetId=sheet_id, range="Популярное!A1:A999").execute()
# resp = get_service_sacc().spreadsheets().values().batchGet(spreadsheetId=sheet_id, ranges=["Популярное", "Лист3"]).execute()

Запрос отправляется (и расходуются квоты) только по методу execute().

В ответ мы получаем json (dict), где все очевидно. В поле values двумерный массив с нужными данными.

Как писать в Google Sheet с API на Python?

Для записи есть 3 метода:

• append - пытается писать в заданный диапазон, если ячейки в заданном диапазоне заняты данными (не пусты), то пишет в ближайшую пустую ячейку ниже,

• update - пишет в заданный диапазон, если ячейки заняты данными - он эти данные перезапишет новыми,

• batchUpdate - действует как update, но может за один запрос записать несколько пачек данных в разные диапазоны и сэкономить квоты.

Данные для всех этих методов передаются в аргументе body. Для append / update структура одинакова:

body = {
    'values' : [
        ["Azzrael Code", "YouTube Channel"], # строка
        ["check it",       "RIGHT NOW !!!"], # строка
    ]
}

Т.е. values - это простой двумерный массив. А запись выглядит вот так:

resp = sheet.values().update(
    spreadsheetId=sheet_id,
    range="Лист2!H1",
    valueInputOption="RAW",
    body=body).execute()

resp = sheet.values().append(
    spreadsheetId=sheet_id,
    range="Лист2!A1",
    valueInputOption="RAW",
    body=body).execute()

range - диапазон для записи, а valueInputOption : 'RAW' - значит данные буду писать в ячейку "как есть", не пытаясь, например, в строке разглядеть дату.

Со структурой body для batchUpdate посложнее, но не сильно:

body = {
    'valueInputOption' : 'RAW',
    'data' : [
        {'range' : 'Лист2!D2', 'values' : [
            ["Azzrael Code", "YouTube Channel"],
            ["More about", "Google Sheets API"],
            ["styling", "formulas", "charts"],
        ]},
        {'range' : 'Лист2!H4', 'values' : [
            ["Azzrael Code", "YouTube Channel"],
            ["More about", "Google Sheets API"],
            ["styling", "formulas", "charts"],
        ]}
    ]
}

Нужно ли тут что-то объяснять? Напишите в комментарий, если все таки надо...

Ну а метод даже проще чем у update / append :

resp = sheet.values().batchUpdate(spreadsheetId=sheet_id, body=body).execute()

Конечно есть, особенно при записи, некоторые тонкости. Особенно когда речь заходит про форматирование - все эти строки которые вдруг становятся датами, когда не надо. Числа которые стали строками... Но что-то и так много текста вышло, поэтому пока закругляюсь.

Полезные ссылки

• Документация Google Sheets API. Доки, кстати, очень хорошие. Не все API Google так хорошо задокументированы.

• На Хабре есть хорошая статья, жаль мне поздно попалась.

• Все что в статье - есть в оформленном виде на GitHub ( темы разбросаны по веткам репозитория, просто переключайтесь ).