Tasks

Update Sheet Rows

This task update rows on a Google Sheets spreadsheet. A specific sheet is selected with a spreadsheet id and sheet title. A filter value is used to filter the rows in the spreadsheet on a specific filter column. Update values are passed, and each row where the filter column matches the filter value is updated to the update values. If more complex matching criteria are required, you can use a complex query to match rows to update. 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

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 to log the data to. If a sheet is specified that does not yet exist, the sheet will be created automatically.

Default: Sheet1

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

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

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

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

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

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

Properties

update_response

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:

Result

Update with a filter column

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

The task definition:

Result

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:

Result

        Not implemented