Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)
Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)

Google Sheets
8.1

Send and retrieve data from Google Sheets.

Overview
Copy

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

API Information
Copy

The Google Sheets connector has the following Base URL: https://sheets.googleapis.com/v4. More information can be found on their main API documentation (v4) site. Details regarding their API limitations can be found here.

Google Sheets templates
Copy

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

Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)
Check for new data in Google Sheets
Workflow
Universal
Intermediate

Runs on a constant schedule to check for new content in a google sheet

Details
Artisan IMG > NetSuite REST (netsuite) (917247fd-0fa6-4f4c-b085-3ccbdc273c4d)
Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)
Export SuiteQL response to Google Sheets
Workflow
Finance Ops
Advanced

Executes a SuiteQL query in Netsuite, then sends the response to a Google Sheet

Details
Artisan IMG > Salesforce (salesforce) (dd966f42-81e8-4770-a3d8-d095ca41ab45)
Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)
Export Salesforce query to new Google Sheet
Workflow
Sales
Beginner

Executes a query for specific records, then compiles them into a new Google Sheet

Details
Artisan IMG > Salesforce (salesforce) (dd966f42-81e8-4770-a3d8-d095ca41ab45)
Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)
Export Salesforce query to existing Google Sheet
Workflow
Sales
Intermediate

Executes a query for specific records, then compiles them into an existing Google Sheet

Details
Artisan IMG > Google Sheets (sheets) (a894e6ff-8a08-4375-ac35-ab9b5d2bc849)
Artisan IMG > BambooHR (bamboohr) (134605bd-dd4e-41e9-9178-da8556ddbeb3)
Upload list of employees to Bamboo
Workflow
HR
Intermediate

Adds new employees from a Google Sheet to BambooHR

Details

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
Copy

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.

Available Operations
Copy

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
Copy

Sharing permissions
Copy

If you wish to share or give people access to your spreadsheet please note that permissions come under the remit of the Google Drive connector. 

Simply add a Google Drive connector and select the Create permission operation to specify the kind of access you wish to grant your user/s.

List Spreadsheets & Google Drive
Copy

IMPORTANT!: You now need to use the Google Drive connector in conjunction with the Google Sheets connector in order to list all available spreadsheets from within a user's account.

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

Due to security updates from Google we have had to separate the ability to List Spreadsheets from the Google Sheets connector itself.

This operation now resides in the Google Drive connector in its entirety.

If you have a workflow which uses an old version of Google Sheets (one where the List spreadsheets operation is still available) we recommended revising your workflow in one of the following ways to avoid any potential downloading or security issues:

  1. Retrieve multiple sheets: Add a Google Drive connector to an earlier point within your current workflow.

    • Select List spreadsheets from the operations list.

    • Set it up in conjunction with your Google Sheets user.

  2. Retrieve a single sheet: Select via sheet ID

    • Put the Google Sheet ID into the relevant operation field.

Finding the Spreadsheet ID
Copy

  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
Copy

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
Copy

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
Copy

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 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
Copy

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
Copy

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')
Copy

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
Copy

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
Copy

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
Copy

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 pages on Basic data concepts and Mapping data between steps

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
Copy

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, be sure to check out some of our key articles such as: