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 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
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
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
Using Stub Data in the Query
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.