Tasks

Google Sheet - Log to Sheet

This task logs data to Google Sheets.

A specific sheet is selected using a spreadsheet ID and sheet title. Values can be logged to the sheet in various ways — see 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 the Tasks Documentation if you need a refresher.

See the Using Google Sheets Guide for more information on integrating 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 used as the column headings.
2. The values Julian, Jones, and julian@gmail.com will be inserted as row values.
3. The sheet title will be employee details. If the sheet doesn’t already exist, it will be created automatically.

# Result

# Parameters

# spreadsheet_id

required

string

The unique ID of the Google Sheet. It can be found in the sheet’s URL:

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 for accessing the Google Sheet. This is the recommended method of access control (instead of using the orgcredentialuuid parameter).

Available Stubber service accounts:

• share-with@stubber-sheets.iam.gserviceaccount.com (read, write)

Default: null

# sheet_title

optional

string

The title of the sheet within the Google Sheets document where data will be logged. If a sheet with this title doesn’t exist, it will be created automatically.

Default: Sheet1

# range

optional

string

Specifies where data can be written in the sheet.

For example, if you specify a range of C2:G7, data will start at cell C2 and won’t go beyond G7 (see example below).

It’s recommended to set the end range to a high value (e.g., A1:Z9999) to allow the sheet to grow.

If your table has name, position, and address columns starting at row 4, column C, use a starting range like C4. You can set the end of the range generously (e.g., ZZ9999) to accommodate future expansion.

Default: A1:Z9999

# insert_headings

optional

boolean

Determines whether to insert the table headings. If set to true, headings are inserted every time the task runs. This should typically only be done once per sheet.

If you pass an object (see examples below), headings will be inserted automatically.

Default: false

# insert_values

optional

array

The values to insert into the sheet. Values are appended to the bottom of the table.

Recommended usage:

• Array of objects – The object keys become headings, and values map automatically to the corresponding columns.

        [
  { first_name: "john", last_name: "smith", email_address: "john@gmail.com" },
  { first_name: "jane", last_name: "doe", email_address: "jane@gmail.com" }
]

      

Alternative usage:

• A 1D array (e.g., [1, 2, 3]) adds one row with three columns.
• A 2D array (e.g., [[1,2,3],[4,5,6]]) adds one row for each nested array.

Default: null

# orgcredentialuuid

optional

string

The unique identifier for the org credential Stubber should use to access the spreadsheet. Using a service account is recommended instead.

Default: null

# headings

optional

array

This parameter was used in earlier insert methods and is no longer recommended.

Headings are inserted automatically when a new sheet is created, or if insert_headings is true. If enabled, they’ll be added every run, so only use this once per sheet.

Default: null

# Result

# Properties

• message – The response message after logging to the sheet.
• success – Indicates whether the task succeeded (true or false).
• rows_inserted – The number of rows inserted.
• heading_count – The number of column headings.
• headings – The column headings in the sheet.

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

        [
  {
    first_name: "Matthew",
    last_name: "Kriel",
    email_address: "mckriel@gmail.com",
    mobile_number: "+27796780829",
    physical_address: "30 Plantation Road, Devils Peak Estate",
  },
  {
    first_name: "juLian",
    email_address: "jkriel@gmail.com",
    last_name: "kriel",
    mobile_number: "+27796780899",
    physical_address: "40 Plantation Road, Devils Peak Estate",
  },
  {
    last_name: "whitfield",
    email_address: "krwhitfield@gmail.com",
    mobile_number: "+2779678880",
    physical_address: "666 Plantation Road, Devils Peak Estate",
    first_name: "kyle",
  },
];

      

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