def fetch_authorization_row(email):
CLIENT_EMAIL = app.config['GOOGLE_CLIENT_EMAIL']
PRIVATE_KEY = app.config['GOOGLE_PRIVATE_KEY']
SCOPE = "https://spreadsheets.google.com/feeds/"
credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY,
SCOPE)
token = OAuth2TokenFromCredentials(credentials)
client = SpreadsheetsClient()
token.authorize(client)
# Load worksheet with auth info
spreadsheet_id = app.config['GOOGLE_SPREADSHEET_ID']
worksheets = client.get_worksheets(spreadsheet_id)
worksheet = worksheets.entry[0]
# worksheet.id.text takes the form of a full url including the spreadsheet, while
# we only need the last part of that
id_parts = worksheet.id.text.split('/')
worksheet_id = id_parts[len(id_parts) - 1]
list_feed = client.get_list_feed(spreadsheet_id, worksheet_id)
rows = [row.to_dict() for row in list_feed.entry]
user_auth_row = None
for row in rows:
if row['email'] == email:
user_auth_row = row
break
return user_auth_row
def fetch_authorization_row(email):
CLIENT_EMAIL = app.config['GOOGLE_CLIENT_EMAIL']
PRIVATE_KEY = app.config['GOOGLE_PRIVATE_KEY']
SCOPE = "https://spreadsheets.google.com/feeds/"
credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY, SCOPE)
token = OAuth2TokenFromCredentials(credentials)
client = SpreadsheetsClient()
token.authorize(client)
# Load worksheet with auth info
spreadsheet_id = app.config['GOOGLE_SPREADSHEET_ID']
worksheets = client.get_worksheets(spreadsheet_id)
worksheet = worksheets.entry[0]
# worksheet.id.text takes the form of a full url including the spreadsheet, while
# we only need the last part of that
id_parts = worksheet.id.text.split('/')
worksheet_id = id_parts[len(id_parts) - 1]
list_feed = client.get_list_feed(spreadsheet_id, worksheet_id)
rows = [row.to_dict() for row in list_feed.entry]
user_auth_row = None
for row in rows:
if row['email'] == email:
user_auth_row = row
break
return user_auth_row
class SpreadsheetFactory(DAClient):
def __init__(self, basedir=None, **kwargs):
super(SpreadsheetFactory, self).__init__(basedir, **kwargs)
self.bearerToken = OAuth2BearerToken(self.credentials)
self.ssc = SpreadsheetsClient(auth_token=self.bearerToken)
def __call__(self, **kwargs):
'''
sheetName: search sheet
parentId: search sheet from parent
sheetId: ignore sheetName and parentId when set this
'''
self.sheetName = kwargs.get('sheetName', None)
self.parentId = kwargs.get('parentId', None)
self.sheetId = kwargs.get('sheetId', None)
self.worksheetId = None
self.set_activesheet()
return self
def set_activesheet(self):
if self.sheetId is None:
q = ["mimeType='%s'" % SPREADSHEET_TYPE]
if self.sheetName: q.append("title contains '%s'" % self.sheetName)
if self.parentId: q.append("'%s' in parents" % self.parentId)
q.append('explicitlyTrashed=False')
entries = self.execQuery(' and '.join(q), noprint=True)
if not len(entries['items']):
self.sheetId = None
self.worksheetId = None
sys.stderr.write('not found: %s\n' % q)
return
self.sheetId = entries['items'][0]['id']
for ws in self.worksheets():
self.worksheetId = ws.get_worksheet_id()
break
else:
self.worksheetId = None
def sheet(self, sheetId=None):
if sheetId is None: sheetId = self.sheetId
return self.service.files().get(fileId=sheetId).execute()
def worksheets(self, sheetId=None):
'''
sheetId:
'''
if sheetId is None: sheetId = self.sheetId
return self.ssc.get_worksheets(sheetId).entry
def cells(self, sheetId=None, worksheetId=None):
'''
sheetId:
worksheetId:
'''
if sheetId is None: sheetId = self.sheetId
if worksheetId is None: worksheetId = self.worksheetId
return self.ssc.get_cells(sheetId, worksheetId).entry
def updateCell(self, row, col, val, sheetId=None, worksheetId=None):
'''
packaged version of gdata-2.0.18 does NOT contain update_cell()
(pip install gdata) or (easy_install gdata)
version (2013-07-12) some functions are added after gdata-2.0.18
https://code.google.com/p/gdata-python-client/source/list
please clone new version (python setup.py install)
hg clone https://code.google.com/p/gdata-python-client/
and bug is reported: http://stackoverflow.com/questions/9940578/
add parameter force=True to fix it
'''
if sheetId is None: sheetId = self.sheetId
if worksheetId is None: worksheetId = self.worksheetId
return self.ssc.update_cell(sheetId,
worksheetId,
row,
col,
val,
force=True)
def createSpreadsheet(self,
sheetName,
description=None,
parentId=None,
csv=None,
rows=1000,
cols=26):
'''
sheetName: file name for Google Drive
description: description for Google Drive (default same as sheetName)
parentId: create into parent folder (default 'root')
csv: string (default None: empty sheet)
rows: int (default 1000)
cols: int (default 26)
'''
body = {
'title': sheetName,
'mimeType': 'text/csv', # to be converted
'description': description if description else sheetName
}
if parentId is None: parentId = 'root'
body['parents'] = [{'id': parentId}]
mbody = MediaInMemoryUpload(csv if csv else '\n'.join([',' * cols] *
rows),
mimetype='text/csv',
chunksize=256 * 1024,
resumable=False)
req = self.service.files().insert(body=body, media_body=mbody)
req.uri += '&convert=true'
fileObj = req.execute()
if fileObj is None: return (None, None)
self.sheetId = fileObj['id']
self.set_activesheet()
return (self.sheetId, fileObj)
class Spreadsheet:
def __init__(self, *args, **kwargs):
if len(kwargs) == 0:
if len(args) == 1:
config_path = args[0]
else:
config_path = CONFIG_PATH
print 'loading config from %s...' % config_path
with open(config_path, 'r') as fp:
self.config = json.load(fp)
else:
self.config = kwargs
if 'oauth2_key_file' not in self.config:
raise Exception('oauth2_key_file is not given')
if 'client_email' not in self.config:
raise Exception('client_email is not given')
self.default_spreadsheet_key = self.config.get(
'default_spreadsheet_key', None)
self.default_worksheet_id = self.config.get('default_worksheet_id',
None)
if _use_old_oauth2client:
# use .pem
key_file_path = self.config['oauth2_key_file']
if key_file_path[-4:] == '.p12':
key_file_path = key_file_path.replace('.p12', '.pem')
with open(key_file_path, 'rb') as f:
private_key = f.read()
self.credentials = SignedJwtAssertionCredentials(
self.config['client_email'],
private_key,
scope=["https://spreadsheets.google.com/feeds"])
else:
# use .p12
self.credentials = ServiceAccountCredentials.from_p12_keyfile(
self.config['client_email'],
self.config['oauth2_key_file'],
scopes=['https://spreadsheets.google.com/feeds'])
self.client = SpreadsheetsClient()
self.auth_token = OAuth2TokenFromCredentials(self.credentials)
self.auth_token.authorize(self.client)
def get_spreadsheet_infos(self):
infos = []
spreadsheets_feed = self.client.get_spreadsheets()
for spreadsheet in spreadsheets_feed.entry:
spreadsheet_key = spreadsheet.id.text.split('/')[-1]
infos.append({
'id': spreadsheet_key,
'title': spreadsheet.title.text,
'sheets': self.get_worksheet_infos(spreadsheet_key)
})
return infos
def get_worksheet_infos(self, spreadsheet_key=None):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
infos = []
worksheets_feed = self.client.get_worksheets(spreadsheet_key)
for worksheet_entry in worksheets_feed.entry:
worksheet_id = worksheet_entry.id.text.split('/')[-1]
worksheet_info = self.get_worksheet_info(spreadsheet_key,
worksheet_id)
infos.append(worksheet_info)
return infos
def get_worksheet_info(self, spreadsheet_key=None, worksheet_id=None):
try:
worksheet_entry = self.client.get_worksheet(
spreadsheet_key, worksheet_id)
if not worksheet_entry:
return None
return _strip_worksheet_entry(worksheet_entry)
except:
return None
def update_entry(self, entry):
if entry:
self.client.update(entry)
def iter_rows(self, spreadsheet_key=None, worksheet_id=None):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
list_feed = self.client.get_list_feed(spreadsheet_key, worksheet_id)
for entry in list_feed.entry:
yield entry.to_dict()
def add_header(self, spreadsheet_key=None, worksheet_id=None, header=[]):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
cell_query = CellQuery(min_row=1,
max_row=1,
min_col=1,
max_col=len(header),
return_empty=True)
cells = self.client.GetCells(spreadsheet_key,
worksheet_id,
q=cell_query)
for i, name in enumerate(header):
cell_entry = cells.entry[i]
cell_entry.cell.input_value = name
self.client.update(cell_entry) # This is the
# add_list_entry
def add_row(self, spreadsheet_key=None, worksheet_id=None, values={}):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
if len(values) == 0:
return
list_entry = ListEntry()
list_entry.from_dict(
{key: _convert(value)
for key, value in values.iteritems()})
self.client.add_list_entry(list_entry, spreadsheet_key, worksheet_id)
def add_worksheet(self,
spreadsheet_key=None,
title=None,
rows=1,
cols=1): # worksheet_id, values):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
if not title:
raise Exception('title is not given')
worksheet_entry = self.client.add_worksheet(spreadsheet_key, title,
rows, cols)
return _strip_worksheet_entry(worksheet_entry)
def delete_worksheet(self, spreadsheet_key, worksheet_id):
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
if worksheet_id is None:
raise Exception('worksheet_id is not given')
worksheet_entry = self.client.get_worksheet(spreadsheet_key,
worksheet_id)
self.client.delete(worksheet_entry)
class SpreadSheetDBConnection(object):
"""
Spreadsheet wrapper class
"""
default_connection = None
def __init__(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
self._username = username
self._password = password
self._spreadsheet_key = spreadsheet_key
self._source = source
self._is_connected = False
self._worksheets = []
self._client = SpreadsheetsClient()
#self._tables = []
def connect(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
"""
make a connection with google api, and load spreadsheet information.
"""
if self._is_connected:
raise SpreadSheetDBError()
if spreadsheet_key:
self._spreadsheet_key = spreadsheet_key
if username:
self._username = username
if password:
self._password = password
if source:
self._source = source
if not self._spreadsheet_key or not self._username or not self._password or not self._source:
raise SpreadSheetDBError()
self._client.client_login(self._username, self._password, source=self._source)
self._is_connected = True
self.select_spreadsheet(self._spreadsheet_key)
def select_spreadsheet(self, spreadsheet_key):
"""
select spreadsheet
"""
if not self._is_connected:
raise SpreadSheetDBError()
self._worksheets = {}
if spreadsheet_key:
self._spreadsheet_key = spreadsheet_key
self._refresh_worksheets_info()
def _refresh_worksheets_info(self):
if not self._is_connected:
raise SpreadSheetDBError()
if not self._spreadsheet_key:
raise SpreadSheetDBError()
worksheets = self._client.get_worksheets(self._spreadsheet_key)
for worksheet in worksheets.entry:
self._worksheets[worksheet.title.text] = {'key': worksheet.get_worksheet_id(), 'row_count': worksheet.row_count.text,
'col_count': worksheet.col_count.text}
def create_list(self, model):
"""
make a new worksheet for model
"""
if not self._is_connected:
raise SpreadSheetDBError()
list_name = getattr(model, "worksheet_name")()
if not list_name:
raise SpreadSheetDBError()
fields = []
for index, value in model.__dict__.iteritems():
if isinstance(value, Field):
if value.field_name:
fields.append(value.field_name)
else:
fields.append(index)
if not list_name in self._worksheets.iterkeys():
worksheet = self._client.add_worksheet(self._spreadsheet_key, list_name, 1, len(fields))
self._worksheets[worksheet.title.text] = {'key': worksheet.get_worksheet_id(), 'row_count': worksheet.row_count.text,
'col_count': worksheet.col_count.text}
cell_feed = gdata.spreadsheets.data.build_batch_cells_update(self._spreadsheet_key, self._worksheets[list_name]['key'])
col = 1
for index in fields:
cell_feed.add_set_cell(1, col, index)
col += 1
self._client.batch(cell_feed, force=True)
def add_entry(self, list_name, fields):
"""
add new entry to worksheet
"""
if not self._is_connected:
raise SpreadSheetDBError()
try:
current_worksheet = self._worksheets[list_name]
except KeyError:
raise SpreadSheetNotExists()
inserting_row = ListEntry()
for key, val in fields.iteritems():
inserting_row.set_value(key, val)
ret = self._client.add_list_entry(inserting_row, self._spreadsheet_key, current_worksheet['key'])
return ret
def update_entry(self, entry, data):
"""
update entry
"""
for key, val in data.iteritems():
entry.set_value(key, val)
self._client.update(entry)
def delete_entry(self, entry):
"""
delete entry
"""
self._client.delete(entry)
def all_entries(self, list_name):
"""
load all entries from worksheet.
"""
list_feed = self._client.get_list_feed(self._spreadsheet_key, self._worksheets[list_name]['key'])
return list_feed.entry
def filtered_entries(self, list_name, sq=None, order_by=None, reverse=None):
"""
load filtered entries
https://developers.google.com/google-apps/spreadsheets/?hl=uk-UA&csw=1#sending_a_structured_query_for_rows
"""
query = ListQuery(order_by=order_by, reverse=reverse, sq=sq)
list_feed = self._client.get_list_feed(self._spreadsheet_key, self._worksheets[list_name]['key'], query=query)
return list_feed.entry