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
"""
CS1 24fa - Assignment 4
Functionality from Google Sheets API to get and update values in a spreadsheet.
Also creates list of dictionaries representing the pendula in the Newton's cradle.
"""
import sys
import os
sys.path.insert(0, os.getcwd())
from support.types import PendAttrs
from src.constants import RANGE, SPREADSHEET_ID
import termcolor
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from typing import Any
from sys import exit
####
SERVICE_ACCOUNT_FILE = "./config.json"
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
try:
creds = Credentials.from_service_account_file(
SERVICE_ACCOUNT_FILE, scopes=SCOPES)
except Exception as e:
print(termcolor.colored(
"Please follow the instructions to get your config.json file here:", "red")
)
print(termcolor.colored(
" https://cs1.caltech.codes/24fa/projects/04/service_account_instructions", color="blue"
))
raise e
def get_values(spreadsheet_id: str, range_names: str | list[str]) -> dict[str, Any]:
"""
Returns the values of a spreadsheet in a given range.
Args:
spreadsheet_id (str): the specific id of the spreadsheet (from url)
range_names (lst[str]): list of strings that have the ranges
Returns:
(dict): with the values of the rows, sorted by columns
Raises:
HttpError: if there is an error in getting the information
"""
try:
service = build('sheets', 'v4', credentials=creds)
result = service.spreadsheets().values().batchGet(
spreadsheetId=spreadsheet_id,
ranges=range_names
).execute()
return result
except HttpError as e:
print(termcolor.colored(
"You do not have access to that spreadsheet OR the range is invalid.\n"
"Did you make sure to share the spreadsheet to be available to 'Anyone with a link'?\n"
"Did you change constants in constants.py:\n"
" - constants.py:14\n"
" - constants.py:16\n"
"You can control/cmd + click on the links to go to that line.",
"red"
))
raise e
def update_values(spreadsheet_id: str,
range_names: str | list[str],
values: list[list[Any]]) -> dict[str, Any]:
"""
Updates the values in the specified spreadsheet in a given range.
Args:
spreadsheet_id (str): the specific id of the spreadsheet (from url)
range_names (lst): list of strings that have the ranges
values (lst): list of values that will be added to the range in sheet
Returns:
(dict): with the new values of the rows, sorted by columns
Raises:
HttpError: if there is an error in getting the information
"""
service = build('sheets', 'v4', credentials=creds)
result = service.spreadsheets().values().batchUpdate(
spreadsheetId=spreadsheet_id,
body={
"valueInputOption": 'USER_ENTERED',
"data": [{
'range': range_names,
'values': values,
}]
}
).execute()
return result
def get_pendulum_info(ranges: str | list[str], spreadsheet_id: str) -> list[PendAttrs]:
"""
Returns a list with all the information from the Google Sheet stored in
dictionaries for each pendulum.
Args:
ranges (lst) - list of strings representing ranges per attribute
spreadsheet_id (str) - unique spreadsheet id of google sheet
Returns:
(list[dict]) - list of dictionaries with attributes for each pendulum
e.g.: [{'masses':1, 'restitution_coeffs':0.1, 'colors':'green',
'textures':'wood'},...,
{'masses':2, 'restitution_coeffs':0.5,
'colors':'blue', 'textures':'earth'}]
"""
pend_info = []
data = get_values(spreadsheet_id, ranges)['valueRanges'][0]['values']
headers = data[0]
rows = data[1:]
for row in rows:
pend_dict = {}
for j, col_header in enumerate(headers):
pend_dict[col_header] = row[j]
pend_info.append(pend_dict)
return pend_info
if __name__ == "__main__":
# testing
pend_list = get_pendulum_info(RANGE, SPREADSHEET_ID)
print(pend_list)
# # You can also update the values in your Google Sheet with code like this!
# mass_range = "A2:A5"
# update_values(SPREADSHEET_ID, mass_range, [['1.5'], ['2'], ['1'], ['1.5']])
# pend_list_new = get_pendulum_info(RANGE, SPREADSHEET_ID)
# print(pend_list_new)