Export SuiteQL response to Google Sheets

Workflow

Finance Ops

Advanced

Use Template

This is a 'Workflow' template which means that it is a single standalone workflow.

Some workflow templates can be modified to work with other workflow templates - e.g. to convert a data sync between two services from uni-directional to bi-directional

Overview

Copy

This workflow allows you to input a Netsuite suiteQL query (see here and here) and use the results to build a report in Google Sheets or to perform actions on each record in other systems with that data.

The business value of this workflow is in the Netsuite connector configuration.

The REST API can call a Netsuite SuiteQL search directly.

This is far more powerful than using the List Records operation in our connector.

With a suiteql query, it is possible to use joins, concatenations, and the full extent of suiteql

More importantly, it is possible to tell the query which fields are to be returned in the response list.

The Find Records operation in the tray only returns the IDs - so you have to GET each record to actually use it.

So this workflow will allow you to get all the results you need in a single call.

Connectors used

Copy

The following connectors are used in this template. This is mostly provided for reference only - there is no need to read through the linked connector pages. All you need to know for this template is explained here, although you will likely need to look up service authentication instructions:

End Result

Copy

The end result of this workflow is that records (e.g. customers) will be pulled from Netsuite, addded to a Google Sheet and then emailed to a chosen email address when complete, including a link to the Google Sheet:

Prerequisites

Copy

This workflow assumes the following:

• You have an authentication to Netsuite and Google Sheets, or your downstream system of choice.

• You have a suiteQL query that you wish to use

• You have a Google Sheet created and column headers aligned to your desired data ready

Getting Live

Copy

The complete workflow is:

It uses a manual trigger. You could change this to a if desired.

There are 3 basic steps in the workflow:

Step 1 takes your SuiteQL query and calls Netsuite

Step 2 loops through the resulting items and adds rows to a Google sheet.

Step 3 sends an email to you to alert you that it has completed, with a link to the sheet

To configure the workflow for your own use:

Important notes

Copy

Using fallback values

Copy

In a scenario where, for example, you are pulling customer data and some customers may have empty values for e.g. 'Phone', if you inspect the Tray logs you will see that Netsuite does not return anything at all for phone:

In this case you will need to use the Tray 'Fallback Values' feature in order to prevent the Google Sheets connector returning an error:

In this case we are using an empty string:

This will result in the Sheet being populated thus:

Setting output schema for the loop connector

Copy

In order to make the correct jsonpaths available from the Loop connector, you may find that you need to do a sample run after making some temporary changes to the workflow:

1. Move the Google Sheets step to after the loop

2. Put a Terminate step in before Google Sheets

3. Run the workflow

4. Click 'debug' above your workflow

5. Click on an iteration of the loop connector in the debug panel

6. In the loop output box click 'Use Output'

This will result in the loop output schema being set correctly:

You will then be able to copy the jsonpath for each piece of data - e.g. $.steps.loop-1.value.companyname

Once done, reset your workflow to its original state!