Tasks

Read From Sheet

This task reads data from Google Sheets. A specific sheet is selected using a spreadsheet ID, and a query is applied to return only the desired rows.

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

Data fetched from Google Sheets can be cached. Caching improves performance if the task runs frequently.

This document assumes familiarity with adding a task to a stub. See the tasks documentation.

# Basic usage

# Parameters

# spreadsheet_id

required

string

The unique ID of the Google Sheet. It appears in the URL, for example: https://docs.google.com/spreadsheets/d/**1ZaYk1_2-o9aefRrd4jdEFgYO0ED5hOuPlhNkE3qMitY**/edit#gid=608545401 The bold section is the spreadsheet_id.

Default: null

# service_account

optional

string

The email address of the service account used to access the Google Sheet. This is the recommended access method (instead of using orgcredentialuuid).

Available Stubber service accounts:

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

Default: null

# sheet_title

optional

string

The title of the sheet inside the Google Sheets document.

Default: Sheet1

# columns

optional

string

The columns to include in the returned data. Provide a comma-separated list, e.g. name, role. Use _all to return every available column.

Default: _all

# range

optional

string

The range of the data table, specified as {{column}}{{row}}:{{column}}{{row}}.

Select the range that includes your table headings. If your table begins at row 4, column C, the range starts at C4. The ending boundary is flexible—choose a value large enough to allow future growth (e.g., Z9999).

Default: A1:Z9999

# query

optional

string

A LokiJS formatted query string used to filter the returned data. See operator examples here: https://github.com/techfort/LokiJS/wiki/Query-Examples#find-operator-examples-

Examples:

• { "price": "39.99" } — Be mindful of data types; strings are recommended.
• { "age": {"$lt":"30"}, "location": "south africa", "tags": { "$contains": "manager" }}
• { "name": { "$regex": /din/ }}
• { "month": "{{stub.data._current_month}}" }

Default: {} (returns all rows)

# use_cache

optional

boolean

Whether to cache the sheet for subsequent tasks. Caching improves performance, but updates made to the sheet will only be reflected once the cache expires.

Default: true

# cache_expire_seconds

optional

string

How long (in seconds) the sheet should remain cached. If set to 60, any read within the next 60 seconds will use the cached version rather than re-fetching.

Only relevant when use_cache is true.

Default: 60

# Details

# orgcredentialuuid

optional

string

The UUID of the org credential Stubber should use to access the spreadsheet. Using a service account is recommended over this method.

Default: null

# Result

# Properties

# data

array

The rows returned from the Google Sheet based on your parameters. Each object represents a row. Returned keys match the columns defined in columns.

# Examples

# Find items by price

Find all products in the sheet with a price of 39.99.

# Result

# Complex query

Generate a quote, dynamically include customer details and line items, and use jsonata to calculate totals.

Task definition:

# Result

# Using Stub Data in the Query with a Stubber Credential

Use a value from stub.data to filter sheet rows.

In this example, we return all employees whose birthday month matches stub.data._current_month.

The variable is inserted using Inline text substitution.

# Result