def _get_spreadsheet_headers(client, spreadsheet_key, worksheet_id): """Returns a list of headers (the first line of the spreadsheet). Will be in the order they appear in the spreadsheet. """ headers = [] query = CellQuery() query.max_row = '1' query.min_row = '1' feed = client.GetCellsFeed(spreadsheet_key, worksheet_id, query=query, visibility='public', projection='values') # Wish python had a do-while... while True: for entry in feed.entry: headers.append(entry.content.text) # Get the next set of cells if needed. next_link = feed.GetNextLink() if next_link: feed = client.Get(next_link.href, converter=SpreadsheetsCellsFeedFromString) else: break return headers
def get_sheet_headers(self, key, sheet_id, client=None): query = CellQuery() query.max_row = "1" query.return_empty = "true" if not client: client = self._get_client() header_xml = client.GetCellsFeed(key, sheet_id, query=query) # setups our cleaning regex REGEX_CLEAN = "([^\s\?]*)" clean = re.compile(REGEX_CLEAN, re.DOTALL) # squirrels away our header data ret = [] for i, v in enumerate(header_xml.entry): if v.cell.inputValue: ret.append( { "index": i, "friendly": v.cell.inputValue, "programmatic": "".join(clean.findall(v.cell.inputValue.lower().replace("_", ""))), } ) return ret
def headers(self): """ Return the name of all headers currently defined for the table. """ if self._headers is None: query = CellQuery() query.max_row = '1' feed = self._service.GetCellsFeed(self._ss.id, self.id, query=query) self._headers = feed.entry return [normalize_header(h.cell.text) for h in self._headers]
def __init__(self, client, key, worksheet_id, start_row, end_row): self.entries = {} self.batch = SpreadsheetsCellsFeed() self.max_col = 0 self.max_row = 0 self.client = client query = CellQuery() query.return_empty = 'true' query.min_row = str(start_row) query.max_row = str(end_row) self.cells = client.GetCellsFeed(key, worksheet_id, query=query) self.register_all(self.cells.entry)
def set_headers(self,header_names): ''' Set the current worksheet headers given a list. NOTE: header list length must match columns in sheet! ''' self.__ss_check() ws = self.gd_client.GetWorksheetsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id) if int(ws.col_count.text) != len(header_names): raise GDError('Number of headers ({}) does not match columns in spreadsheet ({})!' .format(len(header_names),int(ws.col_count.text))) query = CellQuery(params={'min-row':'1','max-row':'1','min-col':'1','max-col':str(len(header_names))}) cells = self.gd_client.GetCellsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id,query=query) if len(cells.entry) == 0: query = CellQuery(params={'min-row':'1','max-row':'1','min-col':'1','max-col':str(len(header_names)),'return-empty':'true'}) cells = self.gd_client.GetCellsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id,query=query) batchRequest = SpreadsheetsCellsFeed() for idx,val in enumerate(header_names): cells.entry[idx].cell.inputValue = val batchRequest.AddUpdate(cells.entry[idx]) updated = self.gd_client.ExecuteBatch(batchRequest,cells.GetBatchLink().href)
def row_as_list(self,row): ''' Read just a single row into a list of strings. NOTE: this is indexed by cell, so row 1 is the header row! ''' self.__ss_check() mincol = 1 ws = self.gd_client.GetWorksheetsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id) if int(ws.col_count.text) < mincol: return [] q = CellQuery(params={'min-row':str(row),'min-col':str(mincol),'max-row':str(row)}) cells = self.gd_client.GetCellsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id,query=q) return [cellentry.cell.text for cellentry in cells.entry]
def column_as_list(self,column,with_header=False): ''' Read just a single column into a list of strings. Ignore the first row by default because it's the column header. ''' self.__ss_check() if with_header: minrow = 1 else: minrow = 2 ws = self.gd_client.GetWorksheetsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id) if int(ws.row_count.text) < minrow: return [] q = CellQuery(params={'min-row':str(minrow),'min-col':str(column),'max-col':str(column)}) cells = self.gd_client.GetCellsFeed(self.gd_cur_ss_key,self.gd_cur_ws_id,query=q) return [cellentry.cell.text for cellentry in cells.entry]
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')]