Tasks

Google Sheet - Update Sheet Rows

This task updates rows on a Google Sheets spreadsheet.

A specific sheet is selected with a spreadsheet id and sheet title.

We recommend confirming that the task returns the rows you expect using the trial_run parameter before applying updates.

There are two options for Stubber to gain access to a google sheet:

1. Share your sheet with the following stubber account: share-with@stubber-sheets.iam.gserviceaccount.com
2. Create a credential in Stubber Manage

See the Using Google Sheets Guide for more information on how to use Google Sheets with Stubber.

# Basic usage

This usage will target all rows that column header is equal to name and whose row values are Bob. It will then set all those rows email values to bob@gmail.com.

# 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/

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 (make sure to provide editor access to the document)

Default: null

# sheet_title

optional

string

The title of the sheet on the Google Sheets document to log the data to. If a sheet is specified that does not yet exist, the sheet will be created automatically.

Default: Sheet1

# range

optional

string

This field sets the range of where data is able to be written to on a given sheet.

As an example if we specified a range value of C2:G7 then the task will insert data starting at C2 and not insert further than G7 (see example below).

We always recommend setting the end range to a high value like Z:9999 which will allow for your sheet to grow.

If you have an employees table with name, position, address columns, and the headings start on row 4 and column C, the range should start 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 for now, a range of ZZ9999 leaves a lot of room for growth.

Default: A1:Z9999

# filter_column

optional

string

This parameter is used to target the specific row header for a sheet, for example first_name, last_name, email_address, etc.

This parameter is used together with filter_value to target specific rows to be updated. If the filter_column matches the filter_value for a row, that row will be updated (see Basic Usage example above).

For more complex matching criteria, use complex_query.

Default: null

# filter_value

optional

string

This parameter is to target the specific value of a cell in a row. For example, a filter_value of "Bob" will target all rows where Bob is matched (for the rows whose header is matched with filter_column).

This parameter is used together with filter_column to target specific rows to be updated. If the filter_column matches the filter_value for a row, that row will be updated.

For more complex matching criteria, use complex_query.

Default: null

# upsert_row

optional

boolean

Whether or not the task should insert a new row if no rows are found that match the filter criteria.

If upsert_row is true and no rows are found that match the filter criteria, a new row will be inserted with the update_values and filter_valuew. Additionally any headings that did not exist previously will be added and have the row content populated.

If upsert_row is false and there are no matches to the filtering criteria, then no new row insertions will happen.

Below is a useful diagram to understand the outcomes of what happens if the filtering criteria is matched or not.

Default: true

# complex_query

optional

string

The LokiJS formatted query to use for filtering the sheet down to the rows that should be updated. 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": "USA", "tags": { "$contains": "manager" }}
• {"name": { '$regex' : /din/ }}

Default: null

# trial_run

optional

boolean

Whether or not the task should be ran as a trial run. If trial_run is true, no updates will be made, but the rows that would normally be updated will be returned in the result of the task.

Default: false

# update_values

required

object

The update_values contains the update that will be applied to each row that was matched by the filter criteria. It has to be a key:value object where each key specifies the column to update and each value the value for the column. Only columns that wants to updated should be included in the object. Columns that are not in the object will remain unchanged.

The next examples assumes an employee sheet with columns name, cell, position:

• update the position

• update the name and position:

• add a new column, email dependent on the name (not implemented yet):

Default: false

# 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

# update_response

object

The response received from Google for the update. The response contains relevant information about the update, like the id of the spreadsheet for a sanity check, as well as the total updated rows, columns, cells and sheets. For each row updated, there is an item in update_response.responses with similar information.

# Examples

# Example: Do a trial run

Log multiple rows to an existing sheet with existing data.

The task definition:

# Result

# Example: Update with a filter column using a credential

Log a single row in an existing sheet with an existing table.

The task definition:

# Result

# Example: Update rows with a complex query

For all the managers, add an email address dynamically.

NOTE: the functionality to use the values of some columns in other columns is not implemented yet, but will be soon 😃 eg: {{name}}@gmail.com, here the name column is used to update the email column.

The task definition: