Начинаем работу с Google Sheets на Python. От регистрации до чтения данных
Довольно долго я обходился выгрузкой данных в Excel, но мода меняется, пользователи хотят в облака.
Начав переводить ряд проектов на Python, решил, что самое время сменить (или дополнить) Excel чем-то более современным.
Когда я впервые столкнулся с необходимостью работы c таблицами Google из 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 и выберите группу «Проект» — «Владелец»
Может показаться странным, что вы создали проект но вынуждены сами себе выдавать права. И это на самом деле странно, но именно такой путь пришлось пройти на момент написания этого курса, чтобы все начало работать как надо.
Выберите на своем проекте меню «Настройки»
В открывшемся окне выберите «Сервисные аккаунты», а затем «Создать сервисный аккаунт»
Введите название аккаунта и нажмите «Создать»
Выберите роль «Владелец» и нажмите «Продолжить»
В появившемся окне нажмите «Создать ключ»
Выберите тип ключа «json» и нажмите «Создать»
Будет создан и сразу же скачан файл с ключами. Сохраните его, именно благодаря ему мы сможем получать доступ к сервисам Google.
Нажмите на кнопку с тремя горизонтальными штрихами, слева от надписи «Google APIs», выберите пункт «API и сервисы», а в нем подпункт «Панель управления».
В открывшемся окне нажмите «Включить API и сервисы»
Введите в строку поиска «google drive» и кликните на сервисе «Google Drive API»
Сайт уведомит вас, что API включено и предупредит, что нужно создать учетные данные. Игнорируйте это предупреждение (ведь мы уже создали сервисный аккаунт).
Снова заходите в панель управления
В открывшемся окне нажмите «Включить API и сервисы»
Введите в строку поиска «sheet» и кликните на сервисе «Google Sheets API»
Убедитесь, что это API подключено. Оно должно включиться автоматически, при подключении Google Drive API. Если оно подключено, вы увидите кнопку «Управление API», если нет — кнопку «Включить». Включите его, при необходимости.
Выберите на своем проекте меню «Настройки»
В открывшемся окне выберите «Сервисные аккаунты», а затем скопируйте и сохраните email сервисного аккаунта. Он пригодится вам, чтобы выдавать доступ к таблицам.
Теперь переходим к установке библиотек. Выполните в консоли команду
pip3 install --upgrade google-api-python-clientpip3 install oauth2clientВозможно, что при запуске второй команды вы получите сообщение, что библиотека oauth2client уже установлена.
Нажмите правую кнопку мышки и выберите «Сохранить как»
Сохраните файл под именем 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': , 'sheets': [>>] >).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 = , # Открываем доступ на редактирование 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('Мы будем использовать лист с sheetId) На экране появится нечто вроде:
0 Лист номер один
415832263 Еще один лист
Мы будем использовать лист с >
В самом деле, первый лист имеет Id равный нулю, а второй пронумерован иначе.
Еще один вопрос: как указывать диапазоны ячеек. Видимо, таблицы Google разрабатывали разные команды, под руководством разных менеджеров и при помощи разных архитекторов. Потому, что координаты ячеек задаются двумя разными способами.
Вариант 1: в формате текста «Лист номер один!B2:D5», т.е. имя листа, после него восклицательный знак, после — левая верхняя ячейка в формате «буква (колонка) + цифра (строка)» + правая нижняя ячейка в таком же формате.
Вариант 2: в json-формате, с указанием ID листа и координат левой верхней и правой нижней ячеек в числовом виде (номер строки и номер столбца)
Разные функции используют разные форматы.
Теперь мы знаем достаточно, чтобы заполнить ячейки данными, нарисовать рамку и выделить заголовки.
results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = < "valueInputOption": "USER_ENTERED", # Данные воспринимаются, как вводимые пользователем (считается значение формул) "data": [ ] >).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": [ , 'bottom': < # Задаем стиль для верхней границы 'style': 'SOLID', # Сплошная линия 'width': 1, # Шириной 1 пиксель 'color': >, # Черный цвет 'top': < # Задаем стиль для нижней границы 'style': 'SOLID', 'width': 1, 'color': >, 'left': < # Задаем стиль для левой границы 'style': 'SOLID', 'width': 1, 'color': >, 'right': < # Задаем стиль для правой границы 'style': 'SOLID', 'width': 1, 'color': >, 'innerHorizontal': < # Задаем стиль для внутренних горизонтальных линий 'style': 'SOLID', 'width': 1, 'color': >, 'innerVertical': < # Задаем стиль для внутренних вертикальных линий 'style': 'SOLID', 'width': 1, 'color': > >> ] >).execute() 
Объединим ячейки над таблицей и впишем в них заголовок
# Объединяем ячейки A2:D1 results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = < "requests": [ , 'mergeType': 'MERGE_ALL'>> ] >).execute() # Добавляем заголовок таблицы results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = < "valueInputOption": "USER_ENTERED", # Данные воспринимаются, как вводимые пользователем (считается значение формул) "data": [ ] >).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']) Значения и раскраска
[, ‘effectiveValue’: , ‘formattedValue’: ‘Ячейка C2’, ‘userEnteredFormat’: , ‘horizontalAlignment’: ‘CENTER’, ‘textFormat’: >, ‘effectiveFormat’: , ‘padding’: , ‘horizontalAlignment’: ‘CENTER’, ‘verticalAlignment’: ‘BOTTOM’, ‘wrapStrategy’: ‘OVERFLOW_CELL’, ‘textFormat’: , ‘fontFamily’: ‘Arial’, ‘fontSize’: 14, ‘bold’: True, ‘italic’: True, ‘strikethrough’: False, ‘underline’: False>, ‘hyperlinkDisplayType’: ‘PLAIN_TEXT’>>]>]
Этот код выведет свойства ячейки 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)Некоторые параметры функции: valueRenderOption — формат чтения числовых данных.
- FORMATTED_VALUE — чтение с учетом формата отображения. Т.е. что было видно в таблице, то и прочитается. Например, в ячейке D3 число 0,9999999, но выбран формат «два знака после запятой», поэтому отображается «1,00», именно в таком формате оно и прочитается.
- UNFORMATTED_VALUE — читается содержимое ячейки, без учета настроек форматирование (т.е. прочиталось бы 0,9999999)
- FORMULA — отображается формула (в этом случае «=sin(3,14/2)». Если в ячейке введено число, то в этом режиме оно и прочитается.
- Если ни одна ячейка в читаемой строке не заполнена — данные по строке не выводятся.
- Данные после последней заполненной ячейки не выводятся.