Google Sheets¶

Overview¶

The GoogleSheets class allows you to interact with Google service account spreadsheets, called “Google Sheets”. You can create, modify, read, format, share and delete sheets with this connector.

In order to instantiate the class, you must pass Google service account credentials as a dictionary, or store the credentials as a JSON file locally and pass the path to the file as a string in the GOOGLE_DRIVE_CREDENTIALS environment variable.

You can follow these steps:

Go to the Google Developer Console and make sure the “Google Drive API” and the “Google Sheets API” are both enabled.
Go to the credentials page via the lefthand sidebar. On the credentials page, click “create credentials”.
Choose the “Service Account” option and fill out the form provided. This should generate your credentials.
Select your newly created Service Account on the credentials main page.
select “keys”, then “add key”, then “create new key”. Pick the key type JSON. The credentials should start to automatically download.

You can now copy and paste the data from the key into your script or (recommended) save it locally as a JSON file.

Quickstart¶

To instantiate the GoogleSheets class, you can either pass the constructor a dict containing your Google service account credentials or define the environment variable GOOGLE_DRIVE_CREDENTIALS to contain a path to the JSON file containing the dict.

Use API credentials via environmental variables¶

from parsons import GoogleSheets
sheets = GoogleSheets()

Pass API credentials as argument¶

credential_filename = 'google_drive_service_credentials.json'
credentials = json.load(open(credential_filename))
sheets = GoogleSheets(google_keyfile_dict=credentials)

Create/modify/retrieve documents using instance methods¶

sheet_id = sheets.create_spreadsheet('Voter Cell Phones')
sheets.append_to_sheet(sheet_id, people_with_cell_phones)
parsons_table = sheets.get_worksheet(sheet_id)

You may also want to share the document with your service or user account.

API¶

class parsons.google.google_sheets.GoogleSheets(google_keyfile_dict=None, subject=None)[source]¶

A connector for Google Sheets, handling data import and export.

Parameters:

google_keyfile_dict – dict A dictionary of Google Drive API credentials, parsed from JSON provided by the Google Developer Console. Required if env variable GOOGLE_DRIVE_CREDENTIALS is not populated.
subject – string In order to use account impersonation, pass in the email address of the account to be impersonated as a string.

list_worksheets(spreadsheet_id)[source]¶

Return a list of worksheets in the spreadsheet.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)

Returns:

list: A List of worksheets order by their index

get_worksheet_index(spreadsheet_id, title)[source]¶

Get the first sheet in a Google spreadsheet with the given title. The title is case sensitive and the index begins with 0.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
title – str The sheet title

Returns:

str: The sheet index

get_worksheet(spreadsheet_id, worksheet=0, skip_header_rows: int = 0)[source]¶

Create a parsons table from a sheet in a Google spreadsheet, given the sheet index.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
worksheet – str or int The index or the title of the worksheet. The index begins with 0.
skip_header_rows (int)

Returns:

Table: See Table for output options.

share_spreadsheet(spreadsheet_id, sharee, share_type='user', role='reader', notify=True, notify_message=None, with_link=False)[source]¶

Share a spreadsheet with a user, group of users, domain and/or the public.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
sharee – str User or group e-mail address, domain name to share the spreadsheet with. To share publicly, set sharee value to None.
share_type – str The sharee type. Allowed values are: user, group, domain, anyone.
role – str The primary role for this user. Allowed values are: owner, writer, reader.
notify – boolean Whether to send an email to the target user/domain.
email_message – str The email to be sent if notify kwarg set to True.
with_link – boolean Whether a link is required for this permission.

get_spreadsheet_permissions(spreadsheet_id)[source]¶

List the permissioned users and groups for a spreadsheet.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)

Returns:

Table: See Table for output options.

create_spreadsheet(title, editor_email=None, folder_id=None)[source]¶

Creates a new Google spreadsheet. Optionally shares the new doc with the given email address. Optionally creates the sheet in a specified folder.

Parameters:

title – str The human-readable title of the new spreadsheet
editor_email – str (optional) Email address which should be given permissions on this spreadsheet. Tip: You may want to share this file with the service account.
folder_id – str (optional) ID of the Google folder where the spreadsheet should be created. Tip: Get this from the folder URL. Anyone shared on the folder will have access to the spreadsheet.

Returns:

str: The spreadsheet ID

delete_spreadsheet(spreadsheet_id)[source]¶

Deletes a Google spreadsheet.

Parameters:: spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)

add_sheet(spreadsheet_id, title=None, rows=100, cols=25)[source]¶

Adds a sheet to a Google spreadsheet.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
rows – int Number of rows
cols – Number of cols

Returns:

str: The sheet index

append_to_sheet(spreadsheet_id, table, worksheet=0, user_entered_value=False, **kwargs)[source]¶

Append data from a Parsons table to a Google sheet. Note that the table’s columns are ignored, as we’ll be keeping whatever header row already exists in the Google sheet.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table – obj Table
worksheet – str or int The index or the title of the worksheet. The index begins with 0.
user_entered_value – bool (optional) If True, will submit cell values as entered (required for entering formulas). Otherwise, values will be entered as strings or numbers only.

paste_data_in_sheet(spreadsheet_id, table, worksheet=0, header=True, startrow=0, startcol=0)[source]¶

Pastes data from a Parsons table to a Google sheet. Note that this may overwrite presently existing data. This function is useful for adding data to a subsection if an existing sheet that will have other existing data - contrast to overwrite_sheet (which will fully replace any existing data) and append_to_sheet (which sticks the data only after all other existing data).

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL).
table – obj Table
worksheet – str or int The index or the title of the worksheet. The index begins with 0.
header – bool Whether or not the header row gets pasted with the data.
startrow – int Starting row position of pasted data. Counts from 0.
startcol – int Starting column position of pasted data. Counts from 0.

overwrite_sheet(spreadsheet_id, table, worksheet=0, user_entered_value=False, **kwargs)[source]¶

Replace the data in a Google sheet with a Parsons table, using the table’s columns as the first row.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
table – obj Table
worksheet – str or int The index or the title of the worksheet. The index begins with 0.
user_entered_value – bool (optional) If True, will submit cell values as entered (required for entering formulas). Otherwise, values will be entered as strings or numbers only.

format_cells(spreadsheet_id, range, cell_format, worksheet=0)[source]¶

Format the cells of a worksheet.

Parameters:

spreadsheet_id – str The ID of the spreadsheet (Tip: Get this from the spreadsheet URL)
range – str The cell range to format. E.g. "A2" or "A2:B100"
cell_format – dict The formatting to apply to the range. Full options are specified in the GoogleSheets API documentation.
worksheet – str or int The index or the title of the worksheet. The index begins with 0.

Examples

# Set 'A4' cell's text format to bold
gs.format_cells(sheet_id, "A4", {"textFormat": {"bold": True}}, worksheet=0)

# Color the background of 'A2:B2' cell range yellow,
# change horizontal alignment, text color and font size
gs.format_cells.format(
    sheet_id,
    "A2:B2",
    {
        "backgroundColor": {
            "red": 0.0,
            "green": 0.0,
            "blue": 0.0
        },
        "horizontalAlignment": "CENTER",
        "textFormat": {
            "foregroundColor": {
                "red": 1.0,
                "green": 1.0,
                "blue": 0.0
            },
            "fontSize": 12,
            "bold": True
        }
    },
    worksheet=0
)