Генерируем красивую Google-таблицу из своей программы (используя Google Sheets API v4)09.08.2016 11:48

Постановка задачи

Особенности этой таблицы:

• задана ширина столбцов;
• верхняя ячейка является объединением A1: E1;
• в некоторых ячейках настроены: формат отображения, размер шрифта, жирность, выравнивание текста и цвет фона;
• значения в последнем столбике вычислены формулой (например, в E4 написано =D4-C4);
• нарисована граница под ячейками A3: E3;
• присутствует Пикачу (но это останется как домашнее задание для энтузиастов).

Решение

Будем использовать Google Sheets API v4.

Шаг 1. Создать сервисный аккаунт

1. Зайти в Google Developers Console и создать новый проект (либо использовать какой-то их тех, что уже есть).
2. Включить для этого проекта Drive API и Sheets API.
3. Создать учётные данные и сохранить закрытый ключ:

   

Шаг 2. Установить необходимые библиотеки

pip install --upgrade google-api-python-client

Шаг 3. Кодить

3.1. Service-объект

import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials

CREDENTIALS_FILE = 'test-proj-for-habr-article-1ab131d98a6b.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)

3.2. Термины и id’шники

• spreadsheet — это Google-документ с таблицами. Ниже буду называть документ (либо английским названием).
  У него есть spreadsheetId, имеющий вид 1kygOW5wSSVqwf26M-OCT72i0FX0olZAz4duT2i6psp4.
• sheet — это лист внутри spreadsheet. Иначе говоря, вкладка с одной из таблиц (их может быть несколько внутри одного документа).
  У sheet есть sheetId, являющийся числом. У первого созданного в документе листа id равен 0. В документе всегда есть как минимум один лист (удалить его не получится). Все листы имеют разные id и разные названия.
  
  Историческая справка про worksheet

  В старом API лист называется worksheet. У него есть worksheetId (или wid), имеющий вид oowy6v0. Для конвертации в число нужно особое извращение:
  

  wid2sheetId = lambda wid: int(wid[1:] if len(wid) > 3 else wid, 36) ^ (474 if len(wid) > 3 else 31578)
  

3.3. Новый spreadsheet

spreadsheet = service.spreadsheets().create(body = {
    'properties': {'title': 'Сие есть название документа', 'locale': 'ru_RU'},
    'sheets': [{'properties': {'sheetType': 'GRID',
                               'sheetId': 0,
                               'title': 'Сие есть название листа',
                               'gridProperties': {'rowCount': 8, 'columnCount': 5}}}]
}).execute()

{'properties': {'autoRecalc': 'ON_CHANGE',
                'defaultFormat': {'backgroundColor': {'blue': 1,
                                                      'green': 1,
                                                      'red': 1},
                                  'padding': {'bottom': 2,
                                              'left': 3,
                                              'right': 3,
                                              'top': 2},
                                  'textFormat': {'bold': False,
                                                 'fontFamily': 'arial,sans,sans-serif',
                                                 'fontSize': 10,
                                                 'foregroundColor': {},
                                                 'italic': False,
                                                 'strikethrough': False,
                                                 'underline': False},
                                  'verticalAlignment': 'BOTTOM',
                                  'wrapStrategy': 'OVERFLOW_CELL'},
                'locale': 'ru_RU',
                'timeZone': 'Etc/GMT',
                'title': 'Сие есть название документа'},
 'sheets': [{'properties': {'gridProperties': {'columnCount': 5,
                                               'rowCount': 8},
                            'index': 0,
                            'sheetId': 0,
                            'sheetType': 'GRID',
                            'title': 'Сие есть название листа'}}],
 'spreadsheetId': '1Sfl7EQ0Yuyo65INidt4LCrHMzFI9wrmc96qHq6EEqHM'}

В ответе содержится spreadsheetId. Ура! Идём смотреть глазами на созданный документ… и обламываемся, потому что доступа к нему у нас нет. Даже на чтение. Всё как у обычной свежесозданной пользователем вручную Google-таблицы.
А у кого доступ? У сервисного аккаунта.

3.4. Доступ к новому документу

driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth)
shareRes = driveService.permissions().create(
    fileId = spreadsheet['spreadsheetId'],
    body = {'type': 'anyone', 'role': 'reader'},  # доступ на чтение кому угодно
    fields = 'id'
).execute()

{'type': 'anyone', 'role': 'reader'}

{'type': 'user', 'role': 'writer', 'emailAddress': 'user@example.com'}

3.5. Ещё немного теории

Есть функция spreadsheets.batchUpdate. Она применяет сразу пачку изменений к документу. А точнее, сначала она проверяет всю пачку на корректность. Если всё OK, то атомарно применяет всё и возвращает соответствующую пачку результатов. Список изменений, которые можно применять этой функцией, находится здесь.

3.6. Ширина столбцов

results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = {
  "requests": [

    # Задать ширину столбца A: 317 пикселей
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",  # COLUMNS - потому что столбец
          "startIndex": 0,         # Столбцы нумеруются с нуля
          "endIndex": 1            # startIndex берётся включительно, endIndex - НЕ включительно,
                                   # т.е. размер будет применён к столбцам в диапазоне [0,1), т.е. только к столбцу A
        },
        "properties": {
          "pixelSize": 317     # размер в пикселях
        },
        "fields": "pixelSize"  # нужно задать только pixelSize и не трогать другие параметры столбца
      }
    },

    # Задать ширину столбца B: 200 пикселей
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",
          "startIndex": 1,
          "endIndex": 2
        },
        "properties": {
          "pixelSize": 200
        },
        "fields": "pixelSize"
      }
    },

    # Задать ширину столбцов C и D: 165 пикселей
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",
          "startIndex": 2,
          "endIndex": 4
        },
        "properties": {
          "pixelSize": 165
        },
        "fields": "pixelSize"
      }
    },

    # Задать ширину столбца E: 100 пикселей
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": 0,
          "dimension": "COLUMNS",
          "startIndex": 4,
          "endIndex": 5
        },
        "properties": {
          "pixelSize": 100
        },
        "fields": "pixelSize"
      }
    }
  ]
}).execute()

3.7. Логика класса-обёртки

Теперь код для задания ширины столбцов выглядит так:

# ss - экземпляр нашего класса Spreadsheet
ss.prepare_setColumnWidth(0, 317)
ss.prepare_setColumnWidth(1, 200)
ss.prepare_setColumnsWidth(2, 3, 165)
ss.prepare_setColumnWidth(4, 100)
ss.runPrepared()

class Spreadsheet:

    # ...

    def prepare_setDimensionPixelSize(self, dimension, startIndex, endIndex, pixelSize):
        self.requests.append({"updateDimensionProperties": {
            "range": {"sheetId": self.sheetId,
                      "dimension": dimension,
                      "startIndex": startIndex,
                      "endIndex": endIndex},
            "properties": {"pixelSize": pixelSize},
            "fields": "pixelSize"}})

    def prepare_setColumnsWidth(self, startCol, endCol, width):
        self.prepare_setDimensionPixelSize("COLUMNS", startCol, endCol + 1, width)

    def prepare_setColumnWidth(self, col, width):
        self.prepare_setColumnsWidth(col, col, width)

3.8. Заполнение ячеек данными

• если USER_ENTERED, то данные интерпретируются, как вводимые пользователем;
• если RAW, то никак не интерпретируются и сохраняются в сыром виде.

Вот так можно заполнить данными пару прямоугольников на листе без использования нашего класса-обёртки:

results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = {
    "valueInputOption": "USER_ENTERED",
    "data": [
        {"range": "Сие есть название листа!B2:C3",
         "majorDimension": "ROWS",     # сначала заполнять ряды, затем столбцы (т.е. самые внутренние списки в values - это ряды)
         "values": [["This is B2", "This is C2"], ["This is B3", "This is C3"]]},

        {"range": "Сие есть название листа!D5:E6",
         "majorDimension": "COLUMNS",  # сначала заполнять столбцы, затем ряды (т.е. самые внутренние списки в values - это столбцы)
         "values": [["This is D5", "This is D6"], ["This is E5", "=5+5"]]}
    ]
}).execute()

Теперь сделаем, чтобы наш класс-обёртка предоставил удобные методы для достижения того же результата.
Пусть функция spreadsheets.values.batchUpdate вызывается в методе runPrepared, а метод prepare_setValues добавляет прямоугольник и данные в список valueRanges, который при вызове runPrepared будет передан в spreadsheets.values.batchUpdate.

Код методов prepare_setValues и runPrepared:

class Spreadsheet:

    # ...

    def prepare_setValues(self, cellsRange, values, majorDimension = "ROWS"):
        self.valueRanges.append({"range": self.sheetTitle + "!" + cellsRange, "majorDimension": majorDimension, "values": values})

    # spreadsheets.batchUpdate and spreadsheets.values.batchUpdate
    def runPrepared(self, valueInputOption = "USER_ENTERED"):
        upd1Res = {'replies': []}
        upd2Res = {'responses': []}
        try:
            if len(self.requests) > 0:
                upd1Res = self.service.spreadsheets().batchUpdate(spreadsheetId = self.spreadsheetId,
                                                                  body = {"requests": self.requests}).execute()
            if len(self.valueRanges) > 0:
                upd2Res = self.service.spreadsheets().values().batchUpdate(spreadsheetId = self.spreadsheetId,
                                                                           body = {"valueInputOption": valueInputOption,
                                                                                   "data": self.valueRanges}).execute()
        finally:
            self.requests = []
            self.valueRanges = []
        return (upd1Res['replies'], upd2Res['responses'])

# ss - экземпляр нашего класса Spreadsheet
ss.prepare_setValues("B2:C3", [["This is B2", "This is C2"], ["This is B3", "This is C3"]])
ss.prepare_setValues("D5:E6", [["This is D5", "This is D6"], ["This is E5", "=5+5"]], "COLUMNS")
ss.runPrepared()

3.9. Объединение ячеек, настройка жирности, формата отображения, цвета фона и прочего

Для более терпеливого читателя:

• MergeCellsRequest — объединить ячейки.
  
  Пример

  # Объединить ячейки A1:E1
  {'mergeCells': {'range': {'sheetId': 0,
                            'startRowIndex': 0,
                            'endRowIndex': 1,
                            'startColumnIndex': 0,
                            'endColumnIndex': 5},
                  'mergeType': 'MERGE_ALL'}}
  
  # Подготовить такой запрос при помощи класса Spreadsheet
  ss.prepare_mergeCells('A1:E1')
  

