Начинаем работу с Google Sheets (Google Таблицы) на Python. От регистрации до чтения данных
Python *Google API *Из песочницыДовольно долго я обходился выгрузкой данных в Excel, но мода меняется, пользователи хотят в облака.
Начав переводить ряд проектов на Python, решил, что самое время сменить (или дополнить) Excel чем-то более современным.
Когда я впервые столкнулся с необходимостью работы c таблицами Google из Python, то пребывал в иллюзии, что все это можно сделать в пару кликов. Реальность оказалась менее радужной, но другого глобуса у нас нет.
Мне очень помогли статьи:
- Генерируем красивую Google-таблицу из своей программы (используя Google Sheets API v4)
- Начинаем работать с Google Sheets API v4/
- Работаем с API Google Drive с помощью Python
Как обычно – когда впервые за что-то берешься, то сталкиваешься с массой вопросов, которые потом вызывают лишь недоумение – как можно было об этом вообще задумываться. Элементарно же!
Возможно, я просто шел длинным путем – буду рад, если вы меня поправите.
Все действия выполнялись на компьютере с Windows + Python 3.6.6, также использовался Jupyter Notebook.
Основные трудности у меня возникали на этапе предварительных настроек. Найти работоспособный код не представляет особого труда.
Код, использованный в статье, доступен в репозитории
Регистрация в сервисах Google и установка библиотек.
Для работы с таблицами нужно зарегистрироваться на Google, настроить проект и установить необходимые библиотеки.
Официальная документация на английском языке находится здесь.
Сначала нужно зарегистрироваться на gmail.com (это вы можете сделать самостоятельно). Потом нужно создать проект (так Google предоставляет доступ к своим сервисам).
Это долгий и нудный процесс, который позволяет понять, почему интерфейсы от Google называют не самыми удобными и интуитивно понятными (некоторые считают, что социальная сеть Google+ не взлетела именно по этой причине).
Для этого зайдите на страницу console.developers.google.com/cloud-resource-manager и нажать «Создать проект»
Введите имя проекта и нажмите «Создать»
В обновленном списке проектов зайдите в меню «Права доступа»
В открывшемся окне нажмите «Добавить», внесите свой email с домена gmail.com и выберите группу «Проект» — «Владелец»
Сохраните изменения.
Может показаться странным, что вы создали проект но вынуждены сами себе выдавать права. И это на самом деле странно, но именно такой путь пришлось пройти на момент написания этого курса, чтобы все начало работать как надо.
Снова зайдите на страницу console.developers.google.com/cloud-resource-manager
Выберите на своем проекте меню «Настройки»
В открывшемся окне выберите «Сервисные аккаунты», а затем «Создать сервисный аккаунт»
Введите название аккаунта и нажмите «Создать»
Выберите роль «Владелец» и нажмите «Продолжить»
В появившемся окне нажмите «Создать ключ»
Выберите тип ключа «json» и нажмите «Создать»
Будет создан и сразу же скачан файл с ключами. Сохраните его, именно благодаря ему мы сможем получать доступ к сервисам Google.
Нажмите на кнопку с тремя горизонтальными штрихами, слева от надписи «Google APIs», выберите пункт «API и сервисы», а в нем подпункт «Панель управления».
В открывшемся окне нажмите «Включить API и сервисы»
Введите в строку поиска «google drive» и кликните на сервисе «Google Drive API»
Нажмите «Включить»
Сайт уведомит вас, что API включено и предупредит, что нужно создать учетные данные. Игнорируйте это предупреждение (ведь мы уже создали сервисный аккаунт).
Снова заходите в панель управления
В открывшемся окне нажмите «Включить API и сервисы»
Введите в строку поиска «sheet» и кликните на сервисе «Google Sheets API»
Убедитесь, что это API подключено. Оно должно включиться автоматически, при подключении Google Drive API. Если оно подключено, вы увидите кнопку «Управление API», если нет — кнопку «Включить». Включите его, при необходимости.
В последний раз зайдите на страницу console.developers.google.com/cloud-resource-manager
Выберите на своем проекте меню «Настройки»
В открывшемся окне выберите «Сервисные аккаунты», а затем скопируйте и сохраните email сервисного аккаунта. Он пригодится вам, чтобы выдавать доступ к таблицам.
Теперь переходим к установке библиотек. Выполните в консоли команду
pip3 install --upgrade google-api-python-client
а затем
pip3 install oauth2client
Возможно, что при запуске второй команды вы получите сообщение, что библиотека oauth2client уже установлена.
Зайдите на страницу raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py
Нажмите правую кнопку мышки и выберите «Сохранить как»
Сохраните файл под именем quickstart.py
и запустите его командой
python quickstart.py
Откроется новая страница в браузере (возможно, он скажет, что страница небезопасная, но смело идите вперед) и вам надо будет принять условия.
На этом наш путь завершен.
Заполнение и форматирование таблицы
Создадим первую таблицу
# Подключаем библиотеки
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials
CREDENTIALS_FILE = 'seraphic-effect-248407-7ac2c44ec709.json' # Имя файла с закрытым ключом, вы должны подставить свое
# Читаем ключи из файла
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'])
httpAuth = credentials.authorize(httplib2.Http()) # Авторизуемся в системе
service = apiclient.discovery.build('sheets', 'v4', http = httpAuth) # Выбираем работу с таблицами и 4 версию API
spreadsheet = service.spreadsheets().create(body = {
'properties': {'title': 'Первый тестовый документ', 'locale': 'ru_RU'},
'sheets': [{'properties': {'sheetType': 'GRID',
'sheetId': 0,
'title': 'Лист номер один',
'gridProperties': {'rowCount': 100, 'columnCount': 15}}}]
}).execute()
spreadsheetId = spreadsheet['spreadsheetId'] # сохраняем идентификатор файла
print('https://docs.google.com/spreadsheets/d/' + spreadsheetId)
Если все прошло без ошибок — на экран будет выведена ссылка на таблицу.
В этой ссылки использован идентификатор файла, мы сохраняем его в переменной spreadsheetId и будем использовать в дальнейшем.
Переходите по ней. Google сообщит вам, что у вас нет доступа
Не запрашивайте разрешение! Вам придет уведомление, что невозможно доставить письмо с запросом на адрес, который сам Google назначил системному аккаунту. А изменить этот адрес нельзя. Возможно, это не работает только в бесплатном режиме.
Но мы можем выдать себе доступ через Google Drive. Вам нужно заменить адрес my_test_address@gmail.com на свой.
driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) # Выбираем работу с Google Drive и 3 версию API
access = driveService.permissions().create(
fileId = spreadsheetId,
body = {'type': 'user', 'role': 'writer', 'emailAddress': 'my_test_address@gmail.com'}, # Открываем доступ на редактирование
fields = 'id'
).execute()
Теперь у вас есть доступ, не закрывайте таблицу, мы будем управлять ею и сразу же смотреть на изменения.
У каждого документа есть свой код — spreadsheetId — именно от отображается в адресной строке, когда мы открываем таблицу в браузере (в URL-е страницы с открытой таблицей он находится между «https://docs.google.com/spreadsheets/d/» и «/edit#gid=0»).
Мы сохранили его в переменной spreadsheetId и дальше будем с ним работать.
Сначала немного теории.
В каждом файле (spreadsheet) находятся листы-вкладки (sheet).
Каждый sheet имеет свой числовой код (sheetId). У первого созданного в документе листа этот Id равен 0. Остальные листы имеют сильно отличные от нуля Id (т.е. они не нумеруются подряд).
Убедимся в этом
# Добавление листа
results = service.spreadsheets().batchUpdate(
spreadsheetId = spreadsheetId,
body =
{
"requests": [
{
"addSheet": {
"properties": {
"title": "Еще один лист",
"gridProperties": {
"rowCount": 20,
"columnCount": 12
}
}
}
}
]
}).execute()
# Получаем список листов, их Id и название
spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute()
sheetList = spreadsheet.get('sheets')
for sheet in sheetList:
print(sheet['properties']['sheetId'], sheet['properties']['title'])
sheetId = sheetList[0]['properties']['sheetId']
print('Мы будем использовать лист с Id = ', sheetId)
На экране появится нечто вроде:
0 Лист номер один
415832263 Еще один лист
Мы будем использовать лист с Id = 0
В самом деле, первый лист имеет Id равный нулю, а второй пронумерован иначе.
Еще один вопрос: как указывать диапазоны ячеек. Видимо, таблицы Google разрабатывали разные команды, под руководством разных менеджеров и при помощи разных архитекторов. Потому, что координаты ячеек задаются двумя разными способами.
Вариант 1: в формате текста «Лист номер один!B2:D5», т.е. имя листа, после него восклицательный знак, после — левая верхняя ячейка в формате «буква (колонка) + цифра (строка)» + правая нижняя ячейка в таком же формате.
{"range": "Лист номер один!B2:D5"}
Вариант 2: в json-формате, с указанием ID листа и координат левой верхней и правой нижней ячеек в числовом виде (номер строки и номер столбца)
{"range":
{
"sheetId": sheetId, # ID листа
"startRowIndex": 1, # Со строки номер startRowIndex
"endRowIndex": 5,# по endRowIndex - 1 (endRowIndex не входит!)
"startColumnIndex": 0, # Со столбца номер startColumnIndex
"endColumnIndex": 1 # по endColumnIndex - 1
}}
Разные функции используют разные форматы.
Теперь мы знаем достаточно, чтобы заполнить ячейки данными, нарисовать рамку и выделить заголовки.
results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = {
"valueInputOption": "USER_ENTERED", # Данные воспринимаются, как вводимые пользователем (считается значение формул)
"data": [
{"range": "Лист номер один!B2:D5",
"majorDimension": "ROWS", # Сначала заполнять строки, затем столбцы
"values": [
["Ячейка B2", "Ячейка C2", "Ячейка D2"], # Заполняем первую строку
['25', "=6*6", "=sin(3,14/2)"] # Заполняем вторую строку
]}
]
}).execute()
Заполняем несколько ячеек данными. Т.к. указан параметр USER_ENTERED, таблица воспринимает эти данные так, как восприняла бы ввод руками пользователя — преобразует числовые значения в числа, а значения, начинающиеся со знака «равно» в формулы.
Посмотрите в вашу таблицу, она заполнилась данными
Зададим ширину колонок. Функция batchUpdate может принимать несколько команд сразу, так что мы одним запросом установим ширину трех групп колонок. В первой и третьей группе одна колонка, а во второй — две.
results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheetId, body = {
"requests": [
# Задать ширину столбца A: 20 пикселей
{
"updateDimensionProperties": {
"range": {
"sheetId": sheetId,
"dimension": "COLUMNS", # Задаем ширину колонки
"startIndex": 0, # Нумерация начинается с нуля
"endIndex": 1 # Со столбца номер startIndex по endIndex - 1 (endIndex не входит!)
},
"properties": {
"pixelSize": 20 # Ширина в пикселях
},
"fields": "pixelSize" # Указываем, что нужно использовать параметр pixelSize
}
},
# Задать ширину столбцов B и C: 150 пикселей
{
"updateDimensionProperties": {
"range": {
"sheetId": sheetId,
"dimension": "COLUMNS",
"startIndex": 1,
"endIndex": 3
},
"properties": {
"pixelSize": 150
},
"fields": "pixelSize"
}
},
# Задать ширину столбца D: 200 пикселей
{
"updateDimensionProperties": {
"range": {
"sheetId": sheetId,
"dimension": "COLUMNS",
"startIndex": 3,
"endIndex": 4
},
"properties": {
"pixelSize": 200
},
"fields": "pixelSize"
}
}
]
}).execute()
Посмотрите на таблицу, ширины колонок изменились.
Нарисуем рамку вокруг таблицы
# Рисуем рамку
results = service.spreadsheets().batchUpdate(
spreadsheetId = spreadsheetId,
body = {
"requests": [
{'updateBorders': {'range': {'sheetId': sheetId,
'startRowIndex': 1,
'endRowIndex': 3,
'startColumnIndex': 1,
'endColumnIndex': 4},
'bottom': {
# Задаем стиль для верхней границы
'style': 'SOLID', # Сплошная линия
'width': 1, # Шириной 1 пиксель
'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, # Черный цвет
'top': {
# Задаем стиль для нижней границы
'style': 'SOLID',
'width': 1,
'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}},
'left': { # Задаем стиль для левой границы
'style': 'SOLID',
'width': 1,
'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}},
'right': {
# Задаем стиль для правой границы
'style': 'SOLID',
'width': 1,
'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}},
'innerHorizontal': {
# Задаем стиль для внутренних горизонтальных линий
'style': 'SOLID',
'width': 1,
'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}},
'innerVertical': {
# Задаем стиль для внутренних вертикальных линий
'style': 'SOLID',
'width': 1,
'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}
}}
]
}).execute()
Объединим ячейки над таблицей и впишем в них заголовок
# Объединяем ячейки A2:D1
results = service.spreadsheets().batchUpdate(
spreadsheetId = spreadsheetId,
body = {
"requests": [
{'mergeCells': {'range': {'sheetId': sheetId,
'startRowIndex': 0,
'endRowIndex': 1,
'startColumnIndex': 1,
'endColumnIndex': 4},
'mergeType': 'MERGE_ALL'}}
]
}).execute()
# Добавляем заголовок таблицы
results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = {
"valueInputOption": "USER_ENTERED",
# Данные воспринимаются, как вводимые пользователем (считается значение формул)
"data": [
{"range": "Лист номер один!B1",
"majorDimension": "ROWS", # Сначала заполнять строки, затем столбцы
"values": [["Заголовок таблицы" ]
]}
]
}).execute()
Установим формат у ячеек заголовка таблицы
# Установка формата ячеек
results = service.spreadsheets().batchUpdate(
spreadsheetId = spreadsheetId,
body =
{
"requests":
[
{
"repeatCell":
{
"cell":
{
"userEnteredFormat":
{
"horizontalAlignment": 'CENTER',
"backgroundColor": {
"red": 0.8,
"green": 0.8,
"blue": 0.8,
"alpha": 1
},
"textFormat":
{
"bold": True,
"fontSize": 14
}
}
},
"range":
{
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": 2,
"startColumnIndex": 1,
"endColumnIndex": 4
},
"fields": "userEnteredFormat"
}
}
]
}).execute()
Есть простой способ узнать, какую ширину или цвет нужно задать ячейке. Для этого достаточно вручную отформатировать одну из ячеек и прочитать ее свойства.
ranges = ["Лист номер один!C2:C2"] #
results = service.spreadsheets().get(spreadsheetId = spreadsheetId,
ranges = ranges, includeGridData = True).execute()
print('Основные данные')
print(results['properties'])
print('\nЗначения и раскраска')
print(results['sheets'][0]['data'][0]['rowData'] )
print('\nВысота ячейки')
print(results['sheets'][0]['data'][0]['rowMetadata'])
print('\nШирина ячейки')
print(results['sheets'][0]['data'][0]['columnMetadata'])
Получаем в ответ
Основные данные
{‘title’: ‘Первый тестовый документ’, ‘locale’: ‘ru_RU’, ‘autoRecalc’: ‘ON_CHANGE’, ‘timeZone’: ‘Etc/GMT’, ‘defaultFormat’: {‘backgroundColor’: {‘red’: 1, ‘green’: 1, ‘blue’: 1}, ‘padding’: {‘top’: 2, ‘right’: 3, ‘bottom’: 2, ‘left’: 3}, ‘verticalAlignment’: ‘BOTTOM’, ‘wrapStrategy’: ‘OVERFLOW_CELL’, ‘textFormat’: {‘foregroundColor’: {}, ‘fontFamily’: ‘arial,sans,sans-serif’, ‘fontSize’: 10, ‘bold’: False, ‘italic’: False, ‘strikethrough’: False, ‘underline’: False}}}Значения и раскраска
[{‘values’: [{‘userEnteredValue’: {‘stringValue’: ‘Ячейка C2’}, ‘effectiveValue’: {‘stringValue’: ‘Ячейка C2’}, ‘formattedValue’: ‘Ячейка C2’, ‘userEnteredFormat’: {‘backgroundColor’: {‘red’: 1, ‘green’: 0.6}, ‘horizontalAlignment’: ‘CENTER’, ‘textFormat’: {‘fontSize’: 14, ‘bold’: True, ‘italic’: True}}, ‘effectiveFormat’: {‘backgroundColor’: {‘red’: 1, ‘green’: 0.6}, ‘padding’: {‘top’: 2, ‘right’: 3, ‘bottom’: 2, ‘left’: 3}, ‘horizontalAlignment’: ‘CENTER’, ‘verticalAlignment’: ‘BOTTOM’, ‘wrapStrategy’: ‘OVERFLOW_CELL’, ‘textFormat’: {‘foregroundColor’: {}, ‘fontFamily’: ‘Arial’, ‘fontSize’: 14, ‘bold’: True, ‘italic’: True, ‘strikethrough’: False, ‘underline’: False}, ‘hyperlinkDisplayType’: ‘PLAIN_TEXT’}}]}]Высота ячейки
[{‘pixelSize’: 21}]Ширина ячейки
[{‘pixelSize’: 150}]
Этот код выведет свойства ячейки C2. Можно выбрать шрифт и цвет заливки вручную (в таблице), в потом увидеть, как они отражаются в json.
Чтение данных из таблицы
Чтобы особенности чтения данных проявились в полной мере, я вручную заполнил ячейки B4, C7 и D5 как показано на рисунке.
Код для чтения данных
ranges = ["Лист номер один!A2:F8"] #
results = service.spreadsheets().values().batchGet(spreadsheetId = spreadsheetId,
ranges = ranges,
valueRenderOption = 'FORMATTED_VALUE',
dateTimeRenderOption = 'FORMATTED_STRING').execute()
sheet_values = results['valueRanges'][0]['values']
print(sheet_values)
Результат
[[», ‘Ячейка B2’, ‘Ячейка C2’, ‘Ячейка D2′], [», ’25’, ’36’, ‘0,9999996829’]]
Некоторые параметры функции: valueRenderOption — формат чтения числовых данных.
- FORMATTED_VALUE — чтение с учетом формата отображения. Т.е. что было видно в таблице, то и прочитается. Например, в ячейке D3 число 0,9999999, но выбран формат «два знака после запятой», поэтому отображается «1,00», именно в таком формате оно и прочитается.
- UNFORMATTED_VALUE — читается содержимое ячейки, без учета настроек форматирование (т.е. прочиталось бы 0,9999999)
- FORMULA — отображается формула (в этом случае «=sin(3,14/2)». Если в ячейке введено число, то в этом режиме оно и прочитается.
Этот код читает данные и построчно выводит их на экран. Читаемый диапазон A2:F8.
Как видно на экране:
- Если ни одна ячейка в читаемой строке не заполнена — данные по строке не выводятся.
- Данные после последней заполненной ячейки не выводятся.