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:

1. Share your sheet with the following stubber account: share-with@stubber-sheets.iam.gserviceaccount.com
2. 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.

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.

1. The keys first_name, last_name and email_address will be the headings
2. The values Julian, Jones and julian@gmail.com will be the inserted values.
3. 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/

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

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

# 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

# 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,6 and 7,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

# headings

optional

array

The headings parameter was used with the previously suggested method of doing inserts and as a result we recommend not making use of this paramter.

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

# 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

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