def unmerge_cells(self, start="A1", end=None, sheet=None): """Unmerge all cells between the start and end cells. Use defaults to unmerge all cells in the sheet. Parameters ---------- start : tuple,str Tuple indicating (row, col) or string like 'A1' (default A1) end : tuple,str Tuple indicating (row, col) or string like 'A1' (default last cell in sheet) sheet : str,int,Worksheet optional, if you want to open or create a different sheet before adding the filter, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet is not None: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") if end is None: end = self.get_sheet_dims() self.spread.batch_update( {"requests": create_unmerge_cells_request(self.sheet.id, start, end)} )
def freeze(self, rows=None, cols=None, sheet=None): """Freeze rows and/or columns for the open worksheet. Parameters ---------- rows : int the DataFrame to save (Default value = None) cols : int whether to include the index in worksheet (default True) sheet : str,int,Worksheet optional, if you want to open or create a different sheet before freezing, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") if rows is None and cols is None: return self.spread.batch_update( {"requests": create_frozen_request(self.sheet.id, rows, cols)}) self.refresh_spread_metadata()
def merge_cells(self, start, end, merge_type="MERGE_ALL", sheet=None): """Merge cells between the start and end cells. Use merge_type if you want to change the behavior of the merge. Parameters ---------- start : tuple,str Tuple indicating (row, col) or string like 'A1' end : tuple, str Tuple indicating (row, col) or string like 'A1' merge_type : str One of MERGE_ALL, MERGE_ROWS, or MERGE_COLUMNS (default "MERGE_ALL") sheet : str,int,Worksheet optional, if you want to open or create a different sheet before adding the filter, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet is not None: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") self.spread.batch_update( {"requests": create_merge_cells_request(self.sheet.id, start, end)} )
def add_filter(self, start_row=None, end_row=None, start_col=None, end_col=None, sheet=None): """ Add filters to data in the open worksheet. :param int start_row: First row to include in filter; this will be the filter header (Default 0) :param int end_row: Last row to include in filter (Default last row in sheet) :param int start_col: First column to include in filter (Default 0) :param int end_col: Last column to include in filter (Default last column in sheet) :param str,int,Worksheet sheet: optional, if you want to open or create a different sheet before adding the filter, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) """ if sheet: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") dims = self.get_sheet_dims() self.spread.batch_update({ 'requests': create_filter_request(self.sheet.id, start_row or 0, end_row or dims[0], start_col or 0, end_col or dims[1]) })
def update_cells(self, start, end, vals, sheet=None): """ Update the values in a given range. The values should be listed in order from left to right across rows. :param tuple,str start: tuple indicating (row, col) or string like 'A1' :param tuple,str end: tuple indicating (row, col) or string like 'Z20' :param list vals: array of values to populate :param str,int,Worksheet sheet: optional, if you want to open a different sheet first, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) """ if sheet: self.open_sheet(sheet) if not self.sheet: raise NoWorksheetException("No open worksheet") if start == end: return for start_cell, end_cell, val_chunks in self._get_update_chunks( start, end, vals): rng = get_range(start_cell, end_cell) cells = self._retry_range(rng) if len(val_chunks) != len(cells): raise MissMatchException( "Number of chunked values doesn't match number of cells") for val, cell in zip(val_chunks, cells): cell.value = val self._retry_update(cells)
def sheet_to_df(self, index=1, header_rows=1, start_row=1, sheet=None): """Pull a worksheet into a DataFrame. Parameters ---------- index : int col number of index column, 0 or None for no index (default 1) header_rows : int number of rows that represent headers (default 1) start_row : int row number for first row of headers or data (default 1) sheet : str,int optional, if you want to open a different sheet first, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- DataFrame DataFrame with the data from the Worksheet """ if sheet is not None: self.open_sheet(sheet) if not self.sheet: raise NoWorksheetException("No open worksheet") vals = self._retry_func(self.sheet.get_all_values) vals = self._fix_merge_values(vals)[start_row - 1 :] col_names = parse_sheet_headers(vals, header_rows) # remove rows where everything is null, then replace nulls with '' df = ( pd.DataFrame(vals[header_rows or 0 :]) .replace("", np.nan) .dropna(how="all") .fillna("") ) if col_names is not None: if len(df.columns) == len(col_names): df.columns = col_names elif len(df) == 0: # if we have headers but no data, set column headers on empty DF df = df.reindex(columns=col_names) else: raise MissMatchException( "Column headers don't match number of data columns" ) return parse_sheet_index(df, index)
def update_cells(self, start, end, vals, sheet=None): """Update the values in a given range. The values should be listed in order from left to right across rows. Parameters ---------- start : tuple,str tuple indicating (row, col) or string like 'A1' end : tuple,str tuple indicating (row, col) or string like 'Z20' vals : list array of values to populate sheet : str,int,Worksheet optional, if you want to open a different sheet first, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet is not None: self.open_sheet(sheet) if not self.sheet: raise NoWorksheetException("No open worksheet") for start_cell, end_cell, val_chunks in self._get_update_chunks( start, end, vals ): rng = get_range(start_cell, end_cell) cells = self._retry_func(partial(self.sheet.range, rng)) if len(val_chunks) != len(cells): raise MissMatchException( "Number of chunked values doesn't match number of cells" ) for val, cell in zip(val_chunks, cells): cell.value = val self._retry_func(partial(self.sheet.update_cells, cells, "USER_ENTERED"))
def clear_sheet(self, rows=1, cols=1, sheet=None): """Reset open worksheet to a blank sheet with given dimensions. Parameters ---------- rows : int number of rows (default 1) cols : int number of columns (default 1) sheet : str,int,Worksheet optional; name, index, or Worksheet, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet is not None: self.open_sheet(sheet) if not self.sheet: raise NoWorksheetException("No open worksheet") # TODO: if my merge request goes through, use sheet.frozen_*_count frozen_rows = self._sheet_metadata["properties"]["gridProperties"].get( "frozenRowCount", 0 ) frozen_cols = self._sheet_metadata["properties"]["gridProperties"].get( "frozenColCount", 0 ) row_resize = max(rows, frozen_rows + 1) col_resize = max(cols, frozen_cols + 1) self.sheet.resize(row_resize, col_resize) self.update_cells( start=(1, 1), end=(row_resize, col_resize), vals=["" for i in range(0, row_resize * col_resize)], )
def clear_sheet(self, rows=1, cols=1, sheet=None): """ Reset open worksheet to a blank sheet with given dimensions. :param int rows: number of rows (default 1) :param int cols: number of columns (default 1) :param str,int,Worksheet sheet: optional; name, index, or Worksheet, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) """ if sheet: self.open_sheet(sheet) if not self.sheet: raise NoWorksheetException("No open worksheet") self.sheet.resize(rows, cols) self.update_cells(start=(1, 1), end=(rows, cols), vals=['' for i in range(0, rows * cols)])
def _ensure_sheet(self, sheet): if sheet is not None: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet")
def df_to_sheet( self, df, index=True, headers=True, start=(1, 1), replace=False, sheet=None, freeze_index=False, freeze_headers=False, fill_value="", add_filter=False, ): """Save a DataFrame into a worksheet. Parameters ---------- df : DataFrame the DataFrame to save index : bool whether to include the index in worksheet (default True) headers : bool whether to include the headers in the worksheet (default True) start : tuple,str tuple indicating (row, col) or string like 'A1' for top left cell (default (1,1)) replace : bool whether to remove everything in the sheet first (default False) sheet : str,int,Worksheet optional, if you want to open or create a different sheet before saving, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) freeze_index : bool whether to freeze the index columns (default False) freeze_headers : bool whether to freeze the header rows (default False) fill_value : str value to fill nulls with (default '') add_filter : bool whether to add a filter to the uploaded sheet (default False) Returns ------- None """ if sheet: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") index_size = df.index.nlevels header_size = df.columns.nlevels if index: df = df.reset_index() df = fillna(df, fill_value) df_list = df.values.tolist() if headers: header_rows = parse_df_col_names(df, index, index_size) df_list = header_rows + df_list start = get_cell_as_tuple(start) sheet_rows, sheet_cols = self.get_sheet_dims() req_rows = len(df_list) + (start[ROW] - 1) req_cols = len(df_list[0]) + (start[COL] - 1) or 1 if replace: # this takes care of resizing self.clear_sheet(req_rows, req_cols) else: # make sure sheet is large enough self.sheet.resize(max(sheet_rows, req_rows), max(sheet_cols, req_cols)) self.update_cells( start=start, end=(req_rows, req_cols), vals=[str(val) for row in df_list for val in row], ) self.freeze( None if not freeze_headers else header_size, None if not freeze_index else index_size, ) if add_filter: self.add_filter(header_size + start[0] - 2, req_rows, start[1] - 1, req_cols)
def add_filter( self, start_row=None, end_row=None, start_col=None, end_col=None, start=None, end=None, sheet=None, ): """Add filters to data in the open worksheet. Parameters ---------- start_row : int (deprecated, use 'start') First row to include in filter; this will be the filter header (default 0) end_row : int (deprecated, use 'end') Last row to include in filter (default last row in sheet) start_col : int (deprecated, use 'start') First column to include in filter (default 0) end_col : int (deprecated, use 'end') Last column to include in filter (default last column in sheet) start : tuple,str Tuple indicating (row, col) or string like 'A1' (default 'A1') end : tuple, str Tuple indicating (row, col) or string like 'A1' (default last cell in sheet) sheet : str,int,Worksheet optional, if you want to open or create a different sheet before adding the filter, see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None) Returns ------- None """ if sheet is not None: self.open_sheet(sheet, create=True) if not self.sheet: raise NoWorksheetException("No open worksheet") dims = self.get_sheet_dims() if ( start_row is not None or end_row is not None or start_col is not None or end_col is not None ): deprecate( "start/end_row/col have been deprecated and will be removed in v2. " "Use 'start' and 'end' instead" ) if start is not None: start_row, start_col = get_cell_as_tuple(start) if end is not None: end_row, end_col = get_cell_as_tuple(end) self.spread.batch_update( { "requests": create_filter_request( self.sheet.id, (start_row or 0, start_col or 0), (end_row or dims[ROW], end_col or dims[COL]), ) } )