Google Sheets

Class which allows for Google Sheets to be used as paramater set database.

Note

If you have data in the first row, you must have entries in some other row.

Authentication

TODO

Config

The Google Sheets class can be instantiated using a Config class. There are several options in the config which are redundant (e.g. worksheet-id and worksheet-title). They are marked with flags in the table below. These values should be placed inside a JSON object named google or google-sheets. If the keys are placed inside the google key, the values will be shared with other Google APIs (e.g. Google Drive ).

Fields Description
spreedsheet-id (str) The Google spreedsheet ID being used. Found in the URL.
*creds-path (str) Path to the Google Sheets credentials JSON file.
*creds (dict) The Google credentials provided from the developer console.
#worksheet-id (int) The Google Sheets worksheet id. Sheets are indexed at 0.
#worksheet-title (str) The Google Sheets worksheet title.

* fields should not be used together. If you use them together, creds will be used over creds-path. # fields should also not be used together and worksheet-id will be used.

The default configuration looks like this:

Sample JSON configuration for Sheets
{
    "google-sheets" : {
        "spreedsheet-id" : "",
        "creds-path" : "",
        "creds" : {},
        "worksheet-id" : "",
        "worksheet-title" : ""
    }
}
class dapt.db.sheets.Sheet(*args, **kwargs)

Bases: dapt.db.base.Database

An interface for accessing and setting paramater set data. You must either provide a Config object or client_id and client_secret.

Keyword Arguments:
 
  • config (Config) – A Config object which contains the client_id and client_secret.
  • spreedsheet_id (str) – the Google Sheets ID creds (str): the path to the file containing the Google API credentials. Default is credentials.json.
  • sheet_id (int) – the the sheet id to use. 0 is used if no value is givin for sheet_title, sheet_id or in the Config
  • sheet_title (str) – the title of the sheet to use
connect()

The method used to connect to the database and log the user in. Some databases won’t need to use the connect method, but it should be called regardless to prevent problems.

Returns:gspread client if the database connected successfully and False otherwise.
connected()

Check to see if the API is connected to the server and working.

Returns:True if the API is connected to the server and False otherwise.
fields()

Get the fields(attributes) of the parameter set

Returns:Array of strings with each element being a field (order is preserved if possible)
get_key_index(column_key)

Get the column index given the key.

Parameters:column_key (str) – the key to find the index of
Returns:The index or -1 if it could not be determined.
get_row_index(column_key, row_value)

Get the row index given the column to look through and row value to match to.

Parameters:
  • column_key (str) – the key to find the index of
  • row_value (str) – the value of the cell to fine
Returns:

The index or -1 if it could not be determined.
get_table()

Get the table from the database.

Returns:An array with each element being a dictionary of the key-value pairs for the row in the database.
update_cell(row_id, field, value)

Update the cell specified by the row_id and field.

Parameters:
  • row_id (int) – the row id to replace
  • field (str) – the field of the value to replace
  • value (object) – the value to insert into the cell
Returns:

A boolean that is True if successfully inserted and False otherwise.
update_row(row_index, values)

Get the row of the paramater set.

Parameters:
  • row_index (int) – the index of the row to replace (starting from 1). Indices less than 1 will return False. Indices greater than the table length will be appended.
  • values (Dict) – the key-value pairs that should be inserted. If the dictionary contains more values then number of columns, the table will be extended.
Returns:

A boolean that is Trues if successfully inserted and False otherwise.
worksheet(*args, **kwargs)

Get a Google Sheet object. The worksheet id or title are obtained from the Config file or initialization.

Returns:A Google Sheet worksheet