-
Notifications
You must be signed in to change notification settings - Fork 0
/
quickstart.py
113 lines (98 loc) · 3.38 KB
/
quickstart.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
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = "1wtTAM7A--ka7Lnog43L6jjo9kMCnDElCrTOBllEg4dA"
def build_spreadsheet_service():
# If modifying these scopes, delete the file token.pickle.
SCOPES = ["*", "https://www.googleapis.com/auth/spreadsheets"]
"""Shows basic usage of the Sheets API.
Prints values from a sample spreadsheet.
"""
creds = None
# The file token.pickle stores the user's access and refresh tokens, and is
# created automatically when the authorization flow completes for the first
# time.
if os.path.exists("token.pickle"):
with open("token.pickle", "rb") as token:
creds = pickle.load(token)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
"marking_and_admin/credentials.json", SCOPES
)
try:
creds = flow.run_local_server()
except OSError as e:
print(e)
creds = flow.run_console()
# Save the credentials for the next run
with open("token.pickle", "wb") as token:
pickle.dump(creds, token)
service = build("sheets", "v4", credentials=creds)
return service
def write(service):
# Call the Sheets API
# sheet = service.spreadsheets()
# result = (
# sheet.values()
# .get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME)
# .execute()
# )
# values = result.get("values", [])
# if not values:
# print("No data found.")
# else:
# print(values[0])
# for row in values[1:]:
# print(row)
# Writing values
new_values = [["a", "These"], ["b", "are"], ["c", "some"], ["d", "entries"]]
body = {"values": new_values}
result = (
service.spreadsheets()
.values()
.update(
spreadsheetId=SAMPLE_SPREADSHEET_ID,
range="testing!A1:Q1000",
valueInputOption="RAW",
body=body,
)
.execute()
)
print("{0} cells updated.".format(result.get("updatedCells")))
# update formatting
new_values = [["a", "These"], ["b", "are"], ["c", "some"], ["d", "entries"]]
request = []
for i, row in enumerate(new_values):
request.append(set_comment(0, i, row[1]))
body = {"requests": request}
result = (
service.spreadsheets()
.batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body)
.execute()
)
print("{0} cells updated.".format(result.get("totalUpdatedCells")))
def set_comment(x, y, comment):
request = {
"repeatCell": {
"range": {
"sheetId": 1704890600,
"startRowIndex": y,
"endRowIndex": y + 1,
"startColumnIndex": x,
"endColumnIndex": x + 1,
},
"cell": {"note": comment},
"fields": "note",
}
}
return request
if __name__ == "__main__":
service = build_spreadsheet_service()
write(service)