API Reference

`gsheets.Sheets`	Collection of spreadsheets available from given OAuth 2.0 credentials or API key.
`gsheets.models.SpreadSheet`	Fetched collection of worksheets.
`gsheets.models.SheetsView`	Read-only view on the list of worksheets in a spreadsheet.
`gsheets.models.WorkSheet`	Two-dimensional table with cells accessible via A1 notation.
`gsheets.get_credentials`	Make OAuth 2.0 credentials for scopes from `secrets` and `storage` files.
`gsheets.build_service`	Return a service endpoint for interacting with a Google API.

Sheets

class gsheets.Sheets(credentials=None, developer_key=None)

Collection of spreadsheets available from given OAuth 2.0 credentials or API key.

__contains__(id)

Return if there is a spreadsheet with the given id.

Parameters:: id (str) – unique alphanumeric id of the spreadsheet
Returns:: True if it can be fetched else False
Return type:: bool

__getitem__(id)

Fetch and return the spreadsheet with the given id.

Parameters:: id (str) – unique alphanumeric id of the spreadsheet
Returns:: new SpreadSheet instance
Return type:: SpreadSheet
Raises:: KeyError – if no spreadsheet with the given id is found

__iter__()

Fetch and yield all available spreadsheets.

Yields:: new SpreadSheet spreadsheet instances

__len__()

Return the number of available spreadsheets.

Returns:: number of spreadsheets
Return type:: int

find(title)

Fetch and return the first spreadsheet with the given title.

Parameters:: title (str) – title/name of the spreadsheet to return
Returns:: new SpreadSheet instance
Return type:: SpreadSheet
Raises:: KeyError – if no spreadsheet with the given title is found

findall(title=None)

Fetch and return a list of spreadsheets with the given title.

Parameters:: title (str) – title/name of the spreadsheets to return, or None for all
Returns:: list of new SpreadSheet instances (possibly empty)
Return type:: list

classmethod from_developer_key(developer_key)

Return a spreadsheet collection using an API key.

Parameters:: developer_key (str) – Google API key authorized for Drive and Sheets APIs
Returns:: new Sheets instance using the specified key
Return type:: Sheets

classmethod from_files(secrets=None, storage=None, scopes=None, *, no_webserver=False)

Return a spreadsheet collection making OAauth 2.0 credentials.

Parameters:

secrets (str) – location of secrets file (default: '~/client_secrets.json')
storage (str) – location of storage file (default: '~/storage.json')
scopes – scope URL(s) or 'read' or 'write' (default: 'read')
no_webserver (bool) – URL/code prompt instead of webbrowser auth

Returns:

new Sheets instance with OAauth 2.0 credentials

Return type:

Sheets

get(id_or_url, default=None)

Fetch and return the spreadsheet with the given id or url.

Parameters:: id_or_url (str) – unique alphanumeric id or URL of the spreadsheet
Returns:: New SpreadSheet instance or given default if none is found
Raises:: ValueError – if an URL is given from which no id could be extracted

ids()

Return a list of all available spreadsheet ids.

Returns:: list of unique alphanumeric id strings
Return type:: list

iterfiles()

Yield (id, title) pairs for all available spreadsheets.

Yields:: pairs of unique id (str) and title/name (str)

titles(unique=False)

Return a list of all available spreadsheet titles.

Parameters:: unique (bool) – drop duplicates
Returns:: list of title/name strings
Return type:: list

SpreadSheet

class gsheets.models.SpreadSheet(id, title, sheets, service)

Fetched collection of worksheets.

__contains__(id)

Return if the spreadsheet has a worksheet with the given id.

Parameters:: id (int) – numeric id of the worksheet
Returns:: True if such a worksheet is present else False
Return type:: bool
Raises:: TypeError – if id is not an int

__getitem__(id)

Return the worksheet with the given id.

Parameters:

id – numeric id of the worksheet

Returns:

contained worksheet object

Return type:

WorkSheet

Raises:

TypeError – if id is not an int
KeyError – if the spreadsheet has no worksheet with the given id

__iter__()

Yield all contained worksheets.

Yields:: WorkSheet objects (spreadsheet members)

__len__()

Return the number of contained worksheets.

Returns:: number of worksheets
Return type:: int

find(title)

Return the first worksheet with the given title.

Parameters:: title (str) – title/name of the worksheet to return
Returns:: contained worksheet object
Return type:: WorkSheet
Raises:: KeyError – if the spreadsheet has no no worksheet with the given title

findall(title=None)

Return a list of worksheets with the given title.

Parameters:: title (str) – title/name of the worksheets to return, or None for all
Returns:: list of contained worksheet instances (possibly empty)
Return type:: list

property first_sheet: The first worksheet of the spreadsheet.

get(id, default=None)

Return the worksheet with the given id or the given default.

Parameters:: id – numeric id of the worksheet
Returns:: contained worksheet object or given default
Return type:: WorkSheet
Raises:: ValueError – if id is not an int

property id

Unique alphanumeric id of the spreadsheet (str).

see https://developers.google.com/sheets/guides/concepts#spreadsheet_id

property sheets: List view of the worksheets in the spreadsheet (positional access).

property title: Title/name of the spreadsheet (str).

to_csv(*, encoding='utf-8', dialect='excel', make_filename='%(title)s - %(sheet)s.csv')

Dump all worksheets of the spreadsheet to individual CSV files.

Parameters:

encoding (str) – result string encoding
dialect (str) – csv dialect name or object to use
make_filename – template or one-argument callable returning the filename

If make_filename is a string, it is string-interpolated with an infos-dictionary with the fields id (spreadhseet id), title (spreadsheet title), sheet (worksheet title), gid (worksheet id), index (worksheet index), and dialect CSV dialect to generate the filename: filename = make_filename % infos.

If make_filename is a callable, it will be called with the infos-dictionary to generate the filename: filename = make_filename(infos).

property url: URL pointing to the first worksheet of the spreadsheet (str).

SheetsView

class gsheets.models.SheetsView(items)

Read-only view on the list of worksheets in a spreadsheet.

__getitem__(index)

Return the worksheet at the given index (position).

Parameters:: index – zero-based position or slice
Raises:: IndexError – if index is out of range

ids()

Return a list of contained worksheet ids.

Returns:: list of numeric ids (int)
Return type:: list

titles(*, unique=False)

Return a list of contained worksheet titles.

Parameters:: unique (bool) – drop duplicates
Returns:: list of titles/name strings
Return type:: list

WorkSheet

class gsheets.models.WorkSheet(id, title, index, values)

Two-dimensional table with cells accessible via A1 notation.

__getitem__(index)

Return the value(s) of the given cell(s).

Parameters:

index (str) – cell/row/col index (‘A1’, ‘2’, ‘B’) or slice (‘A1’:’C3’)

Returns:

value (cell), list(col, row), or nested list (two-dimentional slice)

Raises:

TypeError – if index is not a string or slice of strings
ValueError – if index canot be parsed
IndexError – if index is out of range

at(row, col)

Return the value at the given cell position.

Parameters:

row (int) – zero-based row number
col (int) – zero-based column number

Returns:

cell value

Raises:

TypeError – if row or col is not an int
IndexError – if the position is out of range

property id

Stable numeric worksheet id (int), unique within its spreadsheet.

see https://developers.google.com/sheets/guides/concepts#sheet_id

property index: Zero-based position of the worksheet.

property ncells: Number of cells in the worksheet (int).

property ncols: Number of columns in the worksheet (int).

property nrows: Number of rows in the worksheet (int).

property spreadsheet: Containing spreadsheet of the worksheet.

property title: Worksheet title/name (str).

to_csv(filename=None, *, encoding='utf-8', dialect='excel', make_filename='%(title)s - %(sheet)s.csv')

Dump the worksheet to a CSV file.

Parameters:

filename (str) – result filename (if None use make_filename)
encoding (str) – result string encoding
dialect (str) – csv dialect name or object to use
make_filename – template or one-argument callable returning the filename

If make_filename is a string, it is string-interpolated with an infos-dictionary with the fields id (spreadhseet id), title (spreadsheet title), sheet (worksheet title), gid (worksheet id), index (worksheet index), and dialect CSV dialect to generate the filename: filename = make_filename % infos.

If make_filename is a callable, it will be called with the infos-dictionary to generate the filename: filename = make_filename(infos).

to_frame(*, assign_name=False, **kwargs)

Return a pandas DataFrame loaded from the worksheet data.

Parameters:

assign_name (bool) – set name attribute on the DataFrame to sheet title.
**kwargs – passed to pandas.read_csv() (e.g. header, index_col)

Returns:

new DataFrame instance

Return type:

pandas.DataFrame

property url: URL pointing to the worksheet (str).

values(*, column_major=False)

Return a nested list with the worksheet values.

Parameters:: column_major (bool) – as list of columns (default list of rows)
Returns:: list of lists with values
Return type:: list

Low-level functions

gsheets.get_credentials(scopes=None, secrets=None, storage=None, *, no_webserver=False)

Make OAuth 2.0 credentials for scopes from secrets and storage files.

Parameters:

scopes – scope URL(s) or 'read', 'write' (default: 'read')
secrets – location of secrets file (default: '~/client_secrets.json')
storage – location of storage file (default: '~/storage.json')
no_webserver – url/code prompt instead of webbrowser based auth

see https://developers.google.com/sheets/quickstart/python see https://developers.google.com/api-client-library/python/guide/aaa_client_secrets

gsheets.build_service(name=None, **kwargs): Return a service endpoint for interacting with a Google API.