Spreadsheet

class gspread.spreadsheet.Spreadsheet(http_client, properties)

The class that represents a spreadsheet.

accept_ownership(permission_id)

Accept the pending ownership request on that file.

It is necessary to edit the permission with the pending ownership.

Note

You can only accept ownership transfer for the user currently being used.

add_worksheet(title, rows, cols, index=None)

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.
index (int) – Position of the sheet.

Returns:

a newly created worksheets.

batch_update(body)

Lower-level method that directly calls spreadsheets/<ID>:batchUpdate.

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

New in version 3.0.

property creationTime: Spreadsheet Creation time.

del_worksheet(worksheet)

Deletes a worksheet from a spreadsheet.

Parameters:: worksheet (Worksheet) – The worksheet to be deleted.

del_worksheet_by_id(worksheet_id: str | int): Deletes a Worksheet by id

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

New in version 3.1.

export(format=ExportFormat.PDF)

Export the spreadsheet in the given format.

Parameters:

file_id (str) – A key of a spreadsheet to export
format (ExportFormat) –
The format of the resulting file. Possible values are:

ExportFormat.PDF, ExportFormat.EXCEL, ExportFormat.CSV, ExportFormat.OPEN_OFFICE_SHEET, ExportFormat.TSV, and ExportFormat.ZIPPED_HTML.

See ExportFormat in the Drive API. Default value is ExportFormat.PDF.

Returns bytes:

The content of the exported file.

fetch_sheet_metadata(params=None)

Similar to :method spreadsheets_get:gspread.http_client.spreadsheets_get, get the spreadsheet form the API but by default does not get the cells data. It only retrieve the the metadata from the spreadsheet.

Parameters:: params (dict) – (optional) the HTTP params for the GET request. By default sets the parameter includeGridData to false.
Returns:: The raw spreadsheet
Return type:: dict

get_lastUpdateTime() → str: Get the lastUpdateTime metadata from the Drive API.

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 gspread.worksheet.Worksheet.
Raises:: WorksheetNotFound: if can’t find the worksheet

Example. To get third worksheet of a spreadsheet:

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

get_worksheet_by_id(id: str | int)

Returns a worksheet with specified worksheet id.

Parameters:: id (str | int) – The id of a worksheet. it can be seen in the url as the value of the parameter ‘gid’.
Returns:: an instance of gspread.worksheet.Worksheet.
Raises:: WorksheetNotFound: if can’t find the worksheet

Example. To get the worksheet 123456 of a spreadsheet:

>>> sht = client.open('My fancy spreadsheet')
>>> worksheet = sht.get_worksheet_by_id(123456)

property id: Spreadsheet ID.

property lastUpdateTime: Spreadsheet last updated time. Only updated on initialisation. For actual last updated time, use get_lastUpdateTime().

list_named_ranges(): Lists the spreadsheet’s named ranges.

list_permissions(): Lists the spreadsheet’s permissions.

list_protected_ranges(sheetid): Lists the spreadsheet’s protected named ranges

property locale: Spreadsheet locale

named_range(named_range)

return a list of gspread.cell.Cell objects from the specified named range.

Parameters:: named_range (str) – A string with a named range value to fetch.

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

reorder_worksheets(worksheets_in_desired_order)

Updates the index property of each Worksheet to reflect its index in the provided sequence of Worksheets.

Parameters:: worksheets_in_desired_order – Iterable of Worksheet objects in desired order.

Note: If you omit some of the Spreadsheet’s existing Worksheet objects from the provided sequence, those Worksheets will be appended to the end of the sequence in the order that they appear in the list returned by gspread.spreadsheet.Spreadsheet.worksheets().

New in version 3.4.

share(email_address, perm_type, role, notify=True, email_message=None, with_link=False)

Share the spreadsheet with other accounts.

Parameters:

email_address (str, None) – user or group e-mail address, domain name or None for ‘anyone’ 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 (bool) – (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')

# Give Otto's family a read permission on this spreadsheet
sh.share('otto-familly@example.com', perm_type='group', role='reader')

property sheet1: Shortcut property for getting the first worksheet.

property timezone: Spreadsheet timeZone

property title: Spreadsheet title.

transfer_ownership(permission_id)

Transfer the ownership of this file to a new user.

It is necessary to first create the permission with the new owner’s email address, get the permission ID then use this method to transfer the ownership.

Note

You can list all permissions using gspread.spreadsheet.Spreadsheet.list_permissions().

Warning

You can only transfer ownership to a new user, you cannot transfer ownership to a group or a domain email address.

update_drive_metadata() → None: Fetches the drive metadata from the Drive API and updates the cached values in _properties dict.

update_locale(locale): Update the locale of the spreadsheet. Can be any of the ISO 639-1 language codes, such as: de, fr, en, … Or an ISO 639-2 if no ISO 639-1 exists. Or a combination of the ISO language code and country code, such as en_US, de_CH, fr_FR, …

Note

Note: when updating this field, not all locales/languages are supported.

update_timezone(timezone): Updates the current spreadsheet timezone. Can be any timezone in CLDR format such as “America/New_York” or a custom time zone such as GMT-07:00.

update_title(title)

Renames the spreadsheet.

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

property url: Spreadsheet URL.

values_append(range, params, body)

Lower-level method that directly calls spreadsheets/<ID>/values:append.

Parameters:

range (str) – The A1 notation of a range to search for a logical table of data. Values will be appended after the last row of the table.
params (dict) – Values Append Query parameters.
body (dict) – Values Append Request body.

Returns:

Values Append Response body.

Return type:

dict

New in version 3.0.

values_batch_clear(params=None, body=None)

Lower-level method that directly calls spreadsheets/<ID>/values:batchClear

Parameters:

params (dict) – (optional) Values Batch Clear Query parameters.
body (dict) – (optional) Values Batch Clear request body.

Return type:

dict

values_batch_get(ranges, params=None)

Lower-level method that directly calls spreadsheets/<ID>/values:batchGet.

Parameters:

ranges (list) –
List of ranges in the A1 notation of the values to retrieve.
params (dict) – (optional) Values Batch Get Query parameters.

Returns:

Values Batch Get Response body.

Return type:

dict

values_batch_update(body=None)

Lower-level method that directly calls spreadsheets/<ID>/values:batchUpdate.

Parameters:: body (dict) – (optional) Values Batch Update Request body.
Returns:: Values Batch Update Response body.
Return type:: dict

values_clear(range)

Lower-level method that directly calls spreadsheets/<ID>/values:clear.

Parameters:

range (str) –

The A1 notation of the values to clear.

Returns:

Values Clear Response body.

Return type:

dict

New in version 3.0.

values_get(range, params=None)

Lower-level method that directly calls GET spreadsheets/<ID>/values/<range>.

Parameters:

range (str) –
The A1 notation of the values to retrieve.
params (dict) – (optional) Values Get Query parameters.

Returns:

Values Get Response body.

Return type:

dict

New in version 3.0.

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

Lower-level method that directly calls PUT spreadsheets/<ID>/values/<range>.

Parameters:

range (str) –
The A1 notation of the values to update.
params (dict) – (optional) Values Update Query parameters.
body (dict) – (optional) Values Update Request body.

Returns:

Values Update 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 (str) – A title of a worksheet. If there’re multiple worksheets with the same title, first one will be returned.
Returns:: an instance of gspread.worksheet.Worksheet.
Raises:: WorksheetNotFound: if can’t find the worksheet

Example. Getting worksheet named ‘Annual bonuses’

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

worksheets(exclude_hidden: bool = False)

Returns a list of all worksheets in a spreadsheet.

Parameters:: exclude_hidden (bool) – (optional) If set to True will only return visible worksheets. Default is False.
Returns:: a list of worksheets.
Return type:: list