-
Notifications
You must be signed in to change notification settings - Fork 0
/
gdive_util.py
368 lines (286 loc) · 12.3 KB
/
gdive_util.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
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
from __future__ import print_function
import os, sys
curdir = os.path.dirname(os.path.realpath(__file__))
cpath = os.path.dirname(curdir)
if not cpath in sys.path:
sys.path.append(cpath)
import pickle
import os.path
import io
import json
import numpy as np
import pandas as pd
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2 import service_account
from secret import get_auth
#https://developers.google.com/classroom/guides/auth
class gauth():
guser = 'service'
if guser=='service':
fauth = 'admin-10ac-service.json'
ftoken = 'admin_service_token.pickle'
user_email = 'yabebal@10academy.org'
else:
fauth = 'admin-10ac-oauth.json'
ftoken = 'admin_oauth_token.pickle'
user_email = None
class google_api():
def __init__(self,
token_file='token.pickle',
fauth=None,
SCOPES=None,
user_email=None,
verbose=1):
if SCOPES is None:
#reference: https://developers.google.com/classroom/guides/auth
self.SCOPES = [
#with ability to use files/images stored in drive
'https://www.googleapis.com/auth/drive',
#to edit slides
'https://www.googleapis.com/auth/presentations',
#to sheet
'https://www.googleapis.com/auth/spreadsheets.readonly',
#ref:
#https://developers.google.com/admin-sdk/reports/v1/appendix/usage/customer
#https://developers.google.com/admin-sdk/reports/reference/rest/v1/customerUsageReports/get
'https://www.googleapis.com/auth/admin.reports.usage.readonly',
#ref:
#https://developers.google.com/admin-sdk/reports/v1/updated-meet-metrics
#https://developers.google.com/admin-sdk/reports/v1/quickstart/python
'https://www.googleapis.com/auth/admin.reports.audit.readonly'
]
root = 'https://www.googleapis.com/auth/'
scopes = (
"classroom.student-submissions.students.readonly",
"classroom.profile.emails",
"classroom.courses",
"classroom.rosters",
"classroom.profile.photos"
)
scopes = list((root+e for e in scopes))
self.SCOPES += scopes
else:
self.SCOPES = SCOPES
#
self.verbose = verbose
#get credential file
self.HOME = os.environ.get('HOME','~')
if fauth is None:
fauth = 'gclass_credentials.json'
if os.path.exists(f'~/.env/{fauth}'):
path = '~/.env'
else:
path = os.path.join(self.HOME, '.credentials')
self.fauth = os.path.join(path, fauth)
self.token_file = os.path.join(path, 'gtoken',token_file)
#print('token file',self.token_file)
if self.verbose>1:
print(f'fauth={self.fauth}')
print(f'token_file={self.token_file}')
if not os.path.exists(self.fauth):
self.fauth = '~/.env/gclass_credentials.json'
auth = get_auth(ssmkey='gspread/config',
envvar='GSPREAD_CONFIG',
fconfig=self.fauth)
else:
auth = json.load(open(self.fauth,'r'))
if auth.get("type",'')=="service_account":
print(f'****** using service account: {self.fauth}')
self.creds = self.get_service_account()
if user_email:
print(f'delegating user: {user_email}')
self.creds = self.creds.with_subject(user_email)
else:
print('service account is being used without delegation..')
else:
self.creds = self.get_token()
def get_service_account(self):
return (service_account.Credentials \
.from_service_account_file(self.fauth,scopes=self.SCOPES))
def get_token(self):
"""Shows basic usage of the Slides API.
Prints the number of slides and elments in a sample presentation.
"""
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(self.token_file):
print(f'reading .. {self.token_file}')
with open(self.token_file, 'rb') as token:
creds = pickle.load(token)
else:
print(f'{self.token_file} does not exist .. generating new token')
# If there are no (valid) credentials available, let the user log in.
if creds is None or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
print('auth, scope:',self.fauth, self.SCOPES)
flow = InstalledAppFlow.from_client_secrets_file(
self.fauth, self.SCOPES)
creds = flow.run_local_server(port=0) #,open_browser=False
# Save the credentials for the next run
with open(self.token_file, 'wb') as token:
print(f'token written to {self.token_file}')
pickle.dump(creds, token)
return creds
def get_service(self,name=['drive','sheet','slide','class','admin']):
s = {}
if 'sheet' in name:
s['sheet'] = build('sheets', 'v4', credentials=self.creds)
if 'drive' in name:
s['drive'] = build('drive', 'v3', credentials=self.creds)
if 'slide' in name:
s['gslide'] = build('slides', 'v1', credentials=self.creds)
if 'class' in name:
s['class'] = build('classroom', 'v1', credentials=self.creds)
if 'admin' in name:
#https://developers.google.com/admin-sdk
s['admin'] = build('admin', 'reports_v1', credentials=self.creds)
return s
class gsheet(google_api):
def __init__(self,sheetid,
ftoken = 'token.pickle',
fauth = None,
sheetname = None):
super().__init__(fauth = fauth, token_file = ftoken)
#print(ftoken, type(ftoken))
#
self.sheetid = sheetid
self.sheetname = sheetname
s = self.get_service()
self.drive = s['drive']
self.gsheet = s['sheet']
self.sheet = self.gsheet.spreadsheets()
def get_sheet_df(self,name, start='A',
end=None, iheader=0, idata=0, lastrow=0,
major='COLUMNS'):
#get header
if iheader>0:
hrange = f'{name}!{start}{iheader}:{end}{iheader}'
result = self.sheet.values().get(spreadsheetId=self.sheetid,
range=hrange).execute()
if idata==1:
idata=2
self.all_columns = result.get('values', [])[0]
else:
iheader=0
nrows = lastrow-idata+1
self.all_columns=['']*nrows
#get data
if lastrow==0:
drange = f'{name}'
if idata>0:
drange += f'!{start}{idata}'
if end is not None:
drange += f':{end}'
else:
drange = f'{name}!{start}{idata}:{end}{lastrow}'
result = self.sheet.values().get(spreadsheetId=self.sheetid,
range=drange,
majorDimension=major).execute()
values = result['values']
if lastrow==0:
df = pd.DataFrame(values).transpose()
col = df.iloc[0].values
df = df.drop([0])
df.columns = col
else:
nrows = lastrow-idata+1
data = {col:['']*nrows for col in self.key_columns}
for col in self.key_columns:
if col in self.all_columns:
ic = self.all_columns.index(col)
v = values[ic]
nv = len(v)
data[col][0:nv] = v
df = pd.DataFrame.from_dict(data)
#df = df[:,self.key_columns].dropna(axis='columns', thresh=0.95)
return df.T
def create_sheet(self,title):
spreadsheet = {
'properties': {
'title': title
}
}
spreadsheet = self.sheet.create(body=spreadsheet,
fields='spreadsheetId').execute()
sheetid = spreadsheet.get('spreadsheetId')
print('Spreadsheet ID: {0}'.format(sheetid))
#
return sheetid
def df_to_sheet(self,df,name):
response = self.sheet.values().update(
spreadsheetId=self.sheetid,
valueInputOption='RAW',
range=f'{name}!A1',
body=dict(
majorDimension='ROWS',
values=df.T.reset_index().T.values.tolist())
).execute()
return response
def write_df(self,df,name):
values = [df.columns.values.tolist()]
values.extend(df.T.reset_index().T.values.tolist())
data = [{'range' : name, 'values' : df}]
body = {
'value_input_option': 'RAW',
'data': values }
response = (self.sheet.values()
.batchUpdate(spreadsheetId=self.sheetid, body=body)
.execute()
)
return response
def sheet_update(self,body, major='COLUMNS'):
'''
if you want to update multiple ranges
Refer: https://developers.google.com/sheets/api/guides/values
'''
result = self.sheet.values().batchUpdate(spreadsheetId=self.sheetid,
body=body).execute()
print('{0} cells updated.'.format(result.get('totalUpdatedCells')))
return result
def single_update(self,name, row_list, loc='A1', major='COLUMNS'):
'''
if you want to update a single row
Refer: https://developers.google.com/sheets/api/guides/values
row_list must be a list of lists: e,g [[cell_1],[cell_2],..]
'''
try:
if not isinstance(row_list[0],list):
values = [[x] for x in row_list]
else:
values = row_list
except:
print('row_list must be a list of lists: e,g [[cell_1],[cell_2],..]')
return
body = {'values': values}
#get data
#Ref: https://developers.google.com/sheets/api/guides/values
value_input_option = 'RAW'
#drange = f'{name}!{start}{istart}:{end}{iend}'
drange = f'{name}!{loc}'
result = self.sheet.values().update(spreadsheetId=self.sheetid,
range=drange,
valueInputOption=value_input_option,
body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))
return result
if '__main__' == __name__:
sid_radar = '15m7txkNxxMzHaONkS3kqpFJRMhGKUBaeWFGITfVmuLY'
gd = gsheet(sheetid=sid_radar,fauth='gdrive_10acad_auth.json')
dflist = {}
for i in [1,2,3,4,6,7,8,9,11,12]:
wname = f'Week{i}'
try:
df = gd.get_sheet_df(wname)
dflist[wname] = df
print(df.head())
print(f'------------{wname} df.shape={df.shape}----')
break
except:
print(f'ERROR: Could not obtain sheet for {wname}')