Spreadsheet

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

A class for a spreadsheet object.

id

id of the spreadsheet

title

title of the spreadsheet

sheet1

Shortcut property for getting the first worksheet.

named_ranges

All named ranges in thi spreadsheet

defaultformat

deafault cell format

updated

Last time the spreadsheet was modified, in RFC 3339 format

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

Update all sheet properies.

Parameters:
  • jsonsheet – json object to update values form if not specified, will fetch it and update (see google api, for json format)
  • fetch_sheets – if the sheets should be fetched
worksheets(sheet_property=None, value=None, force_fetch=False)[source]

Get all worksheets filtered by a property.

Parameters:
  • sheet_property – proptery to filter - ‘title’, ‘index’, ‘id’
  • value – value of property to match
  • force_fetch – update the sheets, from cloud
Returns:

list of all worksheets

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

Returns a worksheet with specified property.

Parameters:
  • property ('title','index','id') – A property of a worksheet. If there’re multiple worksheets with the same title, first one will be returned.
  • value – value of given property
Returns:

instance of Worksheet

Example. Getting worksheet named ‘Annual bonuses’

>>> sht = client.open('Sample one')
>>> worksheet = sht.worksheet('title','Annual bonuses')
worksheet_by_title(title)[source]

returns worksheet by title

Parameters:title – title of the sheet
Returns:Spresheet instance
add_worksheet(title, rows=100, cols=26, src_tuple=None, src_worksheet=None)[source]

Adds a new worksheet to a spreadsheet.

Parameters:
  • title – A title of a new worksheet.
  • rows – Number of rows.
  • cols – Number of columns.
  • src_tuple – a tuple (spreadsheet id, worksheet id) specifying a worksheet to copy
  • src_worksheet – source worksheet object to copy values from
Returns:

a newly created worksheets.

del_worksheet(worksheet)[source]

Deletes a worksheet from a spreadsheet.

Parameters:worksheet – The worksheets to be deleted.
find(string, replace=None, regex=True, match_case=False, include_formulas=False, srange=None, sheet=True)[source]

Find and replace cells in spreadsheet

Parameters:
  • string – string to search for
  • replace – string to replace with
  • regex – is the search string regex
  • match_case – match case in search
  • include_formulas – include seach in formula
  • srange – range to search in A1 format
  • sheet – if True - search all sheets, else search specified sheet
share(addr, role='reader', expirationTime=None, is_group=False)[source]

create/update permission for user/group/domain

Parameters:
  • addr – this is the email for user/group and domain address for domains
  • role – permission to be applied (‘owner’,’writer’,’commenter’,’reader’)
  • expirationTime – (Not Implimented) time until this permission should last (datetime)
  • is_group – boolean , Is this a use/group used only when email provided
list_permissions()[source]

list all the permissions of the spreadsheet

Returns:list of permissions as json object
remove_permissions(addr)[source]

Removes all permissions of the user provided

Parameters:addr – email/domain of the user
batch_start()[source]

Start batch mode, where all updates to sheet values will be batched

batch_stop(discard=False)[source]

Stop batch Mode and Update the changes

Parameters:discard – discard all changes done in batch mode

Link the spreadsheet with cloud, so all local changes will be updated instantly, so does all data fetches

Parameters:syncToCloud – true -> update the cloud with local changes false -> update the local copy with cloud

Unlink the spread sheet with cloud, so all local changes will be made on local copy fetched

export(fformat='text/csv:.csv', filename=None)[source]

Export all the worksheet of the worksheet in specified format.

Parameters:
  • fformat – A format of the output as Enum ExportType
  • filename – name of file exported with extension
custom_request(request, fields)[source]

send a custom batch update request for this spreadsheet

Parameters:
  • request – the json batch update request or a list of requests
  • fields – fields to include in the response
Returns:

json Response