- PVSM.RU - https://www.pvsm.ru -
Пусть нам нужно создать программой на языке Python вот такую таблицу [1]:
Особенности этой таблицы:
Интересно? Тогда добро пожаловать под кат.
Сразу отметаем неподходящие библиотеки. Например, gspread [2]. Это обёртка над Google Sheets API v3 [3], в котором нет методов для настройки оформления таблицы. Даже ширину столбца задать не получится.
Будем использовать Google Sheets API v4 [4].
А именно, google-api-python-client. Установить [6] можно при помощи pip, например:
pip install --upgrade google-api-python-client
Эта библиотека притянет необходимые зависимости (такие, как oauth2client и прочие).
Импортируем необходимое:
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)
Теперь на секунду приостановимся и обсудим терминологию.
spreadsheetId
, имеющий вид 1kygOW5wSSVqwf26M-OCT72i0FX0olZAz4duT2i6psp4
.sheetId
, являющийся числом. У первого созданного в документе листа id равен 0. В документе всегда есть как минимум один лист (удалить его не получится). Все листы имеют разные id и разные названия.
worksheetId
(или wid
), имеющий вид oowy6v0
. Для конвертации в число нужно особое извращение [7]:
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
, то по ссылке откроется первый лист в документе.
Вернёмся к коду. Объект service
, который мы создали, даёт нам всего 9 функций [8]. Одна из них называется spreadsheets.create [9], она создаёт новый spreadsheet. В качестве аргумента нужно передать объект Spreadsheet [10]. Заполнять все его поля не требуется, у большинства есть значения по умолчанию.
spreadsheet = service.spreadsheets().create(body = {
'properties': {'title': 'Сие есть название документа', 'locale': 'ru_RU'},
'sheets': [{'properties': {'sheetType': 'GRID',
'sheetId': 0,
'title': 'Сие есть название листа',
'gridProperties': {'rowCount': 8, 'columnCount': 5}}}]
}).execute()
В ответ получаем снова объект Spreadsheet [10], только заполненных параметров больше:
{'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
. Ура! Идём смотреть глазами на созданный документ [11]… и обламываемся, потому что доступа к нему у нас нет. Даже на чтение. Всё как у обычной свежесозданной пользователем вручную Google-таблицы.
А у кого доступ? У сервисного аккаунта.
Что же делать? Ответ очевиден: выдать доступ к документу тоже с помощью API.
У нашего объекта service
нет метода для настройки доступа к документу. Его просто нет в Google Sheets API. Зато он есть [12] в Google Drive API v3 [13]. Пишем код.
driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth)
shareRes = driveService.permissions().create(
fileId = spreadsheet['spreadsheetId'],
body = {'type': 'anyone', 'role': 'reader'}, # доступ на чтение кому угодно
fields = 'id'
).execute()
Такой код даёт доступ всем на чтение по ссылке [14].
Допустим, мы желаем вместо этого дать доступ на редактирование пользователю user@example.com. Для этого вместо
{'type': 'anyone', 'role': 'reader'}
пишем
{'type': 'user', 'role': 'writer', 'emailAddress': 'user@example.com'}
Начнём оформление таблицы с выставления ширины столбцов. Ой, а где такая функция? Всё не так прозрачно и чуть более умно, чем просто какой-нибудь setColumnWidth
.
Есть функция spreadsheets.batchUpdate [15]. Она применяет сразу пачку изменений к документу. А точнее, сначала она проверяет всю пачку на корректность. Если всё OK, то атомарно применяет всё и возвращает соответствующую пачку результатов. Список изменений, которые можно применять этой функцией, находится здесь [16].
Чтобы задать ширину столбцов нужно сделать UpdateDimensionPropertiesRequest [17].
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, который даст мне в удобном виде необходимые методы.
Пусть класс-обёртка (назовём его 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 я приведу немного далее, потому что он пополнится ещё кое-чем.
Для заполнения ячеек информацией в Google Sheets API v4 предусмотрена функция spreadsheets.values.batchUpdate [18], работающая по тому же принципу, что и spreadsheets.batchUpdate. Она принимает список прямоугольников и значений, которые нужно записать в каждый из них. Кроме этого, принимает параметр ValueInputOption [19]:
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()
Получим вот такой документ [20].
Теперь сделаем, чтобы наш класс-обёртка предоставил удобные методы для достижения того же результата.
Пусть функция 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()
Кому не терпится, можете сразу читать полный код класса Spreadsheet [21] и пример его использования [22], который является решением задачи, поставленной в начале статьи.
Для более терпеливого читателя:
# Объединить ячейки A1:E1
{'mergeCells': {'range': {'sheetId': 0,
'startRowIndex': 0,
'endRowIndex': 1,
'startColumnIndex': 0,
'endColumnIndex': 5},
'mergeType': 'MERGE_ALL'}}
# Подготовить такой запрос при помощи класса Spreadsheet
ss.prepare_mergeCells('A1:E1')
# Сделать жирными и выровнять по центру ячейки 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')
# Задать цвет фона ячеек следующим образом: 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}}]])
# Чёрная сплошная граница толщиной 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 [27], задав параметру includeGridData
значение True
, и посмотрел, какое значение параметра numberFormat
у той ячейки.
Q3: В запросах, передаваемых функции spreadsheets.batchUpdate, параметр range
имеет формат GridRange [28]:
{
'sheetId': число,
'startRowIndex': число,
'endRowIndex': число,
'startColumnIndex': число,
'endColumnIndex': число
}
А в прямоугольниках с данными для функции spreadsheets.values.batchUpdate параметр range
— это строка, вида Название_листа!A5:E7
(A1 notation [29]). Странно.
A3: Да. Возможно, в комментариях к статье кто-нибудь объяснит, почему так.
В классе-обёртке я сделал для удобства метод toGridRange [30].
Q4: Пикачу, который в таблице в начале статьи [1], посажен туда программно?
A4: Нет, Пикачу я разместил в таблице вручную. Не уверен, что Google Sheets API v4 позволяет сделать это программно, сходу нужную функцию не нашёл.
Q5: Есть ли какие-то ограничения использования Google Sheets API v4?
A5: Да, они называются квотами. За ними можно следить в Google Developers Console [31]. Там же можно отправить запрос на увеличение квоты, если будет не хватать.
Если Вы дочитали досюда, то, вероятно, освоили, как программно создать spreadsheet, и теперь горите желанием использовать Google-таблицы во всех своих проектах :)
Приведу повторно самые важные ссылки:
Автор: TsarIoann
Источник [34]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/python/169851
Ссылки в тексте:
[1] вот такую таблицу: https://docs.google.com/spreadsheets/d/1kygOW5wSSVqwf26M-OCT72i0FX0olZAz4duT2i6psp4/edit?usp=sharing
[2] gspread: https://github.com/burnash/gspread
[3] Google Sheets API v3: https://developers.google.com/google-apps/spreadsheets/
[4] Google Sheets API v4: https://developers.google.com/sheets/
[5] Google Developers Console: https://console.developers.google.com/project
[6] Установить: https://developers.google.com/api-client-library/python/start/installation
[7] особое извращение: http://stackoverflow.com/a/26885636/1565832
[8] всего 9 функций: https://developers.google.com/sheets/reference/rest/
[9] spreadsheets.create: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/create
[10] Spreadsheet: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#Spreadsheet
[11] созданный документ: https://docs.google.com/spreadsheets/d/1Sfl7EQ0Yuyo65INidt4LCrHMzFI9wrmc96qHq6EEqHM/edit
[12] он есть: https://developers.google.com/drive/v3/web/manage-sharing
[13] Google Drive API v3: https://developers.google.com/drive/v3/web/about-sdk
[14] по ссылке: https://docs.google.com/spreadsheets/d/1cZydEMbMomTfmPFj6IX_5F0ayiKD8H4DKS3lQt0wChc/edit
[15] spreadsheets.batchUpdate: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/batchUpdate
[16] находится здесь: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request
[17] UpdateDimensionPropertiesRequest: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateDimensionPropertiesRequest
[18] spreadsheets.values.batchUpdate: https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/batchUpdate
[19] ValueInputOption: https://developers.google.com/sheets/reference/rest/v4/ValueInputOption
[20] вот такой документ: https://docs.google.com/spreadsheets/d/1PPVP_0HIH9sljQjD86pKJaYaPFmMJgONLpUc9Djhoqg/edit#gid=0
[21] читать полный код класса Spreadsheet: https://github.com/Tsar/Spreadsheet/blob/master/Spreadsheet.py#L30
[22] пример его использования: https://github.com/Tsar/Spreadsheet/blob/master/Spreadsheet.py#L270
[23] MergeCellsRequest: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#MergeCellsRequest
[24] RepeatCellRequest: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#RepeatCellRequest
[25] UpdateCellsRequest: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateCellsRequest
[26] UpdateBordersRequest: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateBordersRequest
[27] spreadsheets.get: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/get
[28] GridRange: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#GridRange
[29] A1 notation: https://developers.google.com/sheets/guides/concepts#a1_notation
[30] toGridRange: https://github.com/Tsar/Spreadsheet/blob/master/Spreadsheet.py#L131
[31] следить в Google Developers Console: https://console.developers.google.com/iam-admin/quotas
[32] Google Developers Console: https://console.developers.google.com/
[33] Класс-обёртка Spreadsheet и примеры его использования: https://github.com/Tsar/Spreadsheet/blob/master/Spreadsheet.py
[34] Источник: https://habrahabr.ru/post/305378/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best
Нажмите здесь для печати.