Tasks
Google Sheet - Log to sheet
This task logs data to Google Sheets.
A specific sheet is selected with a spreadsheet id and sheet title. Values can be logged to the sheet in a variety of ways, refer to the examples provided toward the end of this page.
There are two options for Stubber to gain access to a google sheet:
- Share your sheet with the following stubber account:
share-with@stubber-sheets.iam.gserviceaccount.com - Create a credential in Stubber Manage
The rest of this document assumes familiarity with adding a task to a stub. See tasks documentation if you need a refresher.
It is recommended to use the update_sheet_rows task in advanced use cases, as this task is more flexible and can be used to update as well as insert rows in a sheet. The update_sheet_rows has upsert_row set to true by default.
See the Using Google Sheets Guide for more information on how to use Google Sheets with Stubber.
Basic usage
Log a single object with three elements inside it.
- The keys
first_name,last_nameandemail_addresswill be the headings - The values
Julian,Jonesandjulian@gmail.comwill be the inserted values. - The sheet title will be
employee details(if the sheet doesn't already exist it will be created)
Result
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
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(read, write)
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 (or want to create) an employees table with name, position, address columns, and you want the headings to start on row 4 and column C, the range starts with C4. The end position of the range is less strict, any value greater than the end position of your desired table is acceptible. We recommend setting this high to have some room for growth in the rows and columns of the table. For example, if your table will end at F10 for now, a range of ZZ9999 leaves a lot of room for growth.
Default: A1:Z9999
insert_headings optional boolean
Whether or not to insert the table headings. If this is set to true, it will insert the headings every time the task is run. So a task with this set to true should only run once per sheet.
If you pass in an Object (see examples at end of this document) then headings will be inserted automatically.
Default: false
headings optional array
The headings of the table in the sheet. The headings are only inserted when a new sheet is created automatically, or when the parameter insert_headings is true. If the insert_headings parameter is true, the headings will be inserted each time the task is run, so make sure to either create the headings manually, or update the parameters from the first to the following runs of the task.
If you pass in an Object (see examples at end of this document) the keys of the key/value pairs will be converted into headings automatically.
Default: null
insert_values optional array
The values to be inserted into the sheet. These values will always be appended to the bottom of an existing table. It can be specified in different ways:
Recommended usage:
- An array of objects. The objects keys will be automatically converted into the headings for the spreadsheet and the values be mapped correctly as the row data. For example if you had to pass in:
The headings for the sheet would be first_name, last_name and email_address and the values would map accordingly.
Alternative usage:
- A 1 dimensional array, such as
[1,2,3], will add a single row with 3 columns with the values 1, 2 and 3 for each column respectively. - A 2 dimensional array, such as
[[1,2,3],[4,5,6],[7,8,9]]will add a row for each nested array in the top level array. So the example will add 3 rows,1,2,3,4,5,6and7,8,9.
Default: null
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
message
The response after successfully logging to the google sheet
success
Either true or false based on the outcome
rows_inserted
The amount of rows inserted into the sheet
headings_count
The amount of headings inserted into the sheet
headings
The headings of the spreadsheet
Examples
- It is highly recommended that you make use of sending Objects of Arrays as this has the most robust checks and balances in place to ensure data integrity.
- The Array of Objects method also accounts for dynamic data expansion.
- It can also handled printing out nested JSON objects, however these will print to a single cell.
Object array : Single object with multiple key value pairs
Result
Notice how the keys are extracted and used as the headings
Object array: Two objects in an array, one containing an extra field
Result
Notice the extra heading field middle_name. The task recognises this new field and inserts it for the correct entry and leaves the entry which does not contain that heading empty.
Object array: Nested object
This example contains a nested object within the physical_address pair.
Result
The nested object is converted into a string and inserted in its entirety.
Object array: Interpolated from field
This example retrieves the object from a JSON editor field named ~~stubpost.data.json_field. Objects that are passed in are inserted into the call.
The JSON editor field (json_field) is passed in this input:
Result
The ~~stubpost.data.json_field object is passed into the call and inserted into the sheet accordingly.
Legacy usage:
Whilst these call types are still possible, it is highly recommended that you should rather pass in arrays of objects when making your calls.
Log a single row
Log a single row in an existing sheet with an existing table.
The task definition:
Log multiple rows using Stubber credential
Log multiple rows to an existing sheet with existing data.
The task definition: