Постановка задачи
Пусть нам нужно создать программой на языке Python вот такую таблицу:
Особенности этой таблицы:
- задана ширина столбцов;
- верхняя ячейка является объединением A1:E1;
- в некоторых ячейках настроены: формат отображения, размер шрифта, жирность, выравнивание текста и цвет фона;
- значения в последнем столбике вычислены формулой (например, в E4 написано =D4-C4);
- нарисована граница под ячейками A3:E3;
- присутствует Пикачу (но это останется как домашнее задание для энтузиастов).
Интересно? Тогда добро пожаловать под кат.
Решение
Сразу отметаем неподходящие библиотеки. Например, gspread. Это обёртка над Google Sheets API v3, в котором нет методов для настройки оформления таблицы. Даже ширину столбца задать не получится.
Будем использовать Google Sheets API v4.
Шаг 1. Создать сервисный аккаунт
- Зайти в Google Developers Console и создать новый проект (либо использовать какой-то их тех, что уже есть).
- Включить для этого проекта Drive API и Sheets API.
- Создать учётные данные и сохранить закрытый ключ:
Шаг 2. Установить необходимые библиотеки
А именно, google-api-python-client. Установить можно при помощи pip, например:
pip install --upgrade google-api-python-client
Эта библиотека притянет необходимые зависимости (такие, как oauth2client и прочие).
Шаг 3. Кодить
3.1. Service-объект
Импортируем необходимое:
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials
Создаём Service-объект, для работы с Google-таблицами:
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)
Ссылка на конкретный лист формируется следующим образом:
https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId
Если отбросить #gid=sheetId, то по ссылке откроется первый лист в документе.
3.3. Новый spreadsheet
Вернёмся к коду. Объект service, который мы создали, даёт нам всего 9 функций. Одна из них называется spreadsheets.create, она создаёт новый spreadsheet. В качестве аргумента нужно передать объект Spreadsheet. Заполнять все его поля не требуется, у большинства есть значения по умолчанию.
spreadsheet = service.spreadsheets().create(body = {
'properties': {'title': 'Сие есть название документа', 'locale': 'ru_RU'},
'sheets': [{'properties': {'sheetType': 'GRID',
'sheetId': 0,
'title': 'Сие есть название листа',
'gridProperties': {'rowCount': 8, 'columnCount': 5}}}]
}).execute()
В ответ получаем снова объект Spreadsheet, только заполненных параметров больше:
{'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'}
Можно было задать многие из них в запросе, но для решения текущей задачи нас устраивают параметры по умолчанию.
Параметру locale было задано значение ru_RU не случайно, но об этом позже.
В ответе содержится spreadsheetId. Ура! Идём смотреть глазами на созданный документ… и обламываемся, потому что доступа к нему у нас нет. Даже на чтение. Всё как у обычной свежесозданной пользователем вручную Google-таблицы.
А у кого доступ? У сервисного аккаунта.
Что же делать? Ответ очевиден: выдать доступ к документу тоже с помощью API.
Меня этот вариант не устроил, потому что мне требовалось научить программу создавать много разных документов.
3.4. Доступ к новому документу
У нашего объекта service нет метода для настройки доступа к документу. Его просто нет в Google Sheets API. Зато он есть в Google Drive API v3. Пишем код.
driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth)
shareRes = driveService.permissions().create(
fileId = spreadsheet['spreadsheetId'],
body = {'type': 'anyone', 'role': 'reader'}, # доступ на чтение кому угодно
fields = 'id'
).execute()
Такой код даёт доступ всем на чтение по ссылке.
Допустим, мы желаем вместо этого дать доступ на редактирование пользователю user@example.com. Для этого вместо
{'type': 'anyone', 'role': 'reader'}пишем
{'type': 'user', 'role': 'writer', 'emailAddress': 'user@example.com'}3.5. Ещё немного теории
Начнём оформление таблицы с выставления ширины столбцов. Ой, а где такая функция? Всё не так прозрачно и чуть более умно, чем просто какой-нибудь setColumnWidth.
Есть функция spreadsheets.batchUpdate. Она применяет сразу пачку изменений к документу. А точнее, сначала она проверяет всю пачку на корректность. Если всё OK, то атомарно применяет всё и возвращает соответствующую пачку результатов. Список изменений, которые можно применять этой функцией, находится здесь.
3.6. Ширина столбцов
Чтобы задать ширину столбцов нужно сделать UpdateDimensionPropertiesRequest.
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()
Получилось весьма громоздко и много копипасты. На этом этапе я решил написать небольшой класс-обёртку над Sheets API, который даст мне в удобном виде необходимые методы.
3.7. Логика класса-обёртки
Пусть класс-обёртка (назовём его Spreadsheet) хранит список requests и в своём методе runPrepared передаст его функции spreadsheets.batchUpdate, а затем очистит. Добавлять элементы в этот список будут методы вида prepare_соответствующийЗапрос.
Теперь код для задания ширины столбцов выглядит так:
# 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()
И вот код методов prepare_setColumnWidth и prepare_setColumnsWidth:
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)
Код метода runPrepared я приведу немного далее, потому что он пополнится ещё кое-чем.
3.8. Заполнение ячеек данными
Для заполнения ячеек информацией в Google Sheets API v4 предусмотрена функция spreadsheets.values.batchUpdate, работающая по тому же принципу, что и spreadsheets.batchUpdate. Она принимает список прямоугольников и значений, которые нужно записать в каждый из них. Кроме этого, принимает параметр ValueInputOption:
- если
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. Объединение ячеек, настройка жирности, формата отображения, цвета фона и прочего
Кому не терпится, можете сразу читать полный код класса Spreadsheet и пример его использования, который является решением задачи, поставленной в начале статьи.
Для более терпеливого читателя:
- 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}}}}
Некоторые тонкости
Q1: Зачем всё же при создании документа (в 3.3) параметру locale было задано значение ru_RU?
A1: Дело в том, что в этом случае строка, имеющая вид 2 июл 2016 17:57:52, будет распознаваться таблицей как дата и время. Соответственно, такие ячейки можно использовать в формуле для вычисления продолжительности (как разности двух дат, например).
Q2: Откуда получено, что формат «продолжительность» это {'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}}?
A2: До этого можно докопаться, изучая документацию. Но я просто вручную задал конкретной ячейке формат отображения «продолжительность», а затем получил документ программой с помощью функции spreadsheets.get, задав параметру includeGridData значение True, и посмотрел, какое значение параметра numberFormat у той ячейки.
Q3: В запросах, передаваемых функции spreadsheets.batchUpdate, параметр range имеет формат GridRange:
{
'sheetId': число,
'startRowIndex': число,
'endRowIndex': число,
'startColumnIndex': число,
'endColumnIndex': число
}
А в прямоугольниках с данными для функции spreadsheets.values.batchUpdate параметр range — это строка, вида Название_листа!A5:E7 (A1 notation). Странно.
A3: Да. Возможно, в комментариях к статье кто-нибудь объяснит, почему так.
В классе-обёртке я сделал для удобства метод toGridRange.
Q4: Пикачу, который в таблице в начале статьи, посажен туда программно?
A4: Нет, Пикачу я разместил в таблице вручную. Не уверен, что Google Sheets API v4 позволяет сделать это программно, сходу нужную функцию не нашёл.
Q5: Есть ли какие-то ограничения использования Google Sheets API v4?
A5: Да, они называются квотами. За ними можно следить в Google Developers Console. Там же можно отправить запрос на увеличение квоты, если будет не хватать.
Заключение
Если Вы дочитали досюда, то, вероятно, освоили, как программно создать spreadsheet, и теперь горите желанием использовать Google-таблицы во всех своих проектах :)
Приведу повторно самые важные ссылки:
- Google Sheets API v4
- Google Developers Console
- Класс-обёртка Spreadsheet и примеры его использования
Автор: TsarIoann
