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. An org credential is required for Stubber to gain access to your spreadsheet.

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

Requirements

• Org credential: An org credential for Google Sheets can be created in Stubber Manage

Details

orgcredentialuuid

This is a unique identifier for the org credential that Stubber should use to access the specified spreadsheet.

Default: null

Parameters

spreadsheet_id

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

sheet_title

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

Default: Sheet1

columns

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

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 acceptible. 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

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 datatypes 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/ }}

Default: {} which will return everything.

use_cache

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

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

Result

Properties

data

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