def open_or_create_sheet(self, sheet):
"""
DEPRECATED, use the `create` param for `open_sheet` instead
Open a worksheet. If it doesn't exist, create it first.
"""
deprecate(
"open_or_create_sheet is deprecated, pass create=True to open_sheet"
)
self.open_sheet(sheet, True)
def sheet_to_df(self,
index=1,
headers=1,
header_rows=1,
start_row=1,
sheet=None):
"""
Pull a worksheet into a DataFrame.
:param int index: col number of index column, 0 or None for no index (default 1)
:param int headers: (DEPRECATED - use `header_rows`) number of rows that represent
headers (default 1)
:param int header_rows: number of rows that represent headers (default 1)
:param int start_row: row number for first row of headers or data (default 1)
:param str,int sheet: optional, if you want to open a different sheet first,
see :meth:`open_sheet <gspread_pandas.client.Spread.open_sheet>` (default None)
:returns: a DataFrame with the data from the Worksheet
"""
if sheet:
self.open_sheet(sheet)
if not self.sheet:
raise Exception("No open worksheet")
if headers != 1:
deprecate("headers has been deprecated, use header_rows instead")
header_rows = headers
vals = self._retry_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 Exception(
"Column headers don't match number of data columns")
return parse_sheet_index(df, index)
def __init__(
self,
user_creds_or_client,
spread,
sheet=None,
config=None,
create_spread=False,
create_sheet=False,
scope=default_scope,
user="******",
credentials=None,
client=None,
):
if user_creds_or_client is not None:
deprecate(
"user_creds_or_client will be removed in v2. Use optional 'user',"
"'credentials' or 'client' params instead and pass None for "
"user_creds_or_client to prevent this message. If you only use"
"one set of creds, move ``~/.config/gspread_pandas/creds/<user>`` to "
"``~/.config/gspread_pandas/creds/default`` to avoid having to pass"
"the 'user' param every time"
)
if isinstance(user_creds_or_client, Client):
self.client = user_creds_or_client
elif isinstance(user_creds_or_client, (basestring, OAuth2Credentials)):
self.client = Client(
user_creds_or_client, config, scope, _deprecation_notice=False
)
else:
raise TypeError(
"user_creds_or_client needs to be a string, "
"OAuth2Credentials, or Client object"
)
else:
if isinstance(client, Client):
self.client = client
else:
self.client = Client(
user, config, scope, credentials, _deprecation_notice=False
)
monkey_patch_request(self.client)
self.open(spread, sheet, create_sheet, create_spread)
def _login(self, user_or_creds, config, credentials, _deprecation_notice):
if isinstance(credentials, OAuth2Credentials):
creds = credentials
elif isinstance(user_or_creds, OAuth2Credentials):
if _deprecation_notice:
deprecate(
"user_or_creds will be changed to 'user' in v2, please use "
"'credentials'"
)
creds = user_or_creds
elif isinstance(user_or_creds, basestring):
if _deprecation_notice:
deprecate(
"user_or_creds will become optional and be renamed to 'user' in v2"
)
creds = get_creds(user_or_creds, config, self.scope)
else:
raise TypeError(
"user_or_creds needs to be a string or OAuth2Credentials object"
)
super().__init__(creds)
super().login()
def test_deprecate(recwarn):
with pytest.deprecated_call() as calls:
util.DEPRECATION_WARNINGS_ENABLED = False
util.deprecate("")
assert len(calls) == 1
util.DEPRECATION_WARNINGS_ENABLED = True
util.deprecate("")
assert warnings.filters[0][0] == "default"
assert len(calls) == 2
util.DEPRECATION_WARNINGS_ENABLED = False
util.deprecate("")
assert warnings.filters[0][0] == "ignore"
assert len(calls) == 3
def df_to_sheet(
self,
df,
index=True,
headers=True,
start=(1, 1),
replace=False,
sheet=None,
raw_column_names=None,
raw_columns=None,
freeze_index=False,
freeze_headers=False,
fill_value="",
add_filter=False,
merge_headers=False,
flatten_headers_sep=None,
):
"""
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.spread.Spread.open_sheet>`
(default None)
raw_column_names : list, str
(DEPRECATED use raw_collumns instead) optional, list of columns
from your dataframe that you want interpreted as RAW input in
google sheets.
raw_columns : list, str
optional, list of columns from your dataframe that you want
interpreted as RAW input in google sheets. This can be column
names or column numbers.
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)
merge_headers : bool
whether to merge cells in the header that have the same value
(default False)
flatten_headers_sep : str
if you want to flatten your multi-headers to a single row,
you can pass the string that you'd like to use to concatenate
the levels, for example, ': ' (default None)
Returns
-------
None
"""
self._ensure_sheet(sheet)
header = df.columns
index_size = df.index.nlevels if index else 0
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,
flatten_headers_sep)
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
end = (req_rows, req_cols)
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))
if raw_column_names:
deprecate(
"raw_column_names is deprecated, please use raw_columns instead."
)
raw_columns = find_col_indexes(raw_column_names, header,
start[COL] + index_size)
elif raw_columns:
if is_indexes(raw_columns):
offset = index_size + start[COL] - 1
raw_columns = [ix + offset for ix in raw_columns]
else:
raw_columns = find_col_indexes(raw_columns, header,
start[COL] + index_size)
self.update_cells(
start=start,
end=end,
vals=[str(val) for row in df_list for val in row],
raw_columns=raw_columns,
)
self.freeze(
None if not freeze_headers else header_size + start[ROW] - 1,
None if not freeze_index else index_size + start[COL] - 1,
)
if add_filter:
self.add_filter((header_size + start[ROW] - 2, start[COL] - 1),
(req_rows, req_cols))
if merge_headers:
self.spread.batch_update({
"requests":
create_merge_headers_request(self.sheet.id, header, start,
index_size)
})
self.refresh_spread_metadata()
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='',
start_row=1,
start_col=1):
"""
Save a DataFrame into a worksheet.
:param DataFrame df: the DataFrame to save
:param bool index: whether to include the index in worksheet (default True)
:param bool headers: whether to include the headers in the worksheet (default True)
:param tuple,str start: tuple indicating (row, col) or string like 'A1' for top left
cell
:param bool replace: whether to remove everything in the sheet first (default False)
:param str,int,Worksheet sheet: 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)
:param bool freeze_index: whether to freeze the index columns (default False)
:param bool freeze_headers: whether to freeze the header rows (default False)
:param str fill_value: value to fill nulls with (default '')
:param int start_row: (DEPRECATED - use `start`) row number for first row of headers or data (default 1)
:param int start_col: (DEPRECATED - use `start`) column number for first column of headers or data (default 1)
"""
if sheet:
self.open_sheet(sheet, create=True)
if not self.sheet:
raise Exception("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)
df_list = header_rows + df_list
start = self._get_cell_as_tuple(start)
# Check deprecated params.. will be removed in 1.0
if start == (1, 1) and (start_row > 1 or start_col > 1):
deprecate(
"start_col and start_row params are deprecated, use start instead"
)
start = (start_row, start_col)
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=[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)
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]),
)
}
)