API Reference

Top level

gspread.oauth(scopes=['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'], flow=<function local_server_flow>, credentials_filename='/home/docs/.config/gspread/credentials.json', authorized_user_filename='/home/docs/.config/gspread/authorized_user.json')

Authenticate with OAuth Client ID.

By default this function will use the local server strategy and open the authorization URL in the user’s browser:

gc = gspread.oauth()

Another option is to run a console strategy. This way, the user is instructed to open the authorization URL in their browser. Once the authorization is complete, the user must then copy & paste the authorization code into the application:

gc = gspread.oauth(flow=gspread.auth.console_flow)

scopes parameter defaults to read/write scope available in gspread.auth.DEFAULT_SCOPES. It’s read/write for Sheets and Drive API:

DEFAULT_SCOPES =[
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

You can also use gspread.auth.READONLY_SCOPES for read only access. Obviously any method of gspread that updates a spreadsheet will not work in this case:

gc = gspread.oauth(scopes=gspread.auth.READONLY_SCOPES)

sh = gc.open("A spreadsheet")
sh.sheet1.update('A1', '42')   # <-- this will not work

If you’re storing your user credentials in a place other than the default, you may provide a path to that file like so:

gc = gspread.oauth(
    credentials_filename='/alternative/path/credentials.json',
    authorized_user_filename='/alternative/path/authorized_user.json',
)
Parameters:
  • scopes (list) – The scopes used to obtain authorization.
  • flow (function) – OAuth flow to use for authentication. Defaults to local_server_flow()
  • credentials_filename (str) –

    Filepath (including name) pointing to a credentials .json file. Defaults to DEFAULT_CREDENTIALS_FILENAME:

    • %APPDATA%gspreadcredentials.json on Windows
    • ~/.config/gspread/credentials.json everywhere else
  • authorized_user_filename (str) –

    Filepath (including name) pointing to an authorized user .json file. Defaults to DEFAULT_AUTHORIZED_USER_FILENAME:

    • %APPDATA%gspreaduthorized_user.json on Windows
    • ~/.config/gspread/authorized_user.json everywhere else
Return type:

gspread.Client

gspread.service_account(filename='/home/docs/.config/gspread/service_account.json', scopes=['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'])

Authenticate using a service account.

scopes parameter defaults to read/write scope available in gspread.auth.DEFAULT_SCOPES. It’s read/write for Sheets and Drive API:

DEFAULT_SCOPES =[
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

You can also use gspread.auth.READONLY_SCOPES for read only access. Obviously any method of gspread that updates a spreadsheet will not work in this case.

Parameters:
  • filename (str) – The path to the service account json file.
  • scopes (list) – The scopes used to obtain authorization.
Return type:

gspread.Client

gspread.authorize(credentials, client_class=<class 'gspread.client.Client'>)

Login to Google API using OAuth2 credentials. This is a shortcut function which instantiates client_class. By default gspread.Client is used.

Returns:client_class instance.

Client

class gspread.Client(auth, session=None)

An instance of this class communicates with Google API.

Parameters:
>>> c = gspread.Client(auth=OAuthCredentialObject)
copy(file_id, title=None, copy_permissions=False, folder_id=None)

Copies a spreadsheet.

Parameters:
  • file_id (str) – A key of a spreadsheet to copy.
  • title (str) – (optional) A title for the new spreadsheet.
  • copy_permissions (bool) – (optional) If True, copy permissions from the original spreadsheet to the new spreadsheet.
  • folder_id (str) – Id of the folder where we want to save the spreadsheet.
Returns:

a Spreadsheet instance.

New in version 3.1.0.

Note

If you’re using custom credentials without the Drive scope, you need to add https://www.googleapis.com/auth/drive to your OAuth scope in order to use this method.

Example:

scope = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

Otherwise, you will get an Insufficient Permission error when you try to copy a spreadsheet.

create(title, folder_id=None)

Creates a new spreadsheet.

Parameters:
  • title (str) – A title of a new spreadsheet.
  • folder_id (str) – Id of the folder where we want to save the spreadsheet.
Returns:

a Spreadsheet instance.

del_spreadsheet(file_id)

Deletes a spreadsheet.

Parameters:file_id (str) – a spreadsheet ID (a.k.a file ID).
import_csv(file_id, data)

Imports data into the first page of the spreadsheet.

Parameters:data (str) – A CSV string of data.

Example:

# Read CSV file contents
content = open('file_to_import.csv', 'r').read()

gc.import_csv(spreadsheet.id, content)

Note

This method removes all other worksheets and then entirely replaces the contents of the first worksheet.

insert_permission(file_id, value, perm_type, role, notify=True, email_message=None, with_link=False)

Creates a new permission for a file.

Parameters:
  • file_id (str) – a spreadsheet ID (aka file ID).
  • value (str, None) – user or group e-mail address, domain name or None for ‘default’ type.
  • perm_type (str) – (optional) The account type. Allowed values are: user, group, domain, anyone
  • role (str) – (optional) The primary role for this user. Allowed values are: owner, writer, reader
  • notify (str) – (optional) Whether to send an email to the target user/domain.
  • email_message (str) – (optional) An email message to be sent if notify=True.
  • with_link (bool) – (optional) Whether the link is required for this permission to be active.

Examples:

# Give write permissions to otto@example.com

gc.insert_permission(
    '0BmgG6nO_6dprnRRUWl1UFE',
    'otto@example.org',
    perm_type='user',
    role='writer'
)

# Make the spreadsheet publicly readable

gc.insert_permission(
    '0BmgG6nO_6dprnRRUWl1UFE',
    None,
    perm_type='anyone',
    role='reader'
)
list_permissions(file_id)

Retrieve a list of permissions for a file.

Parameters:file_id (str) – a spreadsheet ID (aka file ID).
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.
>>> gc.open('My fancy spreadsheet')
open_by_key(key)

Opens a spreadsheet specified by key (a.k.a Spreadsheet ID).

Parameters:key (str) – A key of a spreadsheet as it appears in a URL in a browser.
Returns:a Spreadsheet instance.
>>> gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
open_by_url(url)

Opens a spreadsheet specified by url.

Parameters:url (str) – URL of a spreadsheet as it appears in a browser.
Returns:a Spreadsheet instance.
Raises:gspread.SpreadsheetNotFound – if no spreadsheet with specified url is found.
>>> gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
openall(title=None)

Opens all available spreadsheets.

Parameters:title (str) – (optional) If specified can be used to filter spreadsheets by title.
Returns:a list of Spreadsheet instances.
remove_permission(file_id, permission_id)

Deletes a permission from a file.

Parameters:
  • file_id (str) – a spreadsheet ID (aka file ID.)
  • permission_id (str) – an ID for the permission.

Models

The models represent common spreadsheet entities: a spreadsheet, a worksheet and a cell.

Note

The classes described below should not be instantiated by the end-user. Their instances result from calling other objects’ methods.

Utils

gspread.utils

This module contains utility functions.

gspread.utils.rowcol_to_a1(row, col)

Translates a row and column cell address to A1 notation.

Parameters:
  • row (int, str) – The row of the cell to be converted. Rows start at index 1.
  • col – The column of the cell to be converted. Columns start at index 1.
Returns:

a string containing the cell’s coordinates in A1 notation.

Example:

>>> rowcol_to_a1(1, 1)
A1
gspread.utils.a1_to_rowcol(label)

Translates a cell’s address in A1 notation to a tuple of integers.

Parameters:label (str) – A cell label in A1 notation, e.g. ‘B1’. Letter case is ignored.
Returns:a tuple containing row and column numbers. Both indexed from 1 (one).
Return type:tuple

Example:

>>> a1_to_rowcol('A1')
(1, 1)
gspread.utils.a1_range_to_grid_range(name, sheet_id=None)

Converts a range defined in A1 notation to a dict representing a GridRange.

All indexes are zero-based. Indexes are half open, e.g the start index is inclusive and the end index is exclusive: [startIndex, endIndex).

Missing indexes indicate the range is unbounded on that side.

Examples:

>>> a1_range_to_grid_range('A1:A1')

{‘startRowIndex’: 0, ‘endRowIndex’: 1, ‘startColumnIndex’: 0, ‘endColumnIndex’: 1}

>>> a1_range_to_grid_range('A3:B4')
{'startRowIndex': 2, 'endRowIndex': 4, 'startColumnIndex': 0, 'endColumnIndex': 2}
>>> a1_range_to_grid_range('A:B')
{'startColumnIndex': 0, 'endColumnIndex': 2}
>>> a1_range_to_grid_range('A5:B')
{'startRowIndex': 4, 'startColumnIndex': 0, 'endColumnIndex': 2}
>>> a1_range_to_grid_range('A1')
{'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 1}
>>> a1_range_to_grid_range('A')
{'startColumnIndex': 0, 'endColumnIndex': 1}
>>> a1_range_to_grid_range('1')
{'startRowIndex': 0, 'endRowIndex': 1}
>>> a1_range_to_grid_range('A1', sheet_id=0)
{'sheetId': 0, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 1}
gspread.utils.cast_to_a1_notation(method)

Decorator function casts wrapped arguments to A1 notation in range method calls.

gspread.utils.absolute_range_name(sheet_name, range_name=None)

Return an absolutized path of a range.

>>> absolute_range_name("Sheet1", "A1:B1")
"'Sheet1'!A1:B1"
>>> absolute_range_name("Sheet1", "A1")
"'Sheet1'!A1"
>>> absolute_range_name("Sheet1")
"'Sheet1'"
>>> absolute_range_name("Sheet'1")
"'Sheet''1'"
>>> absolute_range_name("Sheet''1")
"'Sheet''''1'"
>>> absolute_range_name("''sheet12''", "A1:B2")
"'''''sheet12'''''!A1:B2"
gspread.utils.is_scalar(x)

Return True if the value is scalar.

A scalar is not a sequence but can be a string.

>>> is_scalar([])
False
>>> is_scalar([1, 2])
False
>>> is_scalar(42)
True
>>> is_scalar('nice string')
True
>>> is_scalar({})
True
>>> is_scalar(set())
True
gspread.utils.filter_dict_values(D)

Return a shallow copy of D with all None values excluded.

>>> filter_dict_values({'a': 1, 'b': 2, 'c': None})
{'a': 1, 'b': 2}
>>> filter_dict_values({'a': 1, 'b': 2, 'c': 0})
{'a': 1, 'b': 2, 'c': 0}
>>> filter_dict_values({})
{}
>>> filter_dict_values({'imnone': None})
{}
gspread.utils.accepted_kwargs(**default_kwargs)
>>> @accepted_kwargs(d='d', e=None)
... def foo(a, b, c='c', **kwargs):
...     return {
...         'a': a,
...         'b': b,
...         'c': c,
...         'd': kwargs['d'],
...         'e': kwargs['e'],
...     }
...
>>> foo('a', 'b')
{'a': 'a', 'b': 'b', 'c': 'c', 'd': 'd', 'e': None}
>>> foo('a', 'b', 'NEW C')
{'a': 'a', 'b': 'b', 'c': 'NEW C', 'd': 'd', 'e': None}
>>> foo('a', 'b', e='Not None')
{'a': 'a', 'b': 'b', 'c': 'c', 'd': 'd', 'e': 'Not None'}
>>> foo('a', 'b', d='NEW D')
{'a': 'a', 'b': 'b', 'c': 'c', 'd': 'NEW D', 'e': None}
>>> foo('a', 'b', a_typo='IS DETECTED')
Traceback (most recent call last):
...
TypeError: foo got unexpected keyword arguments: ['a_typo']
>>> foo('a', 'b', d='NEW D', c='THIS DOES NOT WORK BECAUSE OF d')
Traceback (most recent call last):
...
TypeError: foo got unexpected keyword arguments: ['c']

Auth

gspread.auth

Simple authentication with OAuth.

gspread.auth.local_server_flow(scopes, port=0, filename='/home/docs/.config/gspread/credentials.json')

Run an OAuth flow using a local server strategy.

Creates an OAuth flow and runs google_auth_oauthlib.flow.InstalledAppFlow.run_local_server. This will start a local web server and open the authorization URL in the user’s browser.

Pass this function to flow parameter of oauth() to run a local server flow.

gspread.auth.console_flow(scopes, filename='/home/docs/.config/gspread/credentials.json')

Run an OAuth flow using a console strategy.

Creates an OAuth flow and runs google_auth_oauthlib.flow.InstalledAppFlow.run_console.

Pass this function to flow parameter of oauth() to run a console strategy.

Exceptions

exception gspread.exceptions.GSpreadException

A base class for gspread’s exceptions.

exception gspread.exceptions.APIError(response)