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]