pygsheets Reference

pygsheets is a simple Google Sheets API v4 Wrapper. Some functionality uses the Google Drive API v3 as well.

Authorization

pygsheets.authorize(client_secret='client_secret.json', service_account_file=None, service_account_env_var=None, service_account_json=None, credentials_directory='', scopes=('https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'), custom_credentials=None, local=False, **kwargs)[source]

Authenticate this application with a google account.

See general authorization documentation for details on how to attain the necessary files.

Parameters:
  • client_secret – Location of the oauth2 credentials file.
  • service_account_file – Location of a service account file.
  • service_account_env_var – Use an environment variable to provide service account credentials.
  • service_account_json – pass in json string directly; could use aws secret manager or azure key vault to store value
  • credentials_directory – Location of the token file created by the OAuth2 process. Use ‘global’ to store in global location, which is OS dependent. Default None will store token file in current working directory. Please note that this is override your client secret.
  • custom_credentials – A custom or pre-made credentials object. Will ignore all other params.
  • scopes – The scopes for which the authentication applies.
  • local – If local then a browser will be opened to autheticate
  • kwargs – Parameters to be handed into the client constructor.
Returns:

Client

Warning

The credentials_directory overrides client_secret. So you might be accidently using a different credential than intended, if you are using global credentials_directory in more than one script.

Client

class pygsheets.client.Client(credentials, retries=3, http=None, check=True, seconds_per_quota=100)[source]

Create or access Google spreadsheets.

Exposes members to create new spreadsheets or open existing ones. Use authorize to instantiate an instance of this class.

>>> import pygsheets
>>> c = pygsheets.authorize()

The sheet API service object is stored in the sheet property and the drive API service object in the drive property.

>>> c.sheet.get('<SPREADSHEET ID>')
>>> c.drive.delete('<FILE ID>')
Parameters:
  • credentials – The credentials object returned by google-auth or google-auth-oauthlib.
  • retries – (Optional) Number of times to retry a connection before raising a TimeOut error. Default: 3
  • http – The underlying HTTP object to use to make requests. If not specified, a httplib2.Http instance will be constructed.
  • check – Check for quota error and apply rate limiting.
  • seconds_per_quota – Default value is 100 seconds
spreadsheet_ids(query=None)[source]

Get a list of all spreadsheet ids present in the Google Drive or TeamDrive accessed.

spreadsheet_titles(query=None)[source]

Get a list of all spreadsheet titles present in the Google Drive or TeamDrive accessed.

create(title, template=None, folder=None, folder_name=None, **kwargs)[source]

Create a new spreadsheet.

The title will always be set to the given value (even overwriting the templates title). The template can either be a spreadsheet resource or an instance of Spreadsheet. In both cases undefined values will be ignored.

Parameters:
  • title – Title of the new spreadsheet.
  • template – A template to create the new spreadsheet from.
  • folder – The Id of the folder this sheet will be stored in.
  • folder_name – The Name of the folder this sheet will be stored in.
  • kwargs – Standard parameters (see reference for details).
Returns:

Spreadsheet

open(title)[source]

Open a spreadsheet by title.

In a case where there are several sheets with the same title, the first one found is returned.

>>> import pygsheets
>>> c = pygsheets.authorize()
>>> c.open('TestSheet')
Parameters:title – A title of a spreadsheet.
Returns:Spreadsheet
Raises:pygsheets.SpreadsheetNotFound – No spreadsheet with the given title was found.
open_by_key(key)[source]

Open a spreadsheet by key.

>>> import pygsheets
>>> c = pygsheets.authorize()
>>> c.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
Parameters:key – The key of a spreadsheet. (can be found in the sheet URL)
Returns:Spreadsheet
Raises:pygsheets.SpreadsheetNotFound – The given spreadsheet ID was not found.
open_by_url(url)[source]

Open a spreadsheet by URL.

>>> import pygsheets
>>> c = pygsheets.authorize()
>>> c.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
Parameters:url – URL of a spreadsheet as it appears in a browser.
Returns:Spreadsheet
Raises:pygsheets.SpreadsheetNotFound – No spreadsheet was found with the given URL.
open_all(query='')[source]

Opens all available spreadsheets.

Result can be filtered when specifying the query parameter. On the details on how to form the query:

Reference

Parameters:query – (Optional) Can be used to filter the returned metadata.
Returns:A list of Spreadsheet.
open_as_json(key)[source]

Return a json representation of the spreadsheet.

See Reference for details.

get_range(spreadsheet_id, value_range=None, major_dimension='ROWS', value_render_option=<ValueRenderOption.FORMATTED_VALUE: 'FORMATTED_VALUE'>, date_time_render_option=<DateTimeRenderOption.SERIAL_NUMBER: 'SERIAL_NUMBER'>, value_ranges=None)[source]

Returns a range of values from a spreadsheet. The caller must specify the spreadsheet ID and a range.

Reference: request

Parameters:
  • spreadsheet_id – The ID of the spreadsheet to retrieve data from.
  • value_range – The A1 notation of the values to retrieve.
  • value_ranges – The list of A1 notation of the values to retrieve.
  • major_dimension – The major dimension that results should use. For example, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS will return [[1,2],[3,4]], whereas requesting range=A1:B2,majorDimension=COLUMNS will return [[1,3],[2,4]].
  • value_render_option – How values should be represented in the output. The default render option is ValueRenderOption.FORMATTED_VALUE.
  • date_time_render_option – How dates, times, and durations should be represented in the output. This is ignored if valueRenderOption is FORMATTED_VALUE. The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
Returns:

An array of arrays with the values fetched. Returns an empty array if no values were fetched. Values are dynamically typed as int, float or string.

Models

Python objects for the main Google Sheets API Resources: spreadsheet, worksheet, cell and datarange.

Helper Classes

The Drive API is wrapped by DriveAPIWrapper, and the Sheets API is wrapped by SheetAPIWrapper. They Only implements functionality used by this package. You would never need to access this directly.

Also there are many Enums defined for model properties or function parameters.

Exceptions

exception pygsheets.AuthenticationError[source]

An error during authentication process.

exception pygsheets.SpreadsheetNotFound[source]

Trying to open non-existent or inaccessible spreadsheet.

exception pygsheets.WorksheetNotFound[source]

Trying to open non-existent or inaccessible worksheet.

exception pygsheets.NoValidUrlKeyFound[source]

No valid key found in URL.

exception pygsheets.IncorrectCellLabel[source]

The cell label is incorrect.

exception pygsheets.RequestError[source]

Error while sending API request.

exception pygsheets.InvalidUser[source]

Invalid user/domain

exception pygsheets.InvalidArgumentValue[source]

Invalid value for argument