def load_google_sheet(self, sheet_url=None, collection=None):
if not in_notebook:
raise Exception(
"Con not use google spreadsheets outside colab notebooks")
xlsx_workbook = Workbook(write_only=True)
gc = self.get_gspread_client()
spreadsheet = gc.open_by_url(sheet_url)
worksheets = spreadsheet.worksheets()
for sheet in worksheets:
if sheet.title.startswith('#'):
continue
xlsx_sheet = xlsx_workbook.create_sheet(sheet.title)
data = spreadsheet.values_get(sheet.title,
params={
"valueRenderOption":
"UNFORMATTED_VALUE",
"dateTimeRenderOption":
"FORMATTED_STRING"
})
values = data.get('values')
if not values:
continue
data = fill_gaps(values)
for row in data:
xlsx_sheet.append(row)
xlsx_workbook.save('converted.xlsx')
self.load_xlsx(collection=collection, infile='converted.xlsx')
def _get_columns(self, cols, value_render_option="FORMATTED_VALUE"):
"""
Returns a list of all values in `cols`.
Empty cells in this list will be rendered as :const:`None`.
Parameters
----------
cols : list of ints
Column numbers.
value_render_option : str
Determines how values should be rendered in the the output. Possible
values are "FORMATTED_VALUE", "FORMULA", and "UNFORMATTED_VALUE"
(Default value = "FORMATTED_VALUE")
Returns
-------
"""
ranges = get_ranges(self.sheet.title, cols)
data = self.spread.values_batch_get(
ranges,
params={
"valueRenderOption": value_render_option,
"majorDimension": "COLUMNS",
},
)
try:
return fill_gaps(
[col.get("values", [[]])[0] for col in data["valueRanges"]])
except KeyError:
return []
def _get_all_unformatted_values(self):
data = self.worksheet.spreadsheet.values_get(
self.worksheet.title,
params={'valueRenderOption': "UNFORMATTED_VALUE"})
try:
return fill_gaps(data['values'])
except KeyError:
return []
def _get_all_values(worksheet, evaluate_formulas):
data = worksheet.spreadsheet.values_get(
worksheet.title,
params={
"valueRenderOption": (
"UNFORMATTED_VALUE" if evaluate_formulas else "FORMULA"
),
"dateTimeRenderOption": "FORMATTED_STRING",
},
)
(row_offset, column_offset) = (1, 1)
(last_row, last_column) = (worksheet.row_count, worksheet.col_count)
values = data.get("values", [])
rect_values = fill_gaps(
values,
rows=last_row - row_offset + 1,
cols=last_column - column_offset + 1,
)
cells = [
Cell(row=i + row_offset, col=j + column_offset, value=value)
for i, row in enumerate(rect_values)
for j, value in enumerate(row)
]
# defaultdicts fill in gaps for empty rows/cells not returned by gdocs
rows = defaultdict(lambda: defaultdict(str))
for cell in cells:
row = rows.setdefault(int(cell.row), defaultdict(str))
row[cell.col] = cell.value
if not rows:
return []
all_row_keys = chain.from_iterable(row.keys() for row in rows.values())
rect_cols = range(1, max(all_row_keys) + 1)
rect_rows = range(1, max(rows.keys()) + 1)
return [[rows[i][j] for j in rect_cols] for i in rect_rows]
