Connectors / Service / Google Sheets

Create a new spreadsheet and edit with others at the same time - from your computer, phone or tablet (updated: 1580985638569)

Google Sheets

Send and retrieve data from Google Sheets.

Overview

The Google Sheets connector allows you to send & retrieve data from any of the Google Sheets available within your account.

Google Sheets templates

Please note that we have the following Google Sheets templates available:

These will give you pre-configured best practice ways of working with Google Sheets and integrating it with other connectors.

However, please continue to at least read the Authentication setup instructions on this page to enable you to get started with using Google Sheets.

Authentication

When using the Google Sheets connector, will need to create a new authentication:

google-sheets

This will result in a Tray.io authentication pop-up window. The first page will ask you to name your authentication appropriately, and state which type of authentication you wish to make ('Personal' or 'Organisational').

As you can see, the next page asks you for your 'Service Permissions'. Set as appropriate for your project scopes.

google-sheets-popup

After which (if you are not already logged into your Google Sheets account) there will be an Oauth login, where you'll be directed to a new modal. The modal will ask you for your gmail account details.

Once you have clicked the 'Add authentication' button, go to back to your authentication field (within the workflow dashboard properties panel from earlier), and select the recently added authentication from the dropdown options now available.

This should validate and wohey! you will now be authenticated.

Available Operations

The examples below show one or two of the available connector operations in use.

Please see the Full Operations Reference at the end of this page for details on all available operations for this connector.

Note on Operations usage

Google Drive connector is NECESSARY to List Available Spreadsheets

IMPORTANT!: From now on the Google Drive connector is needed in order to be able to List Available Spreadsheets of a user. It is HIGHLY RECOMMENDED that the Google Driveconnector be placed near the top of any new Google Sheets connector based workflow.

Previously we included the ability to get a list of users spreadsheets from within the Google Sheets connector itself. Regardless of this, behind the scenes it has always been a separate task, actually stemming from the Google Drive connector itself.

Due to new security updates from Google, this feature now needs to be spilt in its entirety.

This update effectively breaks all and any previous workflows that were dependent upon this ability within the Google Sheets connector.

There are 2 ways to navigate this issue:

  1. You can connect a new Google Drive connector to an earlier point withing your current workflow, and set it up in such as way so as to list all the current available spreadsheets found in conjunction with your Google Sheets user.

  2. If you have individual sheets in mind, and you know the Google Sheet ID, you can utilise that ID by putting it directly into the relevant Google Sheet operation field, where needed. In order to find the sheet ID, refer to the Finding the Spreadsheet ID section below.

Finding the Spreadsheet ID

  1. Open the desired spreadhseet.

  2. Select the URL of the spreadhseet. It will be similar to: https://docs.google.com/spreadsheets/d/1Ubfhc3e67Ivv6Wj5pJVPuQ6l7_3n_Bck397CC9-Z8Mw/edit#gid=0.

  3. The Spreadhseet ID is the hash betwen the d/ and the /edit sections.

  4. In this example the Spreadhseet ID is 1Ubfhc3e67Ivv6Wj5pJVPuQ6l7_3n_Bck397CC9-Z8Mw.

Downloading Files

As the Google Drive connector holds most of the permissions available for downloading Google files (regardless of type) - it is recommended to try revising your workflow to include this connector, should you face any Google file downloading issues.

Setting up your Google Sheet to work with Tray.io

To use your Google Sheet with Tray.io, you'll need to set up your Sheet to work in a specific way:

  • The first row must have column headers, for each of the columns you'd like to use with Tray.io

  • There can be no completely blank rows in your spreadsheet (you'll need to delete any blank rows, as Google creates sheets with default 1,000 rows). Our operations are becoming more robust in handling this, however if something isn't acting as expected please check this first.

  • The row values in your sheet should be in rows 2, 3, 4...etc - this is automatically handled if you use the sheets connector to create this for you from scratch.

  • While it may not seem like a mandatory field, having a 'Column heading' is always necessary. Note that there is a 'Create column headers for sheet' operation that may make things simpler for you, should you wish to utilise it.

  • In order to update your column headers, you may wish to utilise the 'Update row' operation. In order to do so you will need to set the property 'Row number' to 1. Please be aware that any data you already have in this row, will be over written as a result!

  • If you have forgotten to add a header to you sheet, but it still contains data, you will need to add the row you are overwriting to the end of the sheet using the 'Create Row' operation first; so you can then utilise the 'Update Row' operation in order to create said headers.

  • When row values are added to a Google Sheet they are mapped based on the letter associated with the Column Heading selected. The technical assumption is that these column headings and associated column letters are set and will not change. If you change the order of the columns on the Google Sheet interface (thus changing the letter associated with the heading) the letter configured in the background of the Tray.io workflow will not update automatically. You will need to refresh the configuration in the Tray.io workflow manually. To do so, you will need to re-select the column heading from the column picker dropdown.

'Get Rows' Operation

PLEASE NOTE: This is one of the most complex operations in the Google Sheets connector.

The 'Get rows' operation generally speaking, allows a user to retrieve any number of rows from a google spreadsheet. This operation will automatically gather all the row data available (including blanks) unless you apply 'Filter's to your request.

There are many variations and combinations possible when using the following filters available:

google-sheets-get-rows
  • Column Heading

  • Operator

  • Value

  • Sort Direction

When using the 'Filter's available (as described in more detail below), your end result should be similar to this:

google-sheets-get-rows-complete

COLUMN HEADING

Users are able to filter through columns, via selecting the relevant column heading. It is important that all columns have headers in order for this operation to work. See above notes on column headers within "Setting up your Google Sheet to work with Tray.io" section for more details.

Do not use the letter of the column you wish to change - make sure to use its header instead. Tray.io uses the header to work out its letter for you.

OPERATOR

This is a set of matcher functions that will match the cell value, to the value that you have supplied, depending on the comparison type you select.

Think of it as a more advanced boolean operation, where there are a few more options to choose from.

While most are self explanatory, please take case when selecting the 'Empty cells' Operator.

When filling in the 'Value' field (having selected the 'Empty cells' as your operator), please make sure to use the values true or false.

google-sheets-empty-cells

VALUE (Operator based)

IMPORTANT!: Please be aware that all values returned from Fthe Google Sheets connector are basedin a string format.

The following 'Operator's deal with the following data types:

Number based:

These 'Operator's convert the value given from the Tray.io 'Value' input field, and the actual specified Google Sheets cell value into numbers, before they then compare the two. Please be aware that they do not deal with floats only integers!.

  • Greater than

  • Less than

  • Greater than or equal to

  • Less than or equal to

String based:

These 'Operator's compare strings only.

These values look at comparing strings to each other.

  • Text contains

  • Text doesn't contain

Number & String based:

As mentioned previously, all values are originally returned in a string data format, from Google Sheets connector. Therefore to reduce complexity, these operators do not take into account the data type be they string or number. This means that an integer 2 would be the same as string '2'.

  • Equal to

  • Not equal to

Boolean based:

  • Empty cells

SORT DIRECTION

This filter is only used when the 'Value' input field parses to a number.

You can use this operation to sort the returned output (from an earlier run) either as ascending or descending.

Create row VS Append Values

Create row allows users to create a row in spreadsheet. It checks that there are headers in the sheets and will automatically add a row under the next available row, without users having to know the cell ranges.

This operation is more useful when adding data one piece at a time

Append values allows users to update a load of data into a sheet in one go. Note that it doesn't check if its overwriting anything.

Think of it like a quick 'bulk insert' that runs no checks.

Using the Raw HTTP Request ('Universal Operation')

As of version 6.1, it is now possible to effectively create your own operations.

This is a very powerful feature which you can put to use when there is an endpoint in Google Sheets which is not used by any of our operations.

To use this you will first of all need to research the endpoint in the Google Sheets API documentation v4.0 , to find the exact format that Google Sheets will be expecting the endpoint to be passed in.

Note that you will only need to add the suffix to the endpoint, as the base URL will be automatically set (the base URL is picked up from the value you entered when you created your authentication).

The base URL for Google Sheets connector is: https://sheets.googleapis.com/v4

For example, say that the 'List Worksheets' operation did not exist in our Google Sheets connector, and you wanted to use this endpoint, you would use the Google Sheets API v4.0 docs to find the 'get worksheets' endpoint. Note that it is a GET request:

google-sheets-raw-http-get

So if you know what your 'Spreadsheet ID' is, you can get the worksheets data details with the following settings:

Method: GET

Endpoint: /spreadsheets/[spreadsheet_id]

Final outcome being: https://sheets.googleapis.com/v4/spreadsheets/[spreadsheet_id]

google-sheets-raw-http

This operation will now return your worksheets details as requested.

Example usage

TRAY POTENTIAL: Tray.io is extremely flexible. By design there is no fixed way of working with it - you can pull whatever data you need from other services and work with it using our core and helper connectors. This demo which follows shows only one possible way of working with Tray.io and the Google Sheets connector. Once you've finished working through this example please see our Introduction to working with data and jsonpaths page and Data Guide for more details.

Below is an example of a way in which you could potentially use the Google Sheets connector, to create a Google Sheet with columns, data and more.

The steps will be as follows:

  1. Setup a manual trigger and use the Google Sheets to create a blank spreadsheet.

  2. Add a worksheet to the newly created spreadsheet.

  3. Automatically input column headers.

  4. Add data to various cells within the worksheet.

The final outcome should look like this:

google-sheets-wf-complete

1 - Setup trigger & Create Spreadsheet

Once you have clicked 'Create new workflow' on your main Tray.io dashboard (and named said new workflow), select the Manual trigger from the trigger options available:

google-sheets-manual-trigger

Once you have been redirected to the Tray.io workflow dashboard, from the connectors panel on the left, add a Google Sheets connector to your second step. Set the operation to 'Create spreadsheet'.

Make sure to give your spreadsheet a 'Title' and fill in the extra fields as you see fit.

google-sheets-step-1

When this operation is successful, it will return all the available data on our chosen spreadsheet, including its ID.

Feel free to re-name your steps as you go along to make things clearer for yourself and other users.

2 - Create a Worksheet

Add another Google Sheets connector to your workflow and set the operation to 'Create worksheet'.

You will need your 'Spreadsheet ID' in order to use this operation, so for sake of ease generate a jsonpath by utilising the connector-snake. Drag your link from the 'Spreadsheet ID' field over your first step ('Create spreadsheet'), and wait for the data options to appear. Select spreadsheetId and your jsonpath with magically appear!

JSONPATHS: For more information on what jsonpaths are and how to use jsonpaths with Tray.io, please see our Intro page and Data Guide for more details.

CONNECTOR-SNAKE: The simplest and easiest way to generate your jsonpaths is to use our feature called the Connector-snake. Please see the main page for more details.

Make sure to also give your worksheet a 'Title' like you did your spreadsheet earlier.

google-sheets-step-2

3 - Create column headers

Add a final Google Sheets connector to your workflow and set the operation to 'Create column headers for sheet'.

Generate the 'Spreadsheet ID' and 'Worksheet name' in a similar fashion using the connector-snake from earlier. Note that your worksheet name will come from your previous Google Sheets connector step ('Create worksheet').

You will also need to fill in the 'Row data' -> 'Column Header' fields, in order to have "something to create with".

google-sheets-step-3

Now when this workflow is run, you will have created a named spreadsheet, with a named worksheet, along with you desired column headers. Make sure to check your Google Sheets account for proof of your new wondrous invention!

google-sheets-step-3-1

BEST PRACTICES: Whenever you do decide to create your own workflow, please make sure you take a look at our managing data best practices guide.

All Operations

Latest version:

8.1

Add values to spreadsheet

Appends values to a spreadsheet.

Create column headers for sheet

Add column headers to your worksheet.

Create empty column or row

Add a column or row to the end of a Google Sheet.

Create row

Add a row to a Google Sheet.

Create spreadsheet

Create a new spreadsheet for your Google Sheets.

Create worksheet

Add a worksheet to a spreadsheet.

Delete column

Remove a column from a worksheet.

Delete row

Remove row from a worksheet.

Delete worksheet

Delete a worksheet from a spreadsheet.

Get row by index

Find a row by its index.

Get rows

Get data from a worksheet of a spreadsheet.

Get spreadsheet details

Get details about a spreadsheet, including information about its worksheets.

Get total active rows

Returns total active rows in a sheet.

Get worksheet data

Get data from a worksheet of a spreadsheet.

Get worksheet data using range

Get data from a worksheet of a spreadsheet using a cell range.

Insert empty column

Insert a column into a Google Sheet.

Insert empty row

Insert a row into a Google Sheet.

List worksheet column headers

List columns of a worksheet.

List worksheet column headers DDL

List worksheet column headers using ID DDL

List worksheets

List worksheets within a spreadsheet.

Raw HTTP request (advanced)

Perform a raw HTTP request with some pre-configuration and processing by the connector, such as authentication.

Update cell value

Set the value of an individual cell in a Google Sheet.

Update row

Update a row, by its number, within a worksheet.

Update values in spreadsheet

Update the values of a spreadsheet in a given range.