1. Tasks
  2. Read From Sheet

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

Details

orgcredentialuuid

required
string

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

Default: null


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


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


Result

loading...

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

loading...
Result
loading...

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:

loading...
Result
loading...

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:

loading...

The variable is called using Inline text substitution inside quotes.

Result
loading...