Google sheets triggers

Get spreadsheet row

The New Spreadsheet Row trigger reads the data in each row of a given spreadsheet and passes it to the next stage of your integration flow.

Get Spreadsheet Row

Input fields description

Input field Required Description Example

Spreadsheet

true

Spreadsheet which is used for data reading

MyTestSpreadsheet

Worksheet

true

Worksheet of spreadsheet which is used for data reading

Sheet1

Dimension

true

The major dimension of the values

ROWS, COLUMNS

Use first row/column as header

true

You should specify Yes if your data has a header in the first row/column and you want to use these values as json key in the output message. If you specify No, json key is taken from row/column index according to A1 notation. Review the example below.

Yes

Select All Data

true

You should specify Yes if you want to fetch all spreadsheet data whenever step starts. If you specify No, a step is emitting only delta changes (lines which were added after last step runs)

No

Using Use first row/column as header feature, you must be sure that header values are unique.

Values from spreadsheets return as UNFORMATTED_VALUE type

The process

Initially, the system reads all the rows from a given Google Spreadsheet and processes it further along with your designed integration flow. It will also create an initial state of your spreadsheet, called a snapshot, in order to have something to compare with after your data is updated.

After the initial read, any further requests for an update is compared to this snapshot and in case any changes are detected they are passed along with the integration flow as well.

If Select All Data configuration property has value Yes, the system will read all the rows from a given spreadsheet whenever flow processes the message.

Cases with rows dimension:

Table

After a trigger execution, data is extracted from the table above and the following message is emitted:

1) dimension: rows, use first row/column as header: yes

  {
    "FirstName": "Tom1",
    "LastName": "Smith1"
  }
  {
    "FirstName": "Tom2",
    "LastName": "Smith2"
  }
  -----------------------
  {
    "FirstName": "Tom10",
    "LastName": "Smith10"
  }

2) dimension: rows, use first row/column as header: no

  {
    "A": "FirstName",
    "B": "LastName"
  }
  {
    "A": "Tom1",
    "B": "Smith1"
  }
  {
    "A": "Tom2",
    "B": "Smith2"
  }
 ---------------------
  {
    "A": "Tom10",
    "B": "Smith10"
  }

Cases with columns dimension:

Table

After a trigger execution, data is extracted from the table above and the following message is emitted:

1) dimension: columns, use first row/column as header: yes

  {
    "FirstName": "Tom1",
    "LastName": "Smith1"
  }
  {
    "FirstName": "Tom2",
    "LastName": "Smith2"
  }
  -----------------------
  {
    "FirstName": "Tom10",
    "LastName": "Smith10"
  }

2) dimension: columns, use first row/column as header: no

  {
    "1": "FirstName",
    "2": "LastName"
  }
  {
    "1": "Tom1",
    "2": "Smith1"
  }
  {
    "1": "Tom2",
    "2": "Smith2"
  }
 ---------------------
  {
    "1": "Tom10",
    "2": "Smith10"
  }

Limitations

Triggers can emit maximum 1000 messages per one execution.

Trigger uses version 4 of the Google Sheet API. Read the Google Sheets API Documentation for more information.

Rows(deprecated)

This trigger is deprecated, Use the Get spreadsheet row trigger.

The Rows trigger reads the data in each row of a given spreadsheet and passes it to the next stage of your integration flow.

The process

Initially, the system will read all the rows from a given spreadsheet and process it further along with your designed integration flow. It will also create an initial state of your spreadsheet, called a snapshot, in order to have something to compare after your data is updated.

After the initial read, any further requests for an update is compared to this snapshot and if any changes are detected they is passed along the integration flow as well. It is, therefore, imperative to provide a readable spreadsheet for integration.

Readable spreadsheets

The nature of API that the platform uses to read the rows in Google Spreadsheets dictates the use of certain rules that it is useful to follow for a smooth integration of your data. In addition to the minimum requirements your spreadsheet must have:

No empty spaces

This means no empty columns, no empty rows, and no spaces or newlines after each record. The reading process stops if it encounters empty columns or rows since there is no way to know if there are new or more records afterward.

Only STRING values

Your data is processed as a STRING data type.

No formula

If you calculate the value of any field using a formula the system will only read the VALUE of that field and convert it into a STRING data type.

One sheet only

Your Google Spreadsheet must contain only one sheet. Even if you insert a second one, it is ignored by the system.

External and internal id for each row

When any given row is processed by the system it receives a unique name or a unique ID so that it can be recognized by the system afterward if you want to make any changes to the values in that specific row.

The row number in the Google Spreadsheet is taken as a unique ID to process through the integration flow.