gspread_pandas¶

Submodules¶

gspread_pandas.spread module¶

class gspread_pandas.spread.Spread(spread, sheet=0, config=None, create_spread=False, create_sheet=False, scope=['openid', 'https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/userinfo.email', 'https://www.googleapis.com/auth/spreadsheets'], user='default', creds=None, client=None, permissions=None)¶

Bases: object

Simple wrapper for gspread to interact with Pandas. It holds an instance of an ‘open’ spreadsheet, an ‘open’ worksheet, and a list of available worksheets.

Each user will be associated with specific OAuth credentials. The authenticated user will need the appropriate permissions to the Spreadsheet in order to interact with it.

Parameters:

spread (str) – name, url, or id of the spreadsheet; must have read access by the authenticated user, see open_spread
sheet (str,int) – optional, name or index of Worksheet, see open_sheet (default 0)
config (dict) – optional, if you want to provide an alternate configuration, see get_config (default None)
create_sheet (bool) – whether to create the worksheet if it doesn’t exist, it wil use the spread value as the sheet title (default False)
create_spread (bool) – whether to create the spreadsheet if it doesn’t exist, it wil use the spread value as the sheet title (default False)
scope (list) – optional, if you’d like to provide your own scope (default default_scope)
user (str) – string indicating the key to a users credentials, which will be stored in a file (by default they will be stored in ~/.config/gspread_pandas/creds/<user> but can be modified with creds_dir property in config). If using a Service Account, this will be ignored. (default “default”)
creds (google.auth.credentials.Credentials) – optional, pass credentials if you have those already (default None)
client (Client) – optionall, if you’ve already instanciated a Client, you can just pass that and it’ll be used instead (default None)
permissions (list) – a list of strings. See add_permissions for the expected format

add_filter(start=None, end=None, sheet=None)¶

Add filters to data in the open worksheet.

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 `open_sheet` (default None)
Returns:
Return type:	None

add_permission(permission)¶

Add a permission to the current spreadsheet.

The format should be: <id>|(<group>)|(<role>)|(<notify>)|(<require_link>) where:

<id> - email address of group or individual, domain, or ‘anyone’ <group> - optional, if the id is a group e-mail, this needs to be ‘group’ or

‘grp’

<role> - optional, one of ‘owner’, ‘writer’, or ‘reader’. If ommited, ‘reader’: will be used

<notify> - optional, if you don’t want to notify the user, pass ‘no’ or ‘false’ <require_link> - optional, if you want to require the user to have the link,

pass ‘link’

For example, to allow anyone with a link in the group admins@example.com to write when they have a link, but without sending a notification to the group: admins@example.com|grp|owner|false|link

Or if you want to give user@example.com reader permissions without a notification: user@example.com|no

Or to give anyone read access: anyone

Parameters:	permissions (string) – A strings meeting the above mentioned format.
Returns:
Return type:	None

add_permissions(permissions)¶

Add permissions to the current spreadsheet. See.

add_permission for format.

Parameters:	permissions (list) – A list of strings meeting the above mentioned format.
Returns:
Return type:	None

