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