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, folder_id=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
folder_id (str) – optional, id of the folder to create the spreadsheet in, used only alongside create_spread. A path can’t name every folder — one shared with you may not appear in your own directory tree at all — so an id is the only way to reach some of them. Get one from find_folders (default None)

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)

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.
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.
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)

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)

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, merge_index=False, append=False, match_columns=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)
merge_index (bool) – whether to merge cells in the index that have the same value (default False)
append (bool) – whether to add the rows below the data already in the sheet instead of overwriting from start. Columns are matched to the existing headers by name, so a reordered or renamed DataFrame still lands under the right headers. Can’t be used with replace (default False)
match_columns (bool) – optional, when appending, whether to use the configured model to resolve column names that don’t match exactly. Defaults to using it whenever GSPREAD_PANDAS_AI_API_KEY is set; pass False to stay with exact and similarity matching only, see smart (default None)

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)

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)

Return type:

None

move(path='/', create=True, folder_id=None)

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)
folder_id (str) – optional, id of the destination folder, used instead of path, see move_file (Default value = None)

open(spread, sheet=None, create_sheet=False, create_spread=False, folder_id=None)

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)
folder_id (str) – optional, id of the folder to create the spreadsheet in, see open_spread (default None)

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)

Return type:

None

open_spread(spread, create=False, folder_id=None)

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)
folder_id (str) – optional, id of the folder to create the spreadsheet in. Only applies when the spreadsheet is created; opening an existing one ignores it. Get an id from find_folders (default None)

Return type:

None

refresh_spread_metadata(): Refresh spreadsheet metadata.

reorder_sheets(order)

Move worksheets into the given order.

Parameters:: order (list) – worksheets, by name, index or Worksheet, in the order you want them. Worksheets you leave out keep their positions relative to each other and follow the ones you named.
Return type:: None

sheet_to_df(index=1, header_rows=1, start_row=1, unformatted_columns=None, formula_columns=None, sheet=None, dropna=True, detect_layout=False, convert_types=False)

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, or -1 for every column (default None)
formula_columns (list) – column numbers or names for columns you’d like to pull in as actual formulas, or -1 for every column (default None)
sheet (str,int) – optional, if you want to open a different sheet first, see open_sheet (default None)
dropna (bool) – whether to remove rows where everything is null (default True)
detect_layout (bool) – whether to work out start_row and header_rows from the sheet instead of using the values passed in, for sheets that open with a title or a blank row before the table. Uses the configured model when GSPREAD_PANDAS_AI_API_KEY is set and falls back to skipping preamble rows otherwise, see detect_structure (default False)
convert_types (bool) – whether to give columns their real types instead of leaving everything as strings. A column is only converted when every non-empty value in it converts cleanly. Uses the configured model for columns strict inference can’t place when GSPREAD_PANDAS_AI_API_KEY is set, see convert_types (default False)

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)

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

Return type:

None

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

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

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

property sheets: (list) - List of available Worksheets

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

property 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: 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, folder_id=None)

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)
folder_id (str) – optional, id of the destination folder, used instead of path. Names aren’t unique in Drive and a folder shared with you may not show up in a path lookup at all, so an id is the only way to name some destinations (Default value = None)

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.

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

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

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

scope: (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