clear_sheet(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 `open_sheet` (default None)
Returns:
Return type:	None

create_sheet(name, rows=1, cols=1)¶

Create a new worksheet with the given number of rows and cols.

Automatically opens that sheet after it’s created.

Parameters:	name (str) – name of new Worksheet rows (int) – number of rows (default 1) cols (int) – number of columns (default 1)
Returns:
Return type:	None

delete_sheet(sheet)¶

Delete a worksheet by title. Returns whether the sheet was deleted or not. If current sheet is deleted, the sheet property will be set to None.

Parameters:	sheet (str,Worksheet) – name or Worksheet
Returns:	True if deleted successfully, else False
Return type:	bool

df_to_sheet(df, index=True, headers=True, start=(1, 1), replace=False, sheet=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 `open_sheet` (default None) 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:
Return type:	None

find_sheet(sheet)¶

Find a given worksheet by title or by object comparison.

Parameters:	sheet (str,Worksheet) – name of Worksheet or Worksheet object
Returns:	the Worksheet by the given name or None if not found
Return type:	Worksheet

freeze(rows=None, cols=None, sheet=None)¶

Freeze rows and/or columns for the open worksheet.

Parameters:	rows (int) – number of rows to freeze, use 0 to ‘unfreeze’ (default None) cols (int) – number of columns to freeze, use 0 to ‘unfreeze’ (default None) sheet (str,int,Worksheet) – optional, if you want to open or create a different sheet before freezing, see `open_sheet` (default None)
Returns:
Return type:	None

get_sheet_dims(sheet=None)¶

Get the dimensions of the currently open Worksheet.

Parameters:	sheet (str,int,Worksheet) – optional, if you want to open a different sheet first, see `open_sheet` (default None)
Returns:	a tuple containing (num_rows,num_cols)
Return type:	tuple

list_permissions()¶

List all permissions for this Spreadsheet.

Returns:	a list of dicts indicating the permissions on this spreadsheet
Return type:	list

merge_cells(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 `open_sheet` (default None)
Returns:
Return type:	None

move(path='/', create=True)¶

Move the current spreadsheet to the specified path in your Google drive. If the file is not currently in you drive, it will be added.

Parameters:	path (str) – folder path (Default value = “/”) create (bool) – if true, create folders as needed (Default value = True)

open(spread, sheet=None, create_sheet=False, create_spread=False)¶

Open a spreadsheet, and optionally a worksheet. See.

open_spread and open_sheet.

Parameters:	spread (str) – name, url, or id of Spreadsheet sheet (str,int) – name or index of Worksheet (default None) create_sheet (bool) – whether to create the worksheet if it doesn’t exist, it wil use the `spread` value as the sheet title (default False) create_spread (bool) – whether to create the spreadsheet if it doesn’t exist, it wil use the `spread` value as the sheet title (default False)
Returns:
Return type:	None

open_sheet(sheet, create=False)¶

Open a worksheet. Optionally, if the sheet doesn’t exist then create it first (only when sheet is a str).

Parameters:	sheet (str,int,Worksheet) – name, index, or Worksheet object create (bool) – whether to create the sheet if it doesn’t exist, see `create_sheet` (default False)
Returns:
Return type:	None

open_spread(spread, create=False)¶

Open a spreadsheet. Authorized user must already have read access.

Parameters:	spread (str) – name, url, or id of Spreadsheet create (bool) – whether to create the spreadsheet if it doesn’t exist, it wil use the `spread` value as the sheet title (default False)
Returns:
Return type:	None

refresh_spread_metadata()¶: Refresh spreadsheet metadata.

sheet_to_df(index=1, header_rows=1, start_row=1, unformatted_columns=None, formula_columns=None, 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) unformatted_columns (list) – column numbers or names for columns you’d like to pull in as unformatted values (defaul []) formula_columns (list) – column numbers or names for columns you’d like to pull in as actual formulas (defaul []) sheet (str,int) – optional, if you want to open a different sheet first, see `open_sheet` (default None)
Returns:	DataFrame with the data from the Worksheet
Return type:	DataFrame

unmerge_cells(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 `open_sheet` (default None)
Returns:
Return type:	None

update_cells(start, end, vals, sheet=None, raw_columns=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 `open_sheet` (default None) raw_columns (list, int) – optional, list of column numbers in the google sheet that should be interpreted as “RAW” input
Returns:
Return type:	None

client = None¶: (Client) - Instance of gspread_pandas Client

email¶: (str) - E-mail for the currently authenticated user

sheet = None¶: (gspread.worksheet.Worksheet) - Currently open Worksheet

sheets¶: (list) - List of available Worksheets

spread = None¶: (gspread.spreadsheet.Spreadsheet) - Currently open Spreadsheet

url¶: (str) - Url for this spreadsheet

gspread_pandas.client module¶

class gspread_pandas.client.Client(user='default', config=None, scope=['openid', 'https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/userinfo.email', 'https://www.googleapis.com/auth/spreadsheets'], creds=None, session=None, load_dirs=False)¶

Bases: gspread.client.Client

The gspread_pandas Client extends Client and authenticates using credentials stored in gspread_pandas config.

This class also adds a few convenience methods to explore the user’s google drive for spreadsheets.

Parameters:

user (str) – optional, string indicating the key to a users credentials, which will be stored in a file (by default they will be stored in ~/.config/gspread_pandas/creds/<user> but can be modified with creds_dir property in config). If using a Service Account, this will be ignored. (default “default”)
config (dict) – optional, if you want to provide an alternate configuration, see get_config (default None)
scope (list) – optional, if you’d like to provide your own scope (default default_scope)
creds (google.auth.credentials.Credentials) – optional, pass credentials if you have those already (default None)
session (google.auth.transport.requests.AuthorizedSession) – optional, pass a google.auth.transport.requests.AuthorizedSession or a requests.Session and creds (default None)
load_dirs (bool) – optional, whether you want to load directories and paths on instanciation. if you refresh directories later or perform an action that requires them, they will be loaded at that time. For speed, this is disabled by default (default False)

create_folder(path, parents=True)¶

Create a new folder in your Google drive.

Parameters:	path (str) – folder path parents (bool) – if True, create parent folders as needed (Default value = True)
Returns:	information for the created directory
Return type:	dict

find_folders(folder_name_query='')¶

Return all folders that the user has access to containing folder_name_query in the name.

Parameters:	folder_name_query (str) – Case insensitive string to search in folder name. If empty, it will return all folders.
Returns:	List of folders. Each folder is a dict with the following keys: id, kind, mimeType, and name.
Return type:	list

find_spreadsheet_files_in_folders(folder_name_query)¶

Return all spreadsheets that the user has access to in all the folders that contain folder_name_query in the name. Returns as a dict with each key being the folder name and the value being a list of spreadsheet files.

Parameters:	folder_name_query (str) – Case insensitive string to search in folder name
Returns:	Spreadsheets in each folder. Each entry is a dict with the folder name as the key and a list of spreadsheets as the value. Each spreadsheet is a dict with the following keys: id, kind, mimeType, and name.
Return type:	dict

list_spreadsheet_files(title=None)¶

Return all spreadsheets that the user has access to.

Parameters:	title (str) – name of the spreadsheet, if none is passed it’ll return every file (default None)
Returns:	List of spreadsheets. Each spreadsheet is a dict with the following keys: id, kind, mimeType, and name.
Return type:	list

list_spreadsheet_files_in_folder(folder_id)¶

Return all spreadsheets that the user has access to in a sepcific folder.

Parameters:	folder_id (str) – ID of a folder, see `find_folders`
Returns:	List of spreadsheets. Each spreadsheet is a dict with the following keys: id, kind, mimeType, and name.
Return type:	list

login()¶: Override login since AuthorizedSession now takes care of automatically refreshing tokens when needed.

move_file(file_id, path, create=False)¶

Move a file to the given path.

Parameters:	file_id (str) – file id path (str) – folder path. A path starting with / will use your drive, for shared drives the path will start with <Shared Drive Name>/ (no leading /) create (bool) – whether to create any missing folders (Default value = False)

open(title)¶

Opens a spreadsheet.

Parameters:	title (str) – A title of a spreadsheet.
Returns:	a `Spreadsheet` instance.

If there’s more than one spreadsheet with same title the first one will be opened.

Raises:	gspread.SpreadsheetNotFound – if no spreadsheet with specified title is found.

>>> c = gspread.authorize(credentials)
>>> c.open('My fancy spreadsheet')

refresh_directories()¶: Refresh list of directories for the current user.

directories¶: (list) - list of dicts for all avaliable directories for the current user

email¶: (str) - E-mail for the currently authenticated user

root¶: (dict) - the info for the top level Drive directory for current user

scope = None¶: (list) - Feeds included for the OAuth2 scope

gspread_pandas.conf module¶

gspread_pandas.conf.get_config(conf_dir=None, file_name='google_secret.json')¶

Get config for Google client. Looks in ~/.config/gspread_pandas/google_secret.json by default but you can override it with conf_dir and file_name. The creds_dir value will be set to conf_dir/creds and the directory will be created if it doesn’t exist; if you’d like to override that you can do so by changing the ‘creds_dir’ value in the dict returned by this function.

Download json from https://console.developers.google.com/apis/credentials

Parameters:	conf_dir (str) – Full path to config dir (Default value = get_config_dir()) file_name (str) – (Default value = “google_secret.json”)
Returns:	Dict with necessary contents of google_secret.json
Return type:	dict

gspread_pandas.conf.get_creds(user='default', config=None, scope=['openid', 'https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/userinfo.email', 'https://www.googleapis.com/auth/spreadsheets'], creds_dir=None, save=True)¶

Get google google.auth.credentials.Credentials for the given user. If the user doesn’t have previous creds, they will go through the OAuth flow to get new credentials which will be saved for later use. Credentials will be saved in config[‘creds_dir’], if this value is not set, then they will be stored in a folder named creds in the default config dir (either ~/.config/gspread_pandas or.

$GSPREAD_PANDAS_CONFIG_DIR)

Alternatively, it will get credentials from a service account.

Parameters:	user (str) – Unique key indicating user’s credentials. This is not necessary when using a ServiceAccount and will be ignored (Default value = “default”) config (dict) – Optional, dict with “client_id”, “client_secret”, and “redirect_uris” keys for OAuth or “type”, “client_email”, “private_key”, “private_key_id”, and “client_id” for a Service Account. If None is passed, it will call `get_config()` (Default value = None) creds_dir (str, Path) – Optional, directory to load and store creds from/in. If None, it will use the `creds` subdirectory in the default config location. (Default value = None) scope (list) – Optional, scope to use for Google Auth (Default value = default_scope)
Returns:	Google credentials that can be used with gspread
Return type:	google.auth.credentials.Credentials