• RepeatCellRequest — применить одинаковые изменения ко всем ячейкам в указанном диапазоне.
  
  Примеры

  # Сделать жирными и выровнять по центру ячейки A3:E3
  {'repeatCell': {'range': {'sheetId': 0,
                            'startRowIndex': 2,
                            'endRowIndex': 3,
                            'startColumnIndex': 0,
                            'endColumnIndex': 5},
                  'cell': {'userEnteredFormat': {'horizontalAlignment': 'CENTER',
                                                 'textFormat': {'bold': True}}},
                  'fields': 'userEnteredFormat'}}  # другие параметры форматирования ячейки будут сброшены
  
  # Формат отображения «продолжительность» для ячеек E4:E8
  {'repeatCell': {'range': {'sheetId': 0,
                            'startRowIndex': 3,
                            'endRowIndex': 8,
                            'startColumnIndex': 4,
                            'endColumnIndex': 5},
                  'cell': {'userEnteredFormat': {'numberFormat': {'pattern': '[h]:mm:ss',
                                                                  'type': 'TIME'}}},
                  'fields': 'userEnteredFormat.numberFormat'}}  # для ячейки изменится только формат отображения
  
  # Подготовить такие запросы при помощи класса Spreadsheet
  ss.prepare_setCellsFormat('A3:E3', {'horizontalAlignment': 'CENTER', 'textFormat': {'bold': True}})
  ss.prepare_setCellsFormat('E4:E8', {'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}},
                            fields = 'userEnteredFormat.numberFormat')
  

• UpdateCellsRequest — применить заданные для каждой ячейки изменения в указанном диапазоне.
  
  Пример

  # Задать цвет фона ячеек следующим образом: B4 - красный, C4 - зелёный, B5 - синий, C5 - жёлтый
  {'updateCells': {'range': {'sheetId': 0,
                             'startRowIndex': 3,
                             'endRowIndex': 5,
                             'startColumnIndex': 1,
                             'endColumnIndex': 3},
                   'rows': [{'values': [{'userEnteredFormat': {'backgroundColor': {'red': 1, 'green': 0, 'blue': 0}}},
                                        {'userEnteredFormat': {'backgroundColor': {'red': 0, 'green': 1, 'blue': 0}}}]},
                            {'values': [{'userEnteredFormat': {'backgroundColor': {'red': 0, 'green': 0, 'blue': 1}}},
                                        {'userEnteredFormat': {'backgroundColor': {'red': 1, 'green': 1, 'blue': 0}}}]}],
                   'fields': 'userEnteredFormat'}}
  
  # Подготовить такой запрос при помощи класса Spreadsheet
  ss.prepare_setCellsFormats('B4:C5', [[{'backgroundColor': {'red': 1, 'green': 0, 'blue': 0}},
                                        {'backgroundColor': {'red': 0, 'green': 1, 'blue': 0}}],
                                       [{'backgroundColor': {'red': 0, 'green': 0, 'blue': 1}},
                                        {'backgroundColor': {'red': 1, 'green': 1, 'blue': 0}}]])
  

• UpdateBordersRequest — задать границу ячеек.
  
  Пример

  # Чёрная сплошная граница толщиной 1 под A3:E3
  {'updateBorders': {'range': {'sheetId': 0,
                               'startRowIndex': 2,
                               'endRowIndex': 3,
                               'startColumnIndex': 0,
                               'endColumnIndex': 5},
                     'bottom': {'style': 'SOLID',
                                'width': 1,
                                'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}}}
  

Некоторые тонкости

Q2: Откуда получено, что формат «продолжительность» это {'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}}?

A2: До этого можно докопаться, изучая документацию. Но я просто вручную задал конкретной ячейке формат отображения «продолжительность», а затем получил документ программой с помощью функции spreadsheets.get, задав параметру includeGridData значение True, и посмотрел, какое значение параметра numberFormat у той ячейки.

Q3: В запросах, передаваемых функции spreadsheets.batchUpdate, параметр range имеет формат GridRange:

{
  'sheetId': число,
  'startRowIndex': число,
  'endRowIndex': число,
  'startColumnIndex': число,
  'endColumnIndex': число
}

Q4: Пикачу, который в таблице в начале статьи, посажен туда программно?
A4: Нет, Пикачу я разместил в таблице вручную. Не уверен, что Google Sheets API v4 позволяет сделать это программно, сходу нужную функцию не нашёл.

Q5: Есть ли какие-то ограничения использования Google Sheets API v4?
A5: Да, они называются квотами. За ними можно следить в Google Developers Console. Там же можно отправить запрос на увеличение квоты, если будет не хватать.

Заключение

Приведу повторно самые важные ссылки:

• Google Sheets API v4
• Google Developers Console
• Класс-обёртка Spreadsheet и примеры его использования

Комментарии (0)