Connectors / Service / Google Sheets

Google Sheets

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.

Authentication

When using the Google Sheets connector, the first thing you will need to do is go to your Tray.io account page, and select the workflow you wish to work on. Once in the workflow dashboard itself, search and drag the Google Sheets connector from the connectors panel (on the left hand side) onto your workflow.

With the new Google Sheets connector step highlighted, in the properties panel on the right, click on 'New Authentication' which is located under the 'Settings' heading.

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.

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 Drive connector 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, you need only look at the Google Sheet URL, and copy the hash found between the d/ and the /edit sections.

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.

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

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

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.

VALUE (Operator based)

IMPORTANT!: Please be aware that all values returned from Fthe Google Sheets connector are based in 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:

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]

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:

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:

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.

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.

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".

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!

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:

7.2