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
|
import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from app.settings import app_cfg as cfg
def fetch_spreadsheet():
"""Open the Google Spreadsheet, which contains the individual worksheets"""
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
path = os.path.dirname(os.path.abspath(__file__))
credentials = ServiceAccountCredentials.from_json_keyfile_name(cfg.GOOGLE_ACCOUNT_CREDS_PATH, scope)
docid = "1denb7TjYsN9igHyvYah7fQ0daABW32Z30lwV7QrDJQc"
client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(docid)
return spreadsheet
def fetch_worksheet(name):
"""Get a reference to a particular "worksheet" from the Google Spreadsheet"""
spreadsheet = fetch_spreadsheet()
return spreadsheet.worksheet(name)
def fetch_google_sheet(name):
"""Get all the values from a particular worksheet as a list of lists.
Returns:
:keys - the first row of the document
:lines - a list of lists with the rest of the rows"""
rows = fetch_worksheet(name).get_all_values()
keys = rows[0]
lines = rows[1:]
return keys, lines
def fetch_google_sheet_objects(name):
"""Get all the values from a worksheet as a list of dictionaries"""
keys, rows = fetch_google_sheet(name)
recs = []
for row in rows:
rec = {}
for index, key in enumerate(keys):
rec[key] = row[index]
recs.append(rec)
return recs
def fetch_google_lookup(name, item_key='key'):
"""Get all the values from a worksheet as a dictionary of dictionaries.
Specify which field you want to use as the dictionary key."""
keys, rows = fetch_google_sheet(name)
lookup = {}
for row in rows:
rec = {}
for index, key in enumerate(keys):
rec[key] = row[index]
lookup[rec[item_key]] = rec
return lookup
def fetch_verified_paper_lookup():
"""Fetch a lookup keyed by dataset, where each dataset points to a hash of valid or invalid papers..."""
keys, rows = fetch_google_sheet('verifications')
verified_lookup = {}
for row in rows:
rec = {}
for index, key in enumerate(keys):
rec[key] = row[index]
if rec['dataset'] not in verified_lookup:
verified_lookup[rec['dataset']] = {}
if str(rec['uses_dataset']) == '1':
verified_lookup[rec['dataset']][rec['paper_id']] = rec
return verified_lookup
def update_or_append_worksheet(name, form):
"""Update a row if it exists, create it if it doesn't"""
worksheet = fetch_worksheet(name)
keys = worksheet.row_values(1)
row = [ form[key] if key in form else '' for key in keys ]
try:
cell = worksheet.find(form['paper_id'])
except:
cell = None
if cell:
for i, item in enumerate(row):
worksheet.update_cell(cell.row, i+1, item)
else:
worksheet.append_row(row)
|
