Довольно долго я обходился выгрузкой данных в 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.
Как видно на экране:

• Если ни одна ячейка в читаемой строке не заполнена — данные по строке не выводятся.
• Данные после последней заполненной ячейки не выводятся.