API Reference

Top level

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

Login to Google API using OAuth2 credentials. This is a shortcut function which instantiates gspread.client.Client and performs login right away.

Returns:gspread.Client instance.

Client

class gspread.Client(auth, session=None)

An instance of this class communicates with Google API.

Parameters:
  • auth – An OAuth2 credential object. Credential objects are those created by the oauth2client library. https://github.com/google/oauth2client
  • session – (optional) A session object capable of making HTTP requests while persisting some parameters across requests. Defaults to requests.Session.
>>> c = gspread.Client(auth=OAuthCredentialObject)
copy(file_id, title=None, copy_permissions=False)

Copies a spreadsheet.

Parameters:
  • file_id – 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 original spreadsheet to new spreadsheet.
Returns:

a Spreadsheet instance.

New in version 3.1.0.

Note

In order to use this method, you need to add https://www.googleapis.com/auth/drive to your oAuth scope.

Example:

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

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

create(title)

Creates a new spreadsheet.

Parameters:title (str) – A title of a new spreadsheet.
Returns:a Spreadsheet instance.

Note

In order to use this method, you need to add https://www.googleapis.com/auth/drive to your oAuth scope.

Example:

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

Otherwise you will get an Insufficient Permission error when you try to create a new spreadsheet.

del_spreadsheet(file_id)

Deletes a spreadsheet.

Parameters:file_id (str) – a spreadsheet ID (aka 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 – (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.)
login()

Authorize client.

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')
open_by_key(key)

Opens a spreadsheet specified by key.

Parameters:key (str) – A key of a spreadsheet as it appears in a URL in a browser.
Returns:a Spreadsheet instance.
>>> c = gspread.authorize(credentials)
>>> c.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.
>>> c = gspread.authorize(credentials)
>>> c.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 objects: a spreadsheet, a worksheet and a cell.

Note

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

class gspread.models.Spreadsheet(client, properties)

The class that represents a spreadsheet.

add_worksheet(title, rows, cols)

Adds a new worksheet to a spreadsheet.

Parameters:
  • title (str) – A title of a new worksheet.
  • rows (int) – Number of rows.
  • cols (int) – Number of columns.
Returns:

a newly created worksheets.

batch_update(body)

Lower-level method that directly calls spreadsheets.batchUpdate.

Parameters:body (dict) – Request body.
Returns:Response body.
Return type:dict

New in version 3.0.

del_worksheet(worksheet)

Deletes a worksheet from a spreadsheet.

Parameters:worksheet (Worksheet) – The worksheet to be deleted.
duplicate_sheet(source_sheet_id, insert_sheet_index=None, new_sheet_id=None, new_sheet_name=None)

Duplicates the contents of a sheet.

Parameters:
  • source_sheet_id (int) – The sheet ID to duplicate.
  • insert_sheet_index (int) – (optional) The zero-based index where the new sheet should be inserted. The index of all sheets after this are incremented.
  • new_sheet_id (int) – (optional) The ID of the new sheet. If not set, an ID is chosen. If set, the ID must not conflict with any existing sheet ID. If set, it must be non-negative.
  • new_sheet_name (str) – (optional) The name of the new sheet. If empty, a new name is chosen for you.
Returns:

a newly created <gspread.models.Worksheet>.

New in version 3.1.0.

get_worksheet(index)

Returns a worksheet with specified index.

Parameters:index (int) – An index of a worksheet. Indexes start from zero.
Returns:an instance of gsperad.models.Worksheet or None if the worksheet is not found.

Example. To get first worksheet of a spreadsheet:

>>> sht = client.open('My fancy spreadsheet')
>>> worksheet = sht.get_worksheet(0)
id

Spreadsheet ID.

list_permissions()

Lists the spreadsheet’s permissions.

remove_permissions(value, role='any')

Remove permissions from a user or domain.

Parameters:
  • value (str) – User or domain to remove permissions from
  • role (str) – (optional) Permission to remove. Defaults to all permissions.

Example:

# Remove Otto's write permission for this spreadsheet
sh.remove_permissions('otto@example.com', role='writer')

# Remove all Otto's permissions for this spreadsheet
sh.remove_permissions('otto@example.com')
share(value, perm_type, role, notify=True, email_message=None, with_link=False)

Share the spreadsheet with other accounts.

Parameters:
  • value (str, None) – user or group e-mail address, domain name or None for ‘default’ type.
  • perm_type (str) – The account type. Allowed values are: user, group, domain, anyone.
  • role (str) – 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) The email to be sent if notify=True
  • with_link (bool) – (optional) Whether the link is required for this permission

Example:

# Give Otto a write permission on this spreadsheet
sh.share('otto@example.com', perm_type='user', role='writer')

# Transfer ownership to Otto
sh.share('otto@example.com', perm_type='user', role='owner')
sheet1

Shortcut property for getting the first worksheet.

title

Spreadsheet title.

updated

Deprecated since version 2.0.

This feature is not supported in Sheets API v4.

values_append(range, params, body)

Lower-level method that directly calls spreadsheets.values.append.

Parameters:
Returns:

Response body.

Return type:

dict

New in version 3.0.

values_clear(range)

Lower-level method that directly calls spreadsheets.values.clear.

Parameters:range (str) –

The A1 notation of the values to clear.

Returns:Response body.
Return type:dict

New in version 3.0.

values_get(range, params=None)

Lower-level method that directly calls spreadsheets.values.get.

Parameters:
Returns:

Response body.

Return type:

dict

New in version 3.0.

values_update(range, params=None, body=None)

Lower-level method that directly calls spreadsheets.values.update.

Parameters:
Returns:

Response body.

Return type:

dict

Example:

sh.values_update(
    'Sheet1!A2',
    params={
        'valueInputOption': 'USER_ENTERED'
    },
    body={
        'values': [[1, 2, 3]]
    }
)

New in version 3.0.

worksheet(title)

Returns a worksheet with specified title.

Parameters:title (int) – A title of a worksheet. If there’re multiple worksheets with the same title, first one will be returned.
Returns:an instance of gsperad.models.Worksheet.

Example. Getting worksheet named ‘Annual bonuses’

>>> sht = client.open('Sample one')
>>> worksheet = sht.worksheet('Annual bonuses')
worksheets()

Returns a list of all worksheets in a spreadsheet.

class gspread.models.Worksheet(spreadsheet, properties)

The class that represents a single sheet in a spreadsheet (aka “worksheet”).

acell(label, value_render_option='FORMATTED_VALUE')

Returns an instance of a gspread.models.Cell.

Parameters:
  • label (str) – Cell label in A1 notation Letter case is ignored.
  • value_render_option (str) – (optional) Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.

Example:

>>> worksheet.acell('A1')
<Cell R1C1 "I'm cell A1">
add_cols(cols)

Adds colums to worksheet.

Parameters:cols (int) – Number of new columns to add.
add_rows(rows)

Adds rows to worksheet.

Parameters:rows (int) – Number of new rows to add.
append_row(values, value_input_option='RAW')

Adds a row to the worksheet and populates it with values. Widens the worksheet if there are more values than columns.

Parameters:
  • values – List of values for the new row.
  • value_input_option (str) – (optional) Determines how input data should be interpreted. See ValueInputOption in the Sheets API.
cell(row, col, value_render_option='FORMATTED_VALUE')

Returns an instance of a gspread.models.Cell located at row and col column.

Parameters:
  • row (int) – Row number.
  • col (int) – Column number.
  • value_render_option (str) – (optional) Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.

Example:

>>> worksheet.cell(1, 1)
<Cell R1C1 "I'm cell A1">
clear()

Clears all cells in the worksheet.

col_count

Number of columns.

col_values(col, value_render_option='FORMATTED_VALUE')

Returns a list of all values in column col.

Empty cells in this list will be rendered as None.

Parameters:
  • col (int) – Column number.
  • value_render_option (str) – (optional) Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.
delete_row(index)

“Deletes the row from the worksheet at the specified index.

Parameters:index (int) – Index of a row for deletion.
duplicate(insert_sheet_index=None, new_sheet_id=None, new_sheet_name=None)

Duplicate the sheet.

Parameters:
  • insert_sheet_index (int) – (optional) The zero-based index where the new sheet should be inserted. The index of all sheets after this are incremented.
  • new_sheet_id (int) – (optional) The ID of the new sheet. If not set, an ID is chosen. If set, the ID must not conflict with any existing sheet ID. If set, it must be non-negative.
  • new_sheet_name (str) – (optional) The name of the new sheet. If empty, a new name is chosen for you.
