1. Tasks
  2. Update Sheet Rows

Tasks

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.

An org credential is required for Stubber to gain access to your spreadsheet.

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

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

The filter_column is the column used for match checking with the filter_value. 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


filter_value

optional
string

The filter_value is the value used for match checking with the filter_column. 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


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
        {
  "position": "manager"
}

      
  • update the name and position:
        {
  "name": "Jonty",
  "position": "developer"
}

      
  • add a new column, email dependent on the name (not implemented yet):
        {
  "email": "{{name}}@mycompany.com"
}

      

Default: false


Result

loading...

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

Do a trial run

Log multiple rows to an existing sheet with existing data.

The task definition:

loading...
Result
loading...

Update with a filter column

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

The task definition:

loading...
Result
loading...

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:

loading...
Result
editor
        Not implemented