API Reference

An Index of all functionality also exists.

Client class

class datasheets.Client(service=False, storage=True, user_agent='Python datasheets library')
__init__(service=False, storage=True, user_agent='Python datasheets library')

Create an authenticated client for interacting with Google Drive and Google Sheets

Parameters:
  • service (bool) –

    Whether to authenticate as a user or as a service account. If service=False, you will be prompted to authorize this instance to access the Google Drive attached to one of your Gmail accounts.

    Service-based authorization proceeds using the JSON file located at $DATASHEETS_SERVICE_PATH (default: ~/.datasheets/service_key.json).

    User-based authorization proceeds using the client secrets stored at $DATASHEETS_SECRETS_PATH (default: ~/.datasheets/client_secrets.json). Successful authentication by this method creates a set of credentials. By default these credentials are stored at $DATASHEETS_CREDENTIALS_PATH (default: ~/.datasheets/client_credentials.json), though storage of these credentials can be disabled with storage=False.

  • storage (bool) –

    Whether to use authorized credentials stored at $DATASHEETS_CREDENTIALS_PATH (or the default of ~/.datasheets/client_credentials.json). If credentials have not been stored yet or are invalid, whether to store newly obtained credentials at this location.

    If False, authorization will be requested every time a new Client instance is created. This mode deliberately does not store credentials to disk in order to allow for use of the library in multi-user environments.

  • user_agent (str) – The user agent tied to new credentials, if new credentials are required. This is primarily metadata, and thus unless you have a reason to change this the default value is probably fine.
create_workbook(filename, folders=())

Create a blank workbook with the specific filename

Parameters:
  • filename (str) – The name to give to the new workbook
  • folders (tuple) – An optional tuple of folders to add the new workbook to
Returns:

An instance of the newly created workbook

Return type:

datasheets.Workbook

delete_workbook(filename=None, file_id=None)

Delete a workbook from Google Drive

Either filename (i.e. title) or file_id should be provided. Providing file_id is preferred as it is more precise.

Parameters:
  • filename (str) – The name of the workbook to delete
  • file_id (str) – The ID of the workbook to delete
Returns:

None

fetch_folders(only_mine=False)

Fetch all folders shared with this account

Parameters:only_mine (bool) – If True, limit results to only those folders owned by this user
Returns:One row per folder listing folder name, ID, most recent modified time, and webview link to the folder
Return type:pandas.DataFrame
fetch_workbook(filename=None, file_id=None)

Fetch a workbook

Either filename (i.e. title) or file_id should be provided. Providing file_id is preferred as it is more precise.

Parameters:
  • filename (str) – The name of the workbook to fetch
  • file_id (str) – The ID of the workbook to fetch
Returns:

An instance of the requested workbook

Return type:

datasheets.Workbook

fetch_workbooks_info(folder=None)

Fetch information on all workbooks shared with this account

Parameters:folder (str) – An optional folder name to limit the results to
Returns:One row per workbook listing workbook name, ID, most recent modified time, and webview link to the workbook
Return type:pandas.DataFrame

Workbook class

class datasheets.Workbook(filename, file_id, client, drive_svc, sheets_svc)
__init__(filename, file_id, client, drive_svc, sheets_svc)

Create a datasheets.Workbook instance of an existing Google Sheets doc

This class in not intended to be directly instantiated; it is created by datasheets.Client.fetch_workbook().

Parameters:
  • filename (str) – The name of the workbook
  • file_id (str) – The Google Sheets-assigned ID for the file
  • client (datasheets.Client) – The client instance that instantiated this workbook
  • drive_svc (googleapiclient.discovery.Resource) – An instance of Google Drive
  • sheets_svc (googleapiclient.discovery.Resource) – An instance of Google Sheets
batch_update(body)

Apply updates to a workbook or tab using Google Sheets’ spreadsheets.batchUpdate method

Parameters:body (list) – A list of requests, with each request provided as a dict

The Google Sheets batch update method is a flexible method exposed by the Google Sheets API that permits effectively all functionality allowed for by the Google Sheets.

For more details on how to use this method, see the following:

Explanation of spreadsheets.batchUpdate method:

List of available request types and the parameters they take:

Example

The following is an example request that would perform only one update operation (the ‘repeatCell’ operation):

request_body = {
    'repeatCell': {
        'range': {
            'sheetId': self.tab_id,
            'startRowIndex': 0,
            'endRowIndex': self.nrows
        },
        'cell': {
            'userEnteredFormat': {
                'horizontalAlignment': horizontal,
                'verticalAlignment': vertical,
            }
        },
        'fields': 'userEnteredFormat(horizontalAlignment,verticalAlignment)'
    }
}
body = {'requests': [request_body]}
client

Property for the client instance that instantiated this workbook

create_tab(tabname, nrows=1000, ncols=26)

Create a new tab in the given workbook

Parameters:
  • tabname (str) – The name for the tab
  • nrows (int) – An integer number of rows for the tab to have. The Google Sheets default is 1000
  • ncols (int) – An integer number of columns for the tab to have. The Google Sheets default is 26
