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