Spreadsheet

class pygsheets.Spreadsheet(client, jsonsheet=None, id=None)[source]

A class for a spreadsheet object.

worksheet_cls

alias of pygsheets.worksheet.Worksheet

id

Id of the spreadsheet.

title

Title of the spreadsheet.

locale

Locale of the spreadsheet.

sheet1

Direct access to the first worksheet.

url

Url of the spreadsheet.

named_ranges

All named ranges in this spreadsheet.

protected_ranges

All protected ranges in this spreadsheet.

defaultformat

Default cell format used.

updated

Last time the spreadsheet was modified using RFC 3339 format.

update_properties()[source]

Update the sheet properties in cloud

fetch_properties(jsonsheet=None, fetch_sheets=True)[source]

Update all properties of this spreadsheet with the remote.

The provided json representation must be the same as the Google Sheets v4 Response. If no sheet is given this will simply fetch all data from remote and update the local representation.

Reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets

Parameters:
  • jsonsheet – Used to update the spreadsheet.
  • fetch_sheets – Fetch sheets from remote.
worksheets(sheet_property=None, value=None, force_fetch=False)[source]

Get worksheets matching the specified property.

Parameters:
  • sheet_property – Property used to filter (‘title’, ‘index’, ‘id’).
  • value – Value of the property.
  • force_fetch – Fetch data from remote.
Returns:

List of Worksheets.

worksheet(property='index', value=0)[source]

Returns the worksheet with the specified index, title or id.

If several worksheets with the same property are found the first is returned. This may not be the same worksheet every time.

Example: Getting a worksheet named ‘Annual bonuses’

>>> sht = client.open('Sample one')
>>> worksheet = sht.worksheet('title','Annual bonuses')
Parameters:
  • property – The searched property.
  • value – Value of the property.
Returns:

Worksheets.

worksheet_by_title(title)[source]

Returns worksheet by title.

Parameters:title – Title of the sheet
Returns:Worksheets.
add_worksheet(title, rows=100, cols=26, src_tuple=None, src_worksheet=None, index=None)[source]

Creates or copies a worksheet and adds it to this spreadsheet.

When creating only a title is needed. Rows & columns can be adjusted to match your needs. Index can be specified to set position of the sheet.

When copying another worksheet supply the spreadsheet id & worksheet id and the worksheet wrapped in a Worksheet class.

Parameters:
  • title – Title of the worksheet.
  • rows – Number of rows which should be initialized (default 100)
  • cols – Number of columns which should be initialized (default 26)
  • src_tuple – Tuple of (spreadsheet id, worksheet id) specifying the worksheet to copy.
  • src_worksheet – The source worksheet.
  • index – Tab index of the worksheet.
Returns:

Worksheets.

del_worksheet(worksheet)[source]

Deletes the worksheet from this spreadsheet.

Parameters:worksheet – The worksheets to be deleted.
replace(pattern, replacement=None, **kwargs)[source]

Replace values in any cells matched by pattern in all worksheets.

Keyword arguments not specified will use the default value. If the spreadsheet is -

Unlinked:
Uses self.find(pattern, **kwargs) to find the cells and then replace the values in each cell.
Linked:
The replacement will be done by a findReplaceRequest as defined by the Google Sheets API. After the request the local copy is updated.
Parameters:
  • pattern – Match cell values.
  • replacement – Value used as replacement.
  • searchByRegex – Consider pattern a regex pattern. (default False)
  • matchCase – Match case sensitive. (default False)
  • matchEntireCell – Only match on full match. (default False)
  • includeFormulas – Match fields with formulas too. (default False)
find(pattern, **kwargs)[source]

Searches through all worksheets.

Search all worksheets with the options given. If an option is not given, the default will be used. Will return a list of cells for each worksheet packed into a list. If a worksheet has no cell which matches pattern an empty list is added.

Parameters:
  • pattern – The value to search.
  • searchByRegex – Consider pattern a regex pattern. (default False)
  • matchCase – Match case sensitive. (default False)
  • matchEntireCell – Only match on full match. (default False)
  • includeFormulas – Match fields with formulas too. (default False)
Returns:

A list of lists of Cells

share(email_or_domain, role='reader', type='user', **kwargs)[source]

Share this file with a user, group or domain.

User and groups need an e-mail address and domain needs a domain for a permission. Share sheet with a person and send an email message.

>>> spreadsheet.share('example@gmail.com', role='commenter', type='user', emailMessage='Here is the spreadsheet we talked about!')

Make sheet public with read only access:

>>> spreadsheet.share('', role='reader', type='anyone')
Parameters:
  • email_or_domain – The email address or domain this file should be shared to.
  • role – The role of the new permission.
  • type – The type of the new permission.
  • kwargs – Optional arguments. See DriveAPIWrapper.create_permission documentation for details.
permissions

Permissions for this file.

remove_permission(email_or_domain, permission_id=None)[source]

Remove a permission from this sheet.

All permissions associated with this email or domain are deleted.

Parameters:
  • email_or_domain – Email or domain of the permission.
  • permission_id – (optional) permission id if a specific permission should be deleted.
export(file_format=<ExportType.CSV: 'text/csv:.csv'>, path='', filename='')[source]

Export all worksheets.

The filename must have an appropriate file extension. Each sheet will be exported into a separate file. The filename is extended (before the extension) with the index number of the worksheet to not overwrite each file.

Parameters:
  • file_format – ExportType.<?>
  • path – Path to the directory where the file will be stored. (default: current working directory)
  • filename – Filename (default: spreadsheet id)
delete()[source]

Deletes this spreadsheet.

Leaves the local copy intact. The deleted spreadsheet is permanently removed from your drive and not moved to the trash.

get_developer_metadata(key=None, search_sheets=False)[source]

Fetch developer metadata associated with this spreadsheet

Parameters:
  • key – The key of the metadata to fetch. If unspecified, all metadata will be returned
  • search_sheets – Set to True to also include worksheets in the metadata search
create_developer_metadata(key, value=None)[source]

Create a new developer metadata associated with this spreadsheet

Will return None when in batch mode, otherwise will return a DeveloperMetadata object

Parameters:
  • key – the key of the metadata to be created
  • value – the value of the metadata to be created (optional)
custom_request(request, fields, **kwargs)[source]

Send a custom batch update request to this spreadsheet.

These requests have to be properly constructed. All possible requests are documented in the reference.

Reference: api docs <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request>`__

Parameters:
  • request – One or several requests as dictionaries.
  • fields – Fields which should be included in the response.
  • kwargs – Any other params according to refrence.
Returns:

json response <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/response> __

to_json()[source]

Return this spreadsheet as json resource.