-
Notifications
You must be signed in to change notification settings - Fork 19
/
Spreadsheet.py
executable file
·349 lines (293 loc) · 18.8 KB
/
Spreadsheet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
#!/usr/bin/env python3
# Author: Ioann Volkov ([email protected])
# This module uses Google Sheets API v4 (and Google Drive API v3 for sharing spreadsheets)
# (!) Disclaimer
# This is NOT a full-functional wrapper over Sheets API v4.
# This module was created just for https://telegram.me/TimeManagementBot and habrahabr article
from pprint import pprint
import httplib2
import apiclient.discovery
import googleapiclient.errors
from oauth2client.service_account import ServiceAccountCredentials
def htmlColorToJSON(htmlColor):
if htmlColor.startswith("#"):
htmlColor = htmlColor[1:]
return {"red": int(htmlColor[0:2], 16) / 255.0, "green": int(htmlColor[2:4], 16) / 255.0, "blue": int(htmlColor[4:6], 16) / 255.0}
class SpreadsheetError(Exception):
pass
class SpreadsheetNotSetError(SpreadsheetError):
pass
class SheetNotSetError(SpreadsheetError):
pass
class Spreadsheet:
def __init__(self, jsonKeyFileName, debugMode = False):
self.debugMode = debugMode
self.credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonKeyFileName, ['https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'])
self.httpAuth = self.credentials.authorize(httplib2.Http())
self.service = apiclient.discovery.build('sheets', 'v4', http = self.httpAuth)
self.driveService = None # Needed only for sharing
self.spreadsheetId = None
self.sheetId = None
self.sheetTitle = None
self.requests = []
self.valueRanges = []
# Creates new spreadsheet
def create(self, title, sheetTitle, rows = 1000, cols = 26, locale = 'en_US', timeZone = 'Etc/GMT'):
spreadsheet = self.service.spreadsheets().create(body = {
'properties': {'title': title, 'locale': locale, 'timeZone': timeZone},
'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': sheetTitle, 'gridProperties': {'rowCount': rows, 'columnCount': cols}}}]
}).execute()
if self.debugMode:
pprint(spreadsheet)
self.spreadsheetId = spreadsheet['spreadsheetId']
self.sheetId = spreadsheet['sheets'][0]['properties']['sheetId']
self.sheetTitle = spreadsheet['sheets'][0]['properties']['title']
def share(self, shareRequestBody):
if self.spreadsheetId is None:
raise SpreadsheetNotSetError()
if self.driveService is None:
self.driveService = apiclient.discovery.build('drive', 'v3', http = self.httpAuth)
shareRes = self.driveService.permissions().create(
fileId = self.spreadsheetId,
body = shareRequestBody,
fields = 'id'
).execute()
if self.debugMode:
pprint(shareRes)
def shareWithEmailForReading(self, email):
self.share({'type': 'user', 'role': 'reader', 'emailAddress': email})
def shareWithEmailForWriting(self, email):
self.share({'type': 'user', 'role': 'writer', 'emailAddress': email})
def shareWithAnybodyForReading(self):
self.share({'type': 'anyone', 'role': 'reader'})
def shareWithAnybodyForWriting(self):
self.share({'type': 'anyone', 'role': 'writer'})
def getSheetURL(self):
if self.spreadsheetId is None:
raise SpreadsheetNotSetError()
if self.sheetId is None:
raise SheetNotSetError()
return 'https://docs.google.com/spreadsheets/d/' + self.spreadsheetId + '/edit#gid=' + str(self.sheetId)
# Sets current spreadsheet by id; set current sheet as first sheet of this spreadsheet
def setSpreadsheetById(self, spreadsheetId):
spreadsheet = self.service.spreadsheets().get(spreadsheetId = spreadsheetId).execute()
if self.debugMode:
pprint(spreadsheet)
self.spreadsheetId = spreadsheet['spreadsheetId']
self.sheetId = spreadsheet['sheets'][0]['properties']['sheetId']
self.sheetTitle = spreadsheet['sheets'][0]['properties']['title']
# spreadsheets.batchUpdate and spreadsheets.values.batchUpdate
def runPrepared(self, valueInputOption = "USER_ENTERED"):
if self.spreadsheetId is None:
raise SpreadsheetNotSetError()
upd1Res = {'replies': []}
upd2Res = {'responses': []}
try:
if len(self.requests) > 0:
upd1Res = self.service.spreadsheets().batchUpdate(spreadsheetId = self.spreadsheetId, body = {"requests": self.requests}).execute()
if self.debugMode:
pprint(upd1Res)
if len(self.valueRanges) > 0:
upd2Res = self.service.spreadsheets().values().batchUpdate(spreadsheetId = self.spreadsheetId, body = {"valueInputOption": valueInputOption,
"data": self.valueRanges}).execute()
if self.debugMode:
pprint(upd2Res)
finally:
self.requests = []
self.valueRanges = []
return (upd1Res['replies'], upd2Res['responses'])
def prepare_addSheet(self, sheetTitle, rows = 1000, cols = 26):
self.requests.append({"addSheet": {"properties": {"title": sheetTitle, 'gridProperties': {'rowCount': rows, 'columnCount': cols}}}})
# Adds new sheet to current spreadsheet, sets as current sheet and returns it's id
def addSheet(self, sheetTitle, rows = 1000, cols = 26):
if self.spreadsheetId is None:
raise SpreadsheetNotSetError()
self.prepare_addSheet(sheetTitle, rows, cols)
addedSheet = self.runPrepared()[0][0]['addSheet']['properties']
self.sheetId = addedSheet['sheetId']
self.sheetTitle = addedSheet['title']
return self.sheetId
# Converts string range to GridRange of current sheet; examples:
# "A3:B4" -> {sheetId: id of current sheet, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2}
# "A5:B" -> {sheetId: id of current sheet, startRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2}
def toGridRange(self, cellsRange):
if self.sheetId is None:
raise SheetNotSetError()
if isinstance(cellsRange, str):
startCell, endCell = cellsRange.split(":")[0:2]
cellsRange = {}
rangeAZ = range(ord('A'), ord('Z') + 1)
if ord(startCell[0]) in rangeAZ:
cellsRange["startColumnIndex"] = ord(startCell[0]) - ord('A')
startCell = startCell[1:]
if ord(endCell[0]) in rangeAZ:
cellsRange["endColumnIndex"] = ord(endCell[0]) - ord('A') + 1
endCell = endCell[1:]
if len(startCell) > 0:
cellsRange["startRowIndex"] = int(startCell) - 1
if len(endCell) > 0:
cellsRange["endRowIndex"] = int(endCell)
cellsRange["sheetId"] = self.sheetId
return cellsRange
def prepare_setDimensionPixelSize(self, dimension, startIndex, endIndex, pixelSize):
if self.sheetId is None:
raise SheetNotSetError()
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)
def prepare_setRowsHeight(self, startRow, endRow, height):
self.prepare_setDimensionPixelSize("ROWS", startRow, endRow + 1, height)
def prepare_setRowHeight(self, row, height):
self.prepare_setRowsHeight(row, row, height)
def prepare_setValues(self, cellsRange, values, majorDimension = "ROWS"):
if self.sheetTitle is None:
raise SheetNotSetError()
self.valueRanges.append({"range": self.sheetTitle + "!" + cellsRange, "majorDimension": majorDimension, "values": values})
def prepare_mergeCells(self, cellsRange, mergeType = "MERGE_ALL"):
self.requests.append({"mergeCells": {"range": self.toGridRange(cellsRange), "mergeType": mergeType}})
# formatJSON should be dict with userEnteredFormat to be applied to each cell
def prepare_setCellsFormat(self, cellsRange, formatJSON, fields = "userEnteredFormat"):
self.requests.append({"repeatCell": {"range": self.toGridRange(cellsRange), "cell": {"userEnteredFormat": formatJSON}, "fields": fields}})
# formatsJSON should be list of lists of dicts with userEnteredFormat for each cell in each row
def prepare_setCellsFormats(self, cellsRange, formatsJSON, fields = "userEnteredFormat"):
self.requests.append({"updateCells": {"range": self.toGridRange(cellsRange),
"rows": [{"values": [{"userEnteredFormat": cellFormat} for cellFormat in rowFormats]} for rowFormats in formatsJSON],
"fields": fields}})
# === Tests for class Spreadsheet ===
GOOGLE_CREDENTIALS_FILE = 'tg-tm-bot-d146fb60ef7a.json'
def testCreateSpreadsheet():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.create("Preved medved", "Тестовый лист")
ss.shareWithEmailForWriting("[email protected]")
def testSetSpreadsheet():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
print(ss.sheetId)
def testAddSheet():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
try:
print(ss.addSheet("Я лолка №1", 500, 11))
except googleapiclient.errors.HttpError:
print("Could not add sheet! Maybe sheet with same name already exists!")
def testSetDimensions():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
ss.prepare_setColumnWidth(0, 500)
ss.prepare_setColumnWidth(1, 100)
ss.prepare_setColumnsWidth(2, 4, 150)
ss.prepare_setRowHeight(6, 230)
ss.runPrepared()
def testGridRangeForStr():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
res = [ss.toGridRange("A3:B4"),
ss.toGridRange("A5:B"),
ss.toGridRange("A:B")]
correctRes = [{"sheetId": ss.sheetId, "startRowIndex": 2, "endRowIndex": 4, "startColumnIndex": 0, "endColumnIndex": 2},
{"sheetId": ss.sheetId, "startRowIndex": 4, "startColumnIndex": 0, "endColumnIndex": 2},
{"sheetId": ss.sheetId, "startColumnIndex": 0, "endColumnIndex": 2}]
print("GOOD" if res == correctRes else "BAD", res)
def testSetCellsFormat():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
ss.prepare_setCellsFormat("B2:E7", {"textFormat": {"bold": True}, "horizontalAlignment": "CENTER"})
ss.runPrepared()
def testPureBlackBorder():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
ss.requests.append({"updateBorders": {"range": {"sheetId": ss.sheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 0, "endColumnIndex": 3},
"bottom": {"style": "SOLID", "width": 3, "color": {"red": 0, "green": 0, "blue": 0}}}})
ss.requests.append({"updateBorders": {"range": {"sheetId": ss.sheetId, "startRowIndex": 2, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 3},
"bottom": {"style": "SOLID", "width": 3, "color": {"red": 0, "green": 0, "blue": 0, "alpha": 1.0}}}})
ss.requests.append({"updateBorders": {"range": {"sheetId": ss.sheetId, "startRowIndex": 3, "endRowIndex": 4, "startColumnIndex": 1, "endColumnIndex": 4},
"bottom": {"style": "SOLID", "width": 3, "color": {"red": 0, "green": 0, "blue": 0.001}}}})
ss.requests.append({"updateBorders": {"range": {"sheetId": ss.sheetId, "startRowIndex": 4, "endRowIndex": 5, "startColumnIndex": 2, "endColumnIndex": 5},
"bottom": {"style": "SOLID", "width": 3, "color": {"red": 0.001, "green": 0, "blue": 0}}}})
ss.runPrepared()
# Reported: https://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=4696
def testUpdateCellsFieldsArg():
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.setSpreadsheetById('19SPK--efwYq9pZ7TvBYtFItxE0gY3zpfR5NykOJ6o7I')
ss.prepare_setCellsFormat("B2:B2", {"textFormat": {"bold": True}, "horizontalAlignment": "CENTER"}, fields = "userEnteredFormat.textFormat,userEnteredFormat.horizontalAlignment")
ss.prepare_setCellsFormat("B2:B2", {"backgroundColor": htmlColorToJSON("#00CC00")}, fields = "userEnteredFormat.backgroundColor")
ss.prepare_setCellsFormats("C4:C4", [[{"textFormat": {"bold": True}, "horizontalAlignment": "CENTER"}]], fields = "userEnteredFormat.textFormat,userEnteredFormat.horizontalAlignment")
ss.prepare_setCellsFormats("C4:C4", [[{"backgroundColor": htmlColorToJSON("#00CC00")}]], fields = "userEnteredFormat.backgroundColor")
pprint(ss.requests)
ss.runPrepared()
# Reported: https://code.google.com/a/google.com/p/apps-api-issues/issues/detail?id=4697
# This function creates a spreadsheet as https://telegram.me/TimeManagementBot can create, but with manually specified data
def testCreateTimeManagementReport():
docTitle = "Тестовый документ"
sheetTitle = "Тестовая таблица действий"
values = [["Действие", "Категория полезности", "Начато", "Завершено", "Потрачено"], # header row
["Обедаю", "Еда", "2 июл 2016 17:57:52", "2 июл 2016 18:43:45", "=D4-C4"],
["Лёг полежать", "Отдых", "2 июл 2016 18:43:47", "2 июл 2016 18:53:36", "=D5-C5"],
["Пью чай", "Еда", "2 июл 2016 18:53:39", "2 июл 2016 19:00:49", "=D6-C6"],
["Пилю и шлифую большие щиты", "Ремонт", "2 июл 2016 19:00:52", "2 июл 2016 19:52:36", "=D7-C7"],
["Собираю дверь шкафа", "Ремонт", "2 июл 2016 19:52:38", "2 июл 2016 21:11:21", "=D8-C8"]]
rowCount = len(values) - 1
colorsForCategories = {"Еда": htmlColorToJSON("#FFCCCC"),
"Отдых": htmlColorToJSON("#CCFFCC"),
"Ремонт": htmlColorToJSON("#CCCCFF")}
values2 = [["Категория полезности", "Потрачено"], # header row
["Ремонт", "=E7+E8"],
["Еда", "=E4+E6"],
["Отдых", "=E5"]]
rowCount2 = len(values2) - 1
ss = Spreadsheet(GOOGLE_CREDENTIALS_FILE, debugMode = True)
ss.create(docTitle, sheetTitle, rows = rowCount + 3, cols = 8, locale = "ru_RU", timeZone = "Europe/Moscow")
ss.shareWithAnybodyForWriting()
ss.prepare_setColumnWidth(0, 400)
ss.prepare_setColumnWidth(1, 200)
ss.prepare_setColumnsWidth(2, 3, 165)
ss.prepare_setColumnWidth(4, 100)
ss.prepare_mergeCells("A1:E1") # Merge A1:E1
rowColors = [colorsForCategories[valueRow[1]] for valueRow in values[1:]]
ss.prepare_setCellsFormat("A1:A1", {"textFormat": {"fontSize": 14}, "horizontalAlignment": "CENTER"}) # Font size 14 and center aligment for A1 cell
ss.prepare_setCellsFormat("A3:E3", {"textFormat": {"bold": True}, "horizontalAlignment": "CENTER"}) # Bold and center aligment for A3:E3 row
ss.prepare_setCellsFormats("A4:E%d" % (rowCount + 3), [[{"backgroundColor": color}] * 5 for color in rowColors])
ss.prepare_setCellsFormat("A4:B%d" % (rowCount + 3), {"numberFormat": {'type': 'TEXT'}}, fields = "userEnteredFormat.numberFormat") # Text format for A4:B* columns
ss.prepare_setCellsFormat("E4:E%d" % (rowCount + 3), {"numberFormat": {'pattern': '[h]:mm:ss', 'type': 'TIME'}}, fields = "userEnteredFormat.numberFormat") # Duration number format for E4:E* column
# Bottom border for A3:E3 row
ss.requests.append({"updateBorders": {"range": {"sheetId": ss.sheetId, "startRowIndex": 2, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 5},
"bottom": {"style": "SOLID", "width": 1, "color": htmlColorToJSON("#000001")}}})
ss.prepare_setValues("A1:A1", [[sheetTitle]])
ss.prepare_setValues("A3:E%d" % (rowCount + 3), values)
#ss.prepare_setCellsFormat("D%d:D%d" % (rowCount + 3, rowCount + 3), {"textFormat": {"italic": True}, "horizontalAlignment": "CENTER"},
# fields = "userEnteredFormat.textFormat,userEnteredFormat.horizontalAlignment") # Italic and center aligment for bottom D* cell
ss.prepare_setColumnWidth(6, 200)
ss.prepare_setColumnWidth(7, 100)
ss.prepare_mergeCells("G1:H1") # Merge G1:H1
rowColors2 = [colorsForCategories[valueRow[0]] for valueRow in values2[1:]]
ss.prepare_setCellsFormat("G1:G1", {"textFormat": {"fontSize": 14}, "horizontalAlignment": "CENTER"}) # Font size 14 and center aligment for G1 cell
ss.prepare_setCellsFormat("G3:H3", {"textFormat": {"bold": True}, "horizontalAlignment": "CENTER"}) # Bold and center aligment for G3:H3 row
ss.prepare_setCellsFormats("G4:H%d" % (rowCount2 + 3), [[{"backgroundColor": color}] * 2 for color in rowColors2])
ss.prepare_setCellsFormat("G4:G%d" % (rowCount2 + 3), {"numberFormat": {'type': 'TEXT'}}, fields = "userEnteredFormat.numberFormat") # Text format for G4:G* column
ss.prepare_setCellsFormat("H4:H%d" % (rowCount2 + 3), {"numberFormat": {'pattern': '[h]:mm:ss', 'type': 'TIME'}}, fields = "userEnteredFormat.numberFormat") # Duration number format for H4:H* column
# Bottom border for G3:H3 row
ss.requests.append({"updateBorders": {"range": {"sheetId": ss.sheetId, "startRowIndex": 2, "endRowIndex": 3, "startColumnIndex": 6, "endColumnIndex": 8},
"bottom": {"style": "SOLID", "width": 1, "color": htmlColorToJSON("#000001")}}})
ss.prepare_setValues("G1:G1", [["Категории"]])
ss.prepare_setValues("G3:H%d" % (rowCount2 + 3), values2)
ss.runPrepared()
print(ss.getSheetURL())
if __name__ == "__main__":
#testCreateSpreadsheet()
#testSetSpreadsheet()
#testAddSheet()
#testSetDimensions()
#testGridRangeForStr()
#testSetCellsFormat()
#testPureBlackBorder()
#testUpdateCellsFieldsArg()
testCreateTimeManagementReport()