Tasks

Read From Sheet

This task reads data from Google Sheets. A specific sheet is selected with a spreadsheet id, and then a query is passed to only return desired rows from the sheet.

For access, the sheet can be shared with the Stubber account share-with@stubber-sheets.iam.gserviceaccount.com. Alternatively, a credential can be created in Stubber Manage and used in the task.

The data that is read from sheets can can be cached, this speeds up the task if it runs often.

The rest of this document assumes familiarity with adding a task to a stub. See tasks documentation

# Basic usage

# Parameters

# spreadsheet_id

required

string

This is the unique id of the Google Sheet on Google's system. It can be found in the url of a spreadsheet, as can be seen here "https://docs.google.com/spreadsheets/d/1ZaYk1_2-o9aefRrd4jdEFgYO0ED5hOuPlhNkE3qMitY/edit#gid=608545401". The string in bold, 1ZaYk1_2-o9aefRrd4jdEFgYO0ED5hOuPlhNkE3qMitY, is the spreadsheet_id.

Default: null

# service_account

optional

string

The email address of the service account to use to access the Google Sheet. This is the recommended method of access control for Google Sheets (as opposed to using the orgcredentialuuid parameter).

The available Stubber service accounts:

• share-with@stubber-sheets.iam.gserviceaccount.com (read, write)

Default: null

# sheet_title

optional

string

The title of the sheet on the Google Sheets document that contains the data.

Default: Sheet1

# columns

optional

string

The columns that should be included in the returned data. This should be specified as comma separated values in a single string, eg. name, role. The value, _all, is a special value, it will include all the available columns in the response.

Default: _all

# range

optional

string

The range of the data table on the sheet specified as starting column and row as {{column}}:{{row}}. The row and column of the headings should be used. For example, if you have an employees table with name, position, address columns, and the headings start on row 4 and column C, the range starts with C4. The end position of the range is less strict, any value greater than the end position of your table is acceptable. We recommend setting this high to have some room for growth in the rows and columns of the table. For example, if your table ends at F10, a range of Z9999 leaves a lot of room for growth.

Default: A1:Z9999

# query

optional

string

The LokiJS formatted query to use for filtering the sheet down to the desired data. This has to be formatted as a string. For a list of available operators see LokiJS operator examples

Examples:

• { "price": "39.99"} be weary of data types in the sheet and in your query, we recommend using strings if possible.
• { "age": {"$lt":"30"}, "location": "south africa", "tags": { "$contains": "manager" }}
• { "name": { '$regex' : /din/ }}
• { "month": "{{stub.data._current_month}}" }

Default: {} which will return everything.

# use_cache

optional

boolean

Whether to cache the sheet in followup tasks. Caching the sheet increases the speed of the task by a second or two, however, information added to the sheet in the cache_expire_seconds will not be available until the cache expires and the sheet is pulled again.

Default: true

# cache_expire_seconds

optional

string

The duration, in seconds, to cache the sheet for. This is only relevant when the use_cache parameter is set to true. If the cache duration is 60 seconds, then any additional read from the same sheet in the next sixty seconds will use the cache instead of pulling the sheet from Google. Setting this to a high value like 1 hour, or 3600 seconds, should only be done if the sheet does not update very often. Any update could take a maximum of cache_expire_seconds before it reflects in task results, depending on the remaining cache duration when the update to the actual sheet happened.

Default: 60

# Details

# orgcredentialuuid

optional

string

This is a unique identifier for the org credential that Stubber should use to access the specified spreadsheet. It is recommended to use the service account instead of this method.

Default: null

# Result

# Properties

# data

array

The rows from the Google Sheet that were found for the given parameters. Each item in the array corresponds to a row in the sheet. The returned keys correspond to the columns that were specified with the columns parameter.

# Examples

# Find items by price

Find all products in the sheet with a price of 39.99

# Result

# Complex query

Generate a quote, dynamically add in customer details and the line items of the quote via the data parameter, use jsonata to calculate totals.

The task definition:

# Result

# Using Stub Data in the Query with a Stubber credential

Using a value from a stub.data variable to return rows from a google sheet.

In this example, we are looking to return the names of the employees who's birthday is in current month. The current month is store at data path stub.data._current_month.

The task definition:

The variable is called using Inline text substitution inside quotes.

# Result