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 getSpreadsheetsClient(): client = SpreadsheetsClient(source='odenkiapi') access_token = loadAccessToken() if access_token is not None: assert isinstance(access_token, OAuthHmacToken) client.auth_token = access_token return client
class Spreadsheet_index(): """Read a published google spreadsheet into a list of dicts. Each dict is a row of the spreadsheet. Repeated properties are represented as a list. The list is then available as the table attribute.""" def __init__(self, google_spreadsheet_key, google_worksheet_id): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._client.client_login( google_credentials.USERNAME, google_credentials.PASSWORD, 'katholiekkwartetten' ) self.table = [] self.sync_table() def sync_table(self): del self.table[:] # table = [] would break the references! self._rows = self._client.get_list_feed( self._google_spreadsheet_key, self._google_worksheet_id ).entry for row in self._rows: self.table.append(import_from_spreadsheet(row.to_dict()))
def getSpreadsheetsClient(self): client = SpreadsheetsClient() access_token = self.loadAccessToken() if access_token is not None: assert isinstance(access_token, OAuthHmacToken) OAuthHmacToken client.auth_token = access_token return client
def __init__(self, google_spreadsheet_key, google_worksheet_id): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._client.client_login(google_credentials.USERNAME, google_credentials.PASSWORD, 'katholiekkwartetten') self.table = [] self.sync_table()
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials) self._token.authorize(self._client) self.table = [] self.sync_table()
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()
def __init__(self, credentials, sheet_name, Alt, BT, Miners): self.credentials = credentials self.gd_client = SpreadsheetsClient() self.spreadsheet_name = sheet_name self.Alt = Alt self.BT = BT self.Miners=Miners auth2token = gauth.OAuth2TokenFromCredentials(self.credentials) auth2token.authorize(self.gd_client)
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 __init__(self, google_spreadsheet_key, google_worksheet_id): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._client.client_login( google_credentials.USERNAME, google_credentials.PASSWORD, 'katholiekkwartetten' ) self.table = [] self.sync_table()
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() # somewhere read that following line help solving "ParseError: no element found: line 1, column 0" error, # in atom/core.py in atom/core.py line 520, but no self._client.additional_headers = { 'Authorization': 'Bearer %s' % oauth_decorator.credentials.access_token, } self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials) self._token.authorize(self._client) self.table = []
class Spreadsheet_index(): """Read a published google spreadsheet into a list of dicts. Each dict is a row of the spreadsheet. Repeated properties are represented as a list. The list is then available as the table attribute.""" def __init__(self, google_spreadsheet_key, google_worksheet_id): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._client.client_login(google_credentials.USERNAME, google_credentials.PASSWORD, 'katholiekkwartetten') self.table = [] self.sync_table() def sync_table(self): del self.table[:] # table = [] would break the references! self._rows = self._client.get_list_feed( self._google_spreadsheet_key, self._google_worksheet_id).entry for row in self._rows: self.table.append(import_from_spreadsheet(row.to_dict()))
def _service(self, http=None): if not (http or self._oauth2token or self._decorator): raise GoogleSpreadsheetsError('please set oauth2 credentials' ' or oauth2 decorator') if http: token = OAuth2BearerToken(http.request.credentials) elif self._decorator: token = OAuth2BearerToken(self._decorator.credentials) else: token = self._oauth2token try: return SpreadsheetsClient(auth_token=token) except Unauthorized as e: raise AccessTokenRefreshError(e.message)
class Spreadsheet_index(): """Read a published google spreadsheet into a list of dicts. Each dict is a row of the spreadsheet. Repeated properties are represented as a list. The list is then available as the table attribute.""" def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials) self._token.authorize(self._client) self.table = [] self.sync_table() def sync_table(self): del self.table[:] # table = [] would break the references! self._rows = self._client.get_list_feed( self._google_spreadsheet_key, self._google_worksheet_id ).entry for row in self._rows: self.table.append(import_from_spreadsheet(row.to_dict()))
class Spreadsheet_index(): """Read a published google spreadsheet into a list of dicts. Each dict is a row of the spreadsheet. Repeated properties are represented as a list. The list is then available as the table attribute.""" def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials) self._token.authorize(self._client) self.table = [] self.sync_table() def sync_table(self): del self.table[:] # table = [] would break the references! self._rows = self._client.get_list_feed( self._google_spreadsheet_key, self._google_worksheet_id).entry for row in self._rows: self.table.append(import_from_spreadsheet(row.to_dict()))
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
class Spreadsheet_index(): """Read a published google spreadsheet into a list of dicts. Each dict is a row of the spreadsheet. Repeated properties are represented as a list. The list is then available as the table attribute.""" def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None): """google_spreadsheet_key is the key of the spreadsheet (can be read from the url).""" self._google_spreadsheet_key = google_spreadsheet_key self._google_worksheet_id = google_worksheet_id self._client = SpreadsheetsClient() # somewhere read that following line help solving "ParseError: no element found: line 1, column 0" error, # in atom/core.py in atom/core.py line 520, but no self._client.additional_headers = { 'Authorization': 'Bearer %s' % oauth_decorator.credentials.access_token, } self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials) self._token.authorize(self._client) self.table = [] # self.sync_table() must be done explicitly! def sync_table(self): del self.table[:] # table = [] would break the references! for attempt in range(5): try: self._rows = self._client.get_list_feed( self._google_spreadsheet_key, self._google_worksheet_id ).entry except httplib.HTTPException as e: logging.info(e.message) time.sleep(0.5) continue else: break else: # we failed all the attempts - deal with the consequences. logging.info("Retried downloading the spreadsheet data three times in vain.") return self.table for row in self._rows: self.table.append(import_from_spreadsheet(row.to_dict())) return self.table def update_fields(self, updates, id_name): """ @param id_name: the name of the field by which the rows can be queried @param updates: a dict of dicts. The index is the value of the id_name field. Each dict contains the fields of a row that must be updated. @return: nothing """ # update the in-memory table for entry in self.table: id = entry[id_name] if id in updates: for label in updates[id]: entry[label] = updates[id][label] # update the spreadsheet for entry in self._rows: id = entry.get_value(id_name) if id and id in updates: entry.from_dict(export_for_spreadsheet(updates[id])) for attempt in range(5): try: self._client.update(entry) except httplib.HTTPException as e: logging.info(e.message) time.sleep(0.5) continue else: break else: # we failed all the attempts - deal with the consequences. logging.info("Retried updating the spreadsheet data three times in vain.") return logging.info('On index updated row with ' + id_name + '=' + id) if updates: self.sync_table() def add_rows(self, additions, id_name): """ @param additions: a dict of dicts. Each dict contains the fields of a row that must be added. @return: """ # update the in-memory table for id in additions: self.table.append(additions[id]) # update the spreadsheet for id in additions: additions[id][id_name] = id # to make sure this field is also filled in if it wasn't explicitly in the dict! entry = ListEntry() entry.from_dict(export_for_spreadsheet(additions[id])) for attempt in range(5): try: self._client.add_list_entry( entry, self._google_spreadsheet_key, google_spreadsheet_first_worksheet_id ) except httplib.HTTPException as e: logging.info(e.message) time.sleep(0.5) continue else: break else: # we failed all the attempts - deal with the consequences. logging.info("Retried adding rows to the spreadsheet data three times in vain.") return logging.info('On index added row with id = ' + id) if additions: self.sync_table()
def __init__(self, basedir=None, **kwargs): super(SpreadsheetFactory, self).__init__(basedir, **kwargs) self.bearerToken = OAuth2BearerToken(self.credentials) self.ssc = SpreadsheetsClient(auth_token=self.bearerToken)
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 UserSpreadsheet: def __init__(self, credentials, sheet_name, Alt, BT, Miners): self.credentials = credentials self.gd_client = SpreadsheetsClient() self.spreadsheet_name = sheet_name self.Alt = Alt self.BT = BT self.Miners=Miners auth2token = gauth.OAuth2TokenFromCredentials(self.credentials) auth2token.authorize(self.gd_client) def create_spreadsheet(self): from apiclient.discovery import build service = build('drive', 'v2') http = self.credentials.authorize(httplib2.Http()) body = { 'mimeType': 'application/vnd.google-apps.spreadsheet', 'title': self.spreadsheet_name, } file = service.files().insert(body=body).execute(http=http) def Run(self): num_coins = len(self.Alt.currency) #query for spreadsheet q = SpreadsheetQuery(title= self.spreadsheet_name,title_exact=True) try: self.gd_client.get_spreadsheets(query = q).entry[0].get_spreadsheet_key() except: return logging.error("Error: No ExcellCoin SpreadSheet") self.spreadsheet_key = self.gd_client.get_spreadsheets(query = q).entry[0].get_spreadsheet_key() #get the first row data to figure out what coins to update. #if blank update all cq = CellQuery(range='A1',return_empty=True) cs = self.gd_client.GetCells(self.spreadsheet_key,'od6',q=cq) coins = cs.entry[0].cell.input_value if coins != '': num_coins = len(coins.split()) cq = CellQuery(range=('R1C1:R%iC3'%(num_coins+13)),return_empty=True) cs = self.gd_client.GetCells(self.spreadsheet_key,'od6',q=cq) count = 1 cs.entry[count*3].cell.input_value='%s'%str(datetime.now()) cs.entry[count*3+1].cell.input_value='USD/BTC' #update the bitcoin to usd rates count+=1 for i,coin in enumerate(self.BT.currency): cs.entry[3*count].cell.input_value=self.BT.currency[i] cs.entry[3*count+1].cell.input_value='%.2f'%self.BT.price[i] count+=1 #update the Altcoin to BTC rates count+=1 cs.entry[3*count].cell.input_value='Altcoins' cs.entry[3*count+1].cell.input_value='Exchange Rate' cs.entry[3*count+2].cell.input_value='Volume in BTC' count+=1 if coins == '': for i,coin in enumerate(self.Alt.currency): cs.entry[3*count].cell.input_value='%s'%coin cs.entry[3*count+1].cell.input_value='%.8f'%self.Alt.price[i] cs.entry[3*count+2].cell.input_value='%.2f'%self.Alt.volume[i] count+=1 else: for i,coin in enumerate(coins.split()): try: if coin in self.Alt.currency: index = self.Alt.currency.index(coin) cs.entry[3*count].cell.input_value=self.Alt.currency[index] cs.entry[3*count+1].cell.input_value='%.8f'%self.Alt.price[index] cs.entry[3*count+2].cell.input_value='%.2f'%self.Alt.volume[index] count+=1 except: logging.error("Error: unable to update coins%s"%coin) count+=2 cs.entry[3*count].cell.input_value='MiningPool' cs.entry[3*count+1].cell.input_value='Workers' cs.entry[3*count+2].cell.input_value='HashRate' count+=1 for Miners in self.Miners: ############ Hash Rate ################### try: if Miners != 'none': site = Miners.split(',')[0] nickname = Miners.split(',')[1] mining_token = Miners.split(',')[2] workers, hash_rate = gb.get_stats(site,mining_token) cs.entry[3*count].cell.input_value=nickname cs.entry[3*count+1].cell.input_value='%i'%int(workers) cs.entry[3*count+2].cell.input_value='%i'%int(hash_rate) count+=1 except: logging.error("Error: unable to update hashrate %s"%Miners.split(',')[0]) count+=1 #send a batch job to update everything in spreadsheet at once try: objData = gdata.spreadsheets.data batch = objData.BuildBatchCellsUpdate(self.spreadsheet_key, 'od6') for cell in cs.entry: batch.add_batch_entry(cell, cell.id.text, batch_id_string=cell.title.text, operation_string='update') self.gd_client.batch(batch, force=True) except: return logging.error("Error: Unable to send batch operation")
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)