Google Sheet и Sheets API - классный (простой, бесплатный и универсальный) способ организовать хранение и анализ данных получаемых от своего сервиса. При условии что этих данных не то чтобы очень много и поступают они не то чтобы очень часто.
У меня таких задач хватает и решаю я их либо на Python, либо на PHP. Сегодня возьму в руки Python.
Доступ к Google API
Чтобы писать из кода в Google Sheet нам нужен доступ к Google Sheets API, которое входит в библиотеку API Google Cloud Platform. Для этого нужно:- нужно создать проект в Google Cloud Platform
- подключить к проекту в Google Cloud Platform Sheets API
- заполнить OAuth consent screen ( пользовательское соглашение )
- создать либо OAuth Client ID, либо Service Account
- я использую Сервисный Аккаунт, следовательно мне нужно дать сервисному аккаунту доступ к Google Sheet (электронной таблице) с уровнем Редактор, тогда я смогу писать из кода в эту таблицу
- получить JSON файл с токеном доступа к сервисному аккаунту и положить его куда-то откуда код сможет его читать
Но разобраться можно, попробуйте. А если не получится, то вот Пост только про токены Google Cloud.
Как дать доступ к таблице Сервисному Аккаунту
Просто беру емейл сервисного аккаунта и расшариваю доступ к электронной таблице Google Sheets, как если бы это был емейл гуглоаккаунта любого другого живого человека. И этому доступу нужно выдать права Редактора, чтобы с Сервисным аккаунтом писать в этот Google Sheet. На стороне Google Cloud ничего дополнительно делать не надо.
Уведомлять Сервисный Аккаунт тоже не обязательно - он же робот, вы чего
Лимиты Google Sheets API
Google почти ко всем своим API доступ выдает БЕСПЛАТНО и без премодерации (в тестовом режиме). Однако ограничивает. К некоторым апи ограничения сильные, а к Sheets API вполне приемлемые.Каждый 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 достаточно.
Как узнать spreadsheet_id Google Sheet
Чтобы писать в электронную таблицу, нужно знать её идентификатор. Также нам может понадобиться (при форматировании уж точно) идентификатор листа в электронной таблице. В браузере это все выглядит просто:# https://docs.google.com/spreadsheet...kEozEE0jpwhAU_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 = {
'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 ( темы разбросаны по веткам репозитория, просто переключайтесь ).
Google Sheets API + Python. Чтение и запись с Сервисным Аккаунтом Google Cloud
Google Sheet и Sheets API - классный (простой, бесплатный и универсальный) способ организовать хранение и анализ данных получаемых от своего сервиса. При условии что этих данных не то чтобы очень...
habr.com