Returns:

An instance of the newly created tab

Return type:

datasheets.Tab

delete_tab(tabname)

Delete a tab with the given name from the current workbook

Parameters:tabname (str) – The name of the tab to delete
Returns:None
fetch_permissions()

Fetch information on who is shared on this workbook and their permission level

Returns:One row per email address shared, including the permission level that that email has been granted
Return type:pandas.DataFrame
fetch_tab(tabname)

Return a datasheets.Tab instance of the given tab associated with this workbook

Parameters:tabname (str) – The name of the tab to fetch
Returns:An instance of the requested tab
Return type:datasheets.Tab
fetch_tab_names()

Show the names of the tabs within the workbook, returned as a pandas.DataFrame.

Returns:One row per tabname within the workbook
Return type:pandas.DataFrame
share(email, role='reader', notify=True, message=None)

Share this workbook with someone.

Parameters:
  • email (str) – The email address to share the workbook with
  • role (str) – The type of permission to grant. Values must be one of ‘owner’, ‘writer’, or ‘reader’
  • notify (bool) – If True, send an email notifying the recipient of their granted permission. These notification emails are the same as what Google sends when a document is shared through Google Drive
  • message (str) – If notify is True, the message to send with the email notification
Returns:

None

unshare(email)

Unshare this workbook with someone.

Parameters:email (str) – The email address that will be unshared
Returns:None

Tab class

class datasheets.Tab(tabname, workbook, drive_svc, sheets_svc)
__init__(tabname, workbook, drive_svc, sheets_svc)

Create a datasheets.Tab instance of an existing Google Sheets tab.

This class in not intended to be directly instantiated; it is created by datasheets.Workbook.fetch_tab().

Parameters:
  • tabname (str) – The name of the tab
  • workbook (datasheets.Workbook) – The workbook instance that instantiated this tab
  • drive_svc (googleapiclient.discovery.Resource) – An instance of Google Drive
  • sheets_svc (googleapiclient.discovery.Resource) – An instance of Google Sheets
add_columns(n)

Add n columns to the given tab

Parameters:n (int) – The number of columns to add
Returns:None
add_rows(n)

Add n rows to the given tab

Parameters:n (int) – The number of rows to add
Returns:None
align_cells(horizontal='LEFT', vertical='MIDDLE')

Align all cells in the tab

Parameters:
  • horizontal (str) – The horizontal alignment for cells. May be one of ‘LEFT’, ‘CENTER’, or ‘RIGHT’
  • vertical (str) – The vertical alignment for cells. May be one of ‘TOP’, ‘MIDDLE’, ‘BOTTOM’
Returns:

None

alter_dimensions(nrows=None, ncols=None)

Alter the dimensions of the current tab.

If either dimension is left to None, that dimension will not be altered. Note that it is possible to set nrows or ncols to smaller than the current tab dimensions, in which case that data will be eliminated.

Parameters:
  • nrows (int) – The number of rows for the tab to have
  • ncols (int) – The number of columns for the tab to have
Returns:

None

append_data(data, index=True, autoformat=True)

Append data to the existing data in this tab.

If the new data exceeds the tab’s current dimensions the tab will be resized to accommodate it. Data headers will not be included among the appended data as they are assumed to already be among the existing tab data.

If the dimensions of data are larger than the tab’s current dimensions, the tab will automatically be resized to fit it.

Parameters:
  • data (pandas.DataFrame or dict or list) – The data to be uploaded, formatted as a pandas.DataFrame, a dict of lists, or a list of lists
  • index (bool) – If data is a pandas.DataFrame, whether to upload the index as well
Returns:

None

autoformat(n_header_rows)

Apply default stylings to the tab

This will apply the following stylings to the tab:

  • Header rows will be formatted to a dark gray background and off-white text
  • Font for all cells will be set to size 10 Proxima Nova
  • Cells will be horizontally left-aligned and vertically middle-aligned
  • Columns will be resized to display their largest entry
  • Empty columns and rows will be trimmed from the tab
Parameters:n_header_rows (int) – The number of header rows (i.e. row of labels / metadata)
Returns:None
autosize_columns()

Resize the widths of all columns in the tab to fit their data

Returns:None
clear_data()

Clear all data from the tab while leaving formatting intact

Returns:None
fetch_data(headers=True, fmt='df')

Retrieve the data within this tab.

Efforts are taken to ensure that returned rows are always the same length. If headers=True, this length will be equal to the length of the headers. If headers=False, this length will be equal to the longest row.

In either case, shorter rows will be padded with Nones and longer rows will be truncated (i.e. if there are 3 headers then all rows will have 3 entries regardless of the amount of populated cells they have).

Parameters:
  • headers (bool) – If True, the first row will be used as the column names for the pandas.DataFrame. Otherwise, a 0-indexed range will be used instead
  • fmt (str) – The format in which to return the data. Accepted values: ‘df’, ‘dict’, ‘list’
Returns:

When fmt=’df’ –> pandas.DataFrame

When fmt=’dict’ –> list of dicts, e.g.:

[{header1: row1cell1, header2: row1cell2},
 {header1: row2cell1, header2: row2cell2},
 ...]

When fmt=’list’ –> tuple of header names, list of lists with row data, e.g.:

([header1, header2, ...],
 [[row1cell1, row1cell2, ...], [row2cell1, row2cell2, ...], ...])

format_font(font='Proxima Nova', size=10)

Set the font and size for all cells in the tab

Parameters:
  • font (str) – The name of the font to use
  • size (int) – The size to set the font to
Returns:

None

format_headers(nrows)

Format the first n rows of a tab.

The following stylings will be applied to these rows:

  • Background will be set to dark gray with off-white text
  • Font will be set to size 10 Proxima Nova
  • Text will be horizontally left-aligned and vertically middle-aligned
  • Rows will be made “frozen” so that when the user scrolls these rows stay visible
Parameters:nrows (int) – The number of rows of headers in the tab
Returns:None
insert_data(data, index=True, autoformat=True)

Overwrite all data in this tab with the provided data.

All existing data in the tab will be removed, even if it might not have been overwritten (for example, if there is 4x2 data already in the tab and only 2x2 data is being inserted).

If the dimensions of data are larger than the tab’s current dimensions, the tab will automatically be resized to fit it.

Parameters:
  • data (pandas.DataFrame or dict or list) – The data to be uploaded, formatted as a pandas.DataFrame, a dict of lists, or a list of lists
  • index (bool) – If data is a pandas.DataFrame, whether to upload the index as well
Returns:

None

ncols

Property for the number (int) of columns in the tab

nrows

Property for the number (int) of rows in the tab

tab_id

Property that gives the ID for the tab

workbook

Property for the workbook instance that this tab belongs to

Helpers

datasheets.create_tab_in_existing_workbook(filename, tabname, file_id=None)

Create a new tab in an existing workbook and return an instance of that tab

Either filename (i.e. title) or file_id should be provided. Providing file_id is preferred as it is more precise.

Parameters:
  • filename (str) – The name of the existing workbook in which the tab will be created
  • tabname (str) – The name of the tab to be created
  • file_id (str) – The unique file_id for the workbook. If provided, the file_id will be used instead of the filename
Returns:

An instance of the newly created tab

Return type:

datasheets.Tab

datasheets.create_tab_in_new_workbook(filename, tabname, emails=(), role='reader', notify=True, message=None)

Create a new tab in a new workbook and return an instance of that tab

Parameters:
  • filename (str) – The name of the workbook to be created
  • tabname (str) – The name of the tab to be created
  • emails (str or tuple) – The email address(es) to grant the permission to. This may be one address in string form or a series of addresses in tuple form
  • role (str or tuple) – The type of permission(s) to grant. This can be either a tuple of the same size as emails or a single value, in which case all emails are granted that permission level. Values must be one of ‘owner’, ‘writer’, or ‘reader’
  • notify (bool) – If True, send an email notifying the recipient(s) of their granted permissions. These notification emails are the same as what Google sends when a document is shared through Google Drive
  • message (str) – If notify is True, the message to send with the email notification
Returns:

An instance of the newly created tab

Return type:

datasheets.Tab

datasheets.helpers.convert_cell_index_to_label(row, col)

Convert two cell indexes to a string address

Parameters:
  • row (int) – The cell row number, starting from 1
  • col (int) – The cell column number, starting from 1

Note that Google Sheets starts both the row and col indexes at 1.

Example

>>> sheets.convert_cell_index_to_label(1, 1)
A1
>>> sheets.convert_cell_index_to_label(10, 40)
BH10
Returns:The cell reference as an address (e.g. ‘B6’)
Return type:str
datasheets.helpers.convert_cell_label_to_index(label)

Convert a cell label in string form into one based cell indexes of the form (row, col).

Parameters:label (str) – The cell label in string form

Note that Google Sheets starts both the row and col indexes at 1.

Example

>>> sheets.convert_cell_label_to_index('A1')
(1, 1)
>>> sheets.convert_cell_label_to_index('BH10')
(10, 40)
Returns:The cell reference in (row_int, col_int) form
Return type:tuple

Exceptions

exception datasheets.exceptions.DatasheetsException

Base Exception for all other datasheets exceptions

This is intended to make catching exceptions from this library easier.

exception datasheets.exceptions.FolderNotFound

Attempting to open non-existent or inaccessible folder

exception datasheets.exceptions.MultipleWorkbooksFound

Multiple workbooks found for the given filename

exception datasheets.exceptions.PermissionNotFound

Trying to retrieve non-existent permission for workbook

exception datasheets.exceptions.TabNotFound

Trying to open non-existent tab

exception datasheets.exceptions.WorkbookNotFound

Trying to open non-existent or inaccessible workbook