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]),
)
}
)