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 _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 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 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')]