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.