Spreadsheet¶
-
class
gspread.spreadsheet.
Spreadsheet
(client, properties)¶ The class that represents a spreadsheet.
-
add_worksheet
(title, rows, cols, index=None)¶ Adds a new worksheet to a spreadsheet.
Parameters: Returns: a newly created
worksheets
.
-
batch_update
(body)¶ Lower-level method that directly calls spreadsheets/<ID>:batchUpdate.
Parameters: body (dict) – Request body. Returns: Response body. Return type: dict New in version 3.0.
-
creationTime
¶ Spreadsheet Creation time.
-
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.worksheet.Worksheet
New in version 3.1.
-
export
(format='application/pdf')¶ Export the spreadsheet in the given format.
Parameters: - file_id (str) – A key of a spreadsheet to export
- format (str) –
The format of the resulting file. Possible values are:
ExportFormat.PDF
,ExportFormat.EXCEL
,ExportFormat.CSV
,ExportFormat.OPEN_OFFICE_SHEET
,ExportFormat.TSV
, andExportFormat.ZIPPED_HTML
.See ExportFormat in the Drive API. Default value is
ExportFormat.PDF
.
Returns bytes: The content of the exported file.
-
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 worksheetExample. To get third worksheet of a spreadsheet:
>>> sht = client.open('My fancy spreadsheet') >>> worksheet = sht.get_worksheet(2)
-
get_worksheet_by_id
(id)¶ Returns a worksheet with specified worksheet id.
Parameters: id (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 worksheetExample. To get the worksheet 123456 of a spreadsheet:
>>> sht = client.open('My fancy spreadsheet') >>> worksheet = sht.get_worksheet_by_id(123456)
-
id
¶ Spreadsheet ID.
-
lastUpdateTime
¶ Spreadsheet last updated time.
-
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
-
locale
¶ Spreadsheet locale
-
named_range
(named_range)¶ return a list of
gspread.cell.Cell
objects from the specified named range.Parameters: name (str) – A string with a named range value to fecth.
-
remove_permissions
(value, role='any')¶ Remove permissions from a user or domain.
Parameters: 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 Worksheets 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 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 (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') # Transfer ownership to Otto sh.share('otto@example.com', perm_type='user', role='owner')
-
sheet1
¶ Shortcut property for getting the first worksheet.
-
timezone
¶ Spreadsheet timeZone
-
title
¶ Spreadsheet title.
-
update_locale
(locale)¶ Update the locale of the spreaddsheet. 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.
-
updated
¶ Deprecated since version 2.0.
This feature is not supported in Sheets API v4.
-
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) – Query parameters.
- body (dict) – Request body.
Returns: Return type: New in version 3.0.
-
values_batch_get
(ranges, params=None)¶ Lower-level method that directly calls spreadsheets/<ID>/values:batchGet.
Parameters: - ranges –
List of ranges in the A1 notation of the values to retrieve.
- params (dict) – (optional) Query parameters.
Returns: Return type: - ranges –
-
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: Response body. Return type: dict New in version 3.0.
-
values_get
(range, params=None)¶ Lower-level method that directly calls spreadsheets/<ID>/values/<range>.
Parameters: - range (str) –
The A1 notation of the values to retrieve.
- params (dict) – (optional) Query parameters.
Returns: Return type: New in version 3.0.
- range (str) –
-
values_update
(range, params=None, body=None)¶ Lower-level method that directly calls spreadsheets/<ID>/values/<range>.
Parameters: - range (str) –
The A1 notation of the values to update.
- params (dict) – (optional) Query parameters.
- body (dict) – (optional) Request body.
Returns: Return type: Example:
sh.values_update( 'Sheet1!A2', params={ 'valueInputOption': 'USER_ENTERED' }, body={ 'values': [[1, 2, 3]] } )
New in version 3.0.
- range (str) –
-
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
()¶ Returns a list of all
worksheets
in a spreadsheet.
-