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
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
optional boolean
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
- update the
name
andposition
:
- add a new column,
email
dependent on the name (not implemented yet):
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: