Connectors / Service / Google Sheets
Google SheetsSend and retrieve data from Google Sheets.
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:
Check for new data in Google Sheets
Export SuiteQL response to Google Sheets
Export Salesforce query to new Google Sheet
Export Salesforce query to existing Google Sheet
Upload list of employees to Bamboo
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.
When using the Google Sheets connector, will need to create a new authentication:
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.
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:
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.
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
Open the desired spreadhseet.
Select the URL of the spreadhseet. It will be similar to:
The Spreadhseet ID is the hash betwen the
In this example the Spreadhseet ID is
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:
When using the 'Filter's available (as described in more detail below), your end result should be similar to this:
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.
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
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:
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 or equal to
Less than or equal to
These 'Operator's compare strings only.
These values look at comparing strings to each other.
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'.
Not equal to
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.
Get worksheet data
Please be aware that the number of Google sheet rows you can pull in using the 'Get worksheet data' operation is limited to 40,000.
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:
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
So if you know what your 'Spreadsheet ID' is, you can get the worksheets data details with the following settings:
Final outcome being: https://sheets.googleapis.com/v4/spreadsheets/[spreadsheet_id]
This operation will now return your worksheets details as requested.
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:
Setup a manual trigger and use the Google Sheets to create a blank spreadsheet.
Add a worksheet to the newly created spreadsheet.
Automatically input column headers.
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!
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.