3. The sheetsync package API

3.1. Sheet

class sheetsync.Sheet(credentials=None, document_key=None, document_name=None, worksheet_name=None, key_column_headers=None, header_row_ix=1, formula_ref_row_ix=None, flag_deletes=True, protected_fields=None, template_key=None, template_name=None, folder_key=None, folder_name=None)

Represents a single worksheet within a google spreadsheet.

This class tracks the google connection, the reference to the worksheet, as well as options controlling the structure of the data in the worksheet.. for .. rubric:: example

  • Which row is used as the table header
  • What header names should be used for the key column(s)
  • Whether some columns are protected from overwriting
document_key

str – The spreadsheet’s document key assigned by google drive. If you are using sheetsync to create a spreadsheet then use this attribute to saved the document_key, and make sure you pass it as a parameter in subsequent calls to __init__

document_name

str – The title of the google spreadsheet document

document_href

str – The HTML href for the google spreadsheet document

__init__(credentials=None, document_key=None, document_name=None, worksheet_name=None, key_column_headers=None, header_row_ix=1, formula_ref_row_ix=None, flag_deletes=True, protected_fields=None, template_key=None, template_name=None, folder_key=None, folder_name=None)

Creates a worksheet object (also creating a new Google sheet doc if required)

Parameters:
  • credentials (OAuth2Credentials) – Credentials object returned by the google authorization server. Described in detail in this article: https://developers.google.com/api-client-library/python/guide/aaa_oauth For testing and development consider using the ia_credentials_helper helper function
  • document_key (Optional) (str) – Document key for the existing spreadsheet to sync data to. More info here: https://productforums.google.com/forum/#!topic/docs/XPOR9bTTS50 If this is not provided sheetsync will use document_name to try and find the correct spreadsheet.
  • document_name (Optional) (str) – The name of the spreadsheet document to access. If this is not found it will be created. If you know the document_key then using that is faster and more reliable.
  • worksheet_name (str) – The name of the worksheet inside the spreadsheet that data will be synced to. If omitted then the default name “Sheet1” will be used, and a matching worksheet created if necessary.
  • key_column_headers (Optional) (list of str) –

    Data in the key column(s) uniquely identifies a row in your data. So, for example, if your data is indexed by a single username string, that you want to store in a column with the header ‘Username’, you would pass this:

    key_column_headers=[‘Username’]

    However, sheetsync also supports component keys. Python dictionaries can use tuples as keys, for example if you had a tuple key like this:

    (‘Tesla’, ‘Model-S’, ‘2013’)

    You can make the column meanings clear by passing in a list of three key_column_headers:

    [‘Make’, ‘Model’, ‘Year’]

    If no value is given, then the default behavior is to name the column “Key”; or “Key-1”, “Key-2”, ... if your data dictionaries keys are tuples.

  • header_row_ix (Optional) (int) – The row number we expect to see column headers in. Defaults to 1 (the very top row).
  • formula_ref_row_ix (Optional) (int) – If you want formulas to be added to some cells when inserting new rows then use a formula reference row. See Formulas for an example use.
  • flag_deletes (Optional) (bool) – Specify if deleted rows should only be flagged for deletion. By default sheetsync does not delete rows of data, it just marks that they are deleted by appending the string ” (DELETED)” to key values. If you pass in the value “False” then rows of data will be deleted by the sync method if they are not found in the input data. Note, use the inject method if you only want to add or modify data to in a worksheet.
  • protected_fields (Optional) (list of str) – An list of fields (column headers) that contain protected data. sheetsync will only write to cells in these columns if they are blank. This can be useful if you are expecting users of the spreadsheet to colaborate on the document and edit values in certain columns (e.g. modifying a “Test result” column from “PENDING” to “PASSED”) and don’t want to overwrite their edits.
  • template_key (Optional) (str) – This optional key references the spreadsheet that will be copied if a new spreadsheet needs to be created. This is useful for copying over formatting, a specific header order, or apps-script functions. See Templates for Formatting.
  • template_name (Optional) (str) – As with template_key but the name of the template spreadsheet. If known, using the template_key will be faster.
  • folder_key (Optional) (str) – This optional key references the folder that a new spreadsheet will be moved to if a new spreadsheet needs to be created.
  • folder_name (Optional) (str) – Like folder_key this parameter specifies the optional folder that a spreadsheet will be created in (if required). If a folder matching the name cannot be found, sheetsync will attempt to create it.
backup(backup_name, folder_key=None, folder_name=None)

Copies the google spreadsheet to the backup_name and folder specified.

Parameters:
  • backup_name (str) – The name of the backup document to create.
  • folder_key (Optional) (str) – The key of a folder that the new copy will be moved to.
  • folder_name (Optional) (str) – Like folder_key, references the folder to move a backup to. If the folder can’t be found, sheetsync will create it.
data(as_cells=False)

Reads the worksheet and returns an indexed dictionary of the row objects.

For example:

>>>print sheet.data()

{‘Miss Piggy’: {‘Color’: ‘Pink’, ‘Performer’: ‘Frank Oz’}, ‘Kermit’: {‘Color’: ‘Green’, ‘Performer’: ‘Jim Henson’}}

inject(raw_data, row_change_callback=None)

Use this function to add rows or update existing rows in the spreadsheet.

Parameters:
  • raw_data (dict) – A dictionary of dictionaries. Where the keys of the outer dictionary uniquely identify each row of data, and the inner dictionaries represent the field,value pairs for a row of data.
  • row_change_callback (Optional) (func) –

    A callback function that you can use to track changes to rows on the spreadsheet. The row_change_callback function must take four parameters like so:

    change_callback(row_key,
    row_dict_before, row_dict_after, list_of_changed_keys)
Returns:

A simple counter object providing statistics

about the changes made by sheetsync.
Return type:

UpdateResults (object)
sync(raw_data, row_change_callback=None)

Equivalent to the inject method but will delete rows from the google spreadsheet if their key is not found in the input (raw_data) dictionary.

Parameters:
  • raw_data (dict) – See inject method
  • row_change_callback (Optional) (func) – See inject method
Returns:

See inject method
Return type:

UpdateResults (object)

3.2. UpdateResults

class sheetsync.UpdateResults

A lightweight counter object that holds statistics about number of updates made after using the ‘sync’ or ‘inject’ method.

added

int – Number of rows added

changed

int – Number of rows changed

nochange

int – Number of rows that were not modified.

deleted

int – Number of rows deleted (which will always be 0 when using the ‘inject’ function)

3.3. ia_credentials_helper

sheetsync.ia_credentials_helper(client_id, client_secret, credentials_cache_file='credentials.json', cache_key='default')

Helper function to manage a credentials cache during testing.

This function attempts to load and refresh a credentials object from a json cache file, using the cache_key and client_id as a lookup.

If this isn’t found then it starts an OAuth2 authentication flow, using the client_id and client_secret and if successful, saves those to the local cache. See Injecting data to a Google sheet.

Parameters:
  • client_id (str) – Google Drive API client id string for an installed app
  • client_secret (str) – The corresponding client secret.
  • credentials_cache_file (str) – Filepath to the json credentials cache file
  • cache_key (str) – Optional string to allow multiple credentials for a client to be stored in the cache.
Returns:

A google api credentials object. As described here: https://developers.google.com/api-client-library/python/guide/aaa_oauth
Return type:

OAuth2Credentials