Постановка задачи
Пусть нам нужно создать программой на языке 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-таблицы.А у кого доступ? У сервисного аккаунта.
Жмёте на кнопку «Запросить разрешение на доступ»?
Не заспамьте себя. При нажатии этой кнопки будет отправлено письмо на e-mail наподобие account@test-proj-for-habr-article.iam.gserviceaccount.com. Доставить это письмо не получится (потому что домена не существует), и на Вашу почту придёт сообщение о неудачной доставке письма. Содержимое письма тоже не поможет, потому что ссылка для выдачи доступа может работать только если Вы залогинены под аккаунт владельца, то есть под сервисный аккаунт.
Что же делать? Ответ очевиден: выдать доступ к документу тоже с помощью API.
Ну или другой вариант
Можно создать документ вручную на своём Google-диске и дать доступ сервисному аккаунту (то есть вручную выдать разрешения тому e-mail наподобие account@test-proj-for-habr-article.iam.gserviceaccount.com). Затем работать с этим документом через 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-таблицы во всех своих проектах :)
Приведу повторно самые важные ссылки:
