def getSheetData(spreadsheet=SPREADSHEET, worksheet=WORKSHEET, headers=HEADERS):
"""
Get the data of the worksheet as a list of rows, each row a dictionary of
the headers
"""
client = SpreadsheetsService()
cols = dict(zip(string.uppercase, headers))
rLabel = 0
niceRows = []
feedArgs = dict(key=spreadsheet, wksht_id=worksheet, visibility='public',
projection='basic')
cells = client.GetCellsFeed(**feedArgs)
for cell in cells.entry:
label = re.match('^([A-Z]+)([0-9]+)$', cell.title.text)
cLabel = label.group(1)
_rLabel = int(label.group(2))
if _rLabel == 1:
continue # header row
if _rLabel > rLabel:
niceRow = {}
niceRows.append(niceRow)
rLabel = _rLabel
niceRow[cols[cLabel]] = cell.content.text
return niceRows
def __init__(self,uname,pswd,document_name=None,worksheet_name=None):
# Connect to Google
self.gd_client = SpreadsheetsService(email=uname,password=pswd,source=GDATA_SOURCE)
self.gd_client.ProgrammaticLogin()
if document_name is not None:
self.set_document(document_name)
if worksheet_name is not None:
self.set_worksheet(worksheet_name)
def __init__(self, user, pw):
self.document_name = ''
self.current_key = ''
# Login process
self.gd_client = SpreadsheetsService()
self.gd_client.email = user
self.gd_client.password = pw
self.gd_client.ProgrammaticLogin()
def sheets_service(self):
""" A SpreadsheetsService singleton, used to perform
operations on the actual spreadsheet. """
if not hasattr(self, '_sheets_service'):
service = SpreadsheetsService()
service.email = self.google_user
service.password = self.google_password
service.source = SOURCE_NAME
service.ProgrammaticLogin()
self._sheets_service = service
return self._sheets_service
def getClient(self):
"""Returns a spreadsheet client if there's an Internet connection.
Returns None if no connection.
"""
client = SpreadsheetsService()
try:
client.GetWorksheetsFeed(self.spreadsheet_key, visibility='public',
projection='basic')
except gaierror:
client = None
return client
def reload_data(self):
self.client = SpreadsheetsService()
feed = self.client.GetWorksheetsFeed(self.key,
visibility='public',
projection='basic')
self.data = {}
for entry in feed.entry:
if entry.title.text in self.worksheets.keys():
bad_rows, total_rows = self.process_sheet(
entry.id.text.split("/")[-1],
self.worksheets[entry.title.text])
print "Skipped %d / %d rows in sheet \"%s\"" % (
bad_rows, total_rows, entry.title.text)
elif DEBUG:
print "Skipped sheet \"%s\"" % entry.title.text
def __init__(self, usr, pwd, form):
global FORM_COLS
self.gc = SpreadsheetsService()
self.gc.ClientLogin(username=usr, password=pwd, source=APP_NAME)
query = DocumentQuery()
query.title = form
feed = self.gc.GetSpreadsheetsFeed(query=query)
if not feed.entry: raise Exception, "empty spreadsheet list"
self.formid = feed.entry[0].id.text.rsplit('/', 1)[1]
# self.update = feed.entry[0].updated.text
query = CellQuery()
query.range = '%c1:%c1' % tuple(i + ord('A')
for i in (0, len(FORM_KEYS) - 1))
feed = self.gc.GetCellsFeed(self.formid, query=query)
FORM_COLS = tuple(''.join(c for c in cell.content.text.lower()
if c.isalnum() or c in '-_')
for cell in feed.entry)
self.statcol = FORM_COLS[FORM_KEYS.index('status')]
def get_cos_contributors():
''' returns a list of contribtors from a google spreadsheet
specified by spreadsheet ID '''
client = SpreadsheetsService()
feed = client.GetWorksheetsFeed(DOC_KEY, visibility='public', wksht_id='1')
sheet_key = feed.id.text.rsplit('/', 1)[1]
list_of_rows = client.GetListFeed(DOC_KEY, sheet_key,
visibility='public').entry
print('There are {} rows on the COS spreadsheet.'.format(
len(list_of_rows)))
cos_users = []
for row in list_of_rows:
row_dict = row.custom
cos_users.append(row_dict[COL_ROW_NAME].text)
return cos_users
def load_google_spreadsheet(spreadsheet_key, worksheet_name=None):
"""Downloads and exports a Google Spreadsheet in TSV format.
Returns a string containing the spreadsheet contents in TSV format (e.g.
for writing out to a file or parsing).
The first line is assumed to be the spreadsheet header (i.e. containing
column names), which can optionally be followed by one or more comment
lines (starting with '#'). Only the first cell of a comment line will be
parsed (to keep exported spreadsheets consistent with QIIME mapping files'
comments). The (optional) comments section is then followed by the
spreadsheet data.
Some of this code is based on the following websites, as well as the
gdata.spreadsheet.text_db module:
http://www.payne.org/index.php/Reading_Google_Spreadsheets_in_Python
http://stackoverflow.com/a/12031835
Arguments:
spreadsheet_key - the key used to identify the spreadsheet (a string).
Can either be a key or a URL containing the key
worksheet_name - the name of the worksheet to load data from (a
string). If not supplied, will use first worksheet in the
spreadsheet
"""
spreadsheet_key = _extract_spreadsheet_key_from_url(spreadsheet_key)
gd_client = SpreadsheetsService()
try:
worksheets_feed = gd_client.GetWorksheetsFeed(spreadsheet_key,
visibility='public',
projection='basic')
except gaierror:
raise GoogleSpreadsheetConnectionError("Could not establish "
"connection with server. Do "
"you have an active Internet "
"connection?")
if len(worksheets_feed.entry) < 1:
raise GoogleSpreadsheetError("The Google Spreadsheet with key '%s' "
"does not have any worksheets associated "
"with it." % spreadsheet_key)
# Find worksheet that will be exported. If a name has not been provided,
# use the first worksheet.
worksheet = None
if worksheet_name is not None:
for sheet in worksheets_feed.entry:
if sheet.title.text == worksheet_name:
worksheet = sheet
if worksheet is None:
raise GoogleSpreadsheetError("The worksheet name '%s' could not "
"be found in the Google Spreadsheet "
"with key '%s'." %
(worksheet_name, spreadsheet_key))
else:
# Choose the first one.
worksheet = worksheets_feed.entry[0]
# Extract the ID of the worksheet.
worksheet_id = worksheet.id.text.split('/')[-1]
# Now that we have a spreadsheet key and worksheet ID, we can read the
# data. First get the headers (first row). We need this in order to grab
# the rest of the actual data in the correct order (it is returned
# unordered).
headers = _get_spreadsheet_headers(gd_client, spreadsheet_key,
worksheet_id)
if len(headers) < 1:
raise GoogleSpreadsheetError("Could not load spreadsheet header (it "
"appears to be empty). Is your Google "
"Spreadsheet with key '%s' empty?" %
spreadsheet_key)
# Loop through the rest of the rows and build up a list of data (in the
# same row/col order found in the spreadsheet).
spreadsheet_lines = _export_spreadsheet(gd_client, spreadsheet_key,
worksheet_id, headers)
out_lines = StringIO()
tsv_writer = writer(out_lines, delimiter='\t', lineterminator='\n')
tsv_writer.writerows(spreadsheet_lines)
return out_lines.getvalue()
###############################################################################
SERVICE_ACCOUNT_EMAIL = '*****@*****.**'
PROJECT_NUMBER = '456961407722'
pemfile = file('tinkeracademy.pem', 'rb')
key = pemfile.read()
pemfile.close()
credentials = None
googlehttp = None
GOOGLE_DRIVE_SERVICE = None
GOOGLE_SPREADSHEETS_SERVICE = SpreadsheetsService()
###############################################################################
## Code below updates the tokens
###############################################################################
def updatetokens():
global credentials
credentials = SignedJwtAssertionCredentials(
SERVICE_ACCOUNT_EMAIL,
key,
scope=[
constants.GOOGLE_DRIVE_SCOPE, constants.GOOGLE_SPREADSHEETS_SCOPE
],
sub='*****@*****.**')