Returns:

a newly created <gspread.models.Worksheet>.

New in version 3.1.0.

export(format)

Deprecated since version 2.0.

This feature is not supported in Sheets API v4.

find(query)

Finds the first cell matching the query.

Parameters:query (str, re.RegexObject) – A literal string to match or compiled regular expression.
findall(query)

Finds all cells matching the query.

Parameters:query (str, re.RegexObject) – A literal string to match or compiled regular expression.
get_all_records(empty2zero=False, head=1, default_blank='', allow_underscores_in_numeric_literals=False)

Returns a list of dictionaries, all of them having the contents of the spreadsheet with the head row as keys and each of these dictionaries holding the contents of subsequent rows of cells as values.

Cell values are numericised (strings that can be read as ints or floats are converted).

Parameters:
  • empty2zero (bool) – (optional) Determines whether empty cells are converted to zeros.
  • head (int) – (optional) Determines wich row to use as keys, starting from 1 following the numeration of the spreadsheet.
  • default_blank (str) – (optional) Determines whether empty cells are converted to something else except empty string or zero.
  • allow_underscores_in_numeric_literals (bool) – (optional) Allow underscores in numeric literals, as introduced in PEP 515
get_all_values()

Returns a list of lists containing all cells’ values as strings.

Note

Empty trailing rows and columns will not be included.

id

Worksheet ID.

insert_row(values, index=1, value_input_option='RAW')

Adds a row to the worksheet at the specified index and populates it with values.

Widens the worksheet if there are more values than columns.

Parameters:
  • values – List of values for the new row.
  • index (int) – (optional) Offset for the newly inserted row.
  • value_input_option (str) – (optional) Determines how input data should be interpreted. See ValueInputOption in the Sheets API.
range(*args, **kwargs)

Returns a list of Cell objects from a specified range.

Parameters:name (str) – A string with range value in A1 notation, e.g. ‘A1:A5’.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:
  • first_row (int) – Row number
  • first_col (int) – Row number
  • last_row (int) – Row number
  • last_col (int) – Row number

Example:

>>> # Using A1 notation
>>> worksheet.range('A1:B7')
[<Cell R1C1 "42">, ...]

>>> # Same with numeric boundaries
>>> worksheet.range(1, 1, 7, 2)
[<Cell R1C1 "42">, ...]
resize(rows=None, cols=None)

Resizes the worksheet. Specify one of rows or cols.

Parameters:
  • rows (int) – (optional) New number of rows.
  • cols (int) – (optional) New number columns.
row_count

Number of rows.

row_values(row, value_render_option='FORMATTED_VALUE')

Returns a list of all values in a row.

Empty cells in this list will be rendered as None.

Parameters:
  • row (int) – Row number.
  • value_render_option (str) – (optional) Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.
title

Worksheet title.

update_acell(label, value)

Updates the value of a cell.

Parameters:
  • label (str) – Cell label in A1 notation. Letter case is ignored.
  • value – New value.

Example:

worksheet.update_acell('A1', '42')
update_cell(row, col, value)

Updates the value of a cell.

Parameters:
  • row (int) – Row number.
  • col (int) – Column number.
  • value – New value.

Example:

worksheet.update_cell(1, 1, '42')
update_cells(cell_list, value_input_option='RAW')

Updates many cells at once.

Parameters:
  • cell_list – List of Cell objects to update.
  • value_input_option (str) – (optional) Determines how input data should be interpreted. See ValueInputOption in the Sheets API.

Example:

# Select a range
cell_list = worksheet.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
worksheet.update_cells(cell_list)
update_title(title)

Renames the worksheet.

Parameters:title (str) – A new title.
updated

Deprecated since version 2.0.

This feature is not supported in Sheets API v4.

class gspread.models.Cell(row, col, value='')

An instance of this class represents a single cell in a worksheet.

col

Column number of the cell.

input_value

Deprecated since version 2.0.

This feature is not supported in Sheets API v4.

row

Row number of the cell.

value = None

Value of the cell.

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

Example:

>>> a1_to_rowcol('A1')
(1, 1)

Exceptions

exception gspread.exceptions.GSpreadException

A base class for gspread’s exceptions.

exception gspread.exceptions.APIError(response)