Help Scout to Google Sheets Integration to Export Conversations and Other Data

You can export reporting data from Help Scout manually as a CSV or XLSX file. To get any other type of data, such as conversations, you’ll need to connect to Help Scout API and use it to export data. Read on to find out how you can do this easily without coding and even automate the transfer of data from Help Scout to Google Sheets.

How you can integrate Help Scout to Google Sheets

Help Scout does not provide any native integration to Google Sheets, so we’ll connect to its API without needing to write any code. For this, we’ll use Coupler.io, a solution for importing data from multiple sources to Google Sheets, Excel, or BigQuery. To set up an integration, you’ll need to complete the following steps:

• Create a Help Scout application to get credentials
• Get a Help Scout access code 
• Get a Help Scout access token and refresh token
• Sign up to Coupler.io and create an importer
• Set up the Help Scout to Google Sheets integration based on the data you need to export

The drawback of this method of connecting to the Help Scout API is that the access token expires after 2 days or 48 hours. This means you won’t be able to set up recurring data exports from Help Scout for a longer period. 

However, we have you covered with our ready-to-use Help Scout to Google Sheets template which contains a few preinstalled importers that will transfer all of your Help Scout conversations. 

To use it, you’ll need to complete two steps:

• Install the Coupler.io add-on for Google Sheets from the Google Workspace Marketplace 
• Create a Help Scout application to get credentials (you’ll find the how-to guide below)

After that, follow the instructions from Readme! tab and enjoy exporting your data.

For those who’d like to learn more about the mechanics of connecting to the Help Scout API without coding, please continue reading!

Get Help Scout credentials for authentication 

Create a Help Scout application

First, we need to acquire an access token to authenticate our connection between Help Scout and Google Sheets. For this, complete the following steps:

• Go to your Help Scout Profile => My Apps and click Create My App.

• Name your app as you wish and enter a redirect URL that you want. In our example, we used this one:

https://blog.couplerstaging.dev/

• Once you click Create, a page with your Credentials will appear. 

Get a Help Scout access code

• Enter your App ID in the following boilerplate:

https://secure.helpscout.net/authentication/authorizeClientApplication?client_id={application_id}

• Copy the resulting URL and paste it in your browser. Once you hit enter, you’ll be asked to authorize your app to access your account. Click Authorize. 

• After that your redirect URL will open with a code parameter.

Copy this code string – we’ll use it to obtain a pair of access and refresh tokens.

Get a Help Scout access token and a refresh token

To get a Help Scout access token, you’ll need to send a POST request to the Help Scout API. You don’t need a terminal or any script to do this – just use Coupler.io. It’s a solution for importing data from multiple sources into Google Sheets, Excel, or BigQuery, which is also mentioned as a pro in a recent Help Scout alternatives comparison. We’ll use it to get your access token and retrieve Help Scout data afterwards.

Therefore, sign up to Coupler.io with your Google account and click Add New Importer. 

Note: Alternatively, you can install Coupler.io add-on for Google Sheets from the Google Workspace Marketplace and use it right from your spreadsheet. Check out the available Google Sheets integrations.

Then you’ll need to set up three blocks: Source, Destination, and Schedule.

Source

• Select JSON as a source app
• In the JSON URL field, insert the following URL having replaced the variables with your values

https://api.helpscout.net/v2/oauth2/token?code={enter-your-access-code}&client_id={enter-your-app-id}&client_secret={enter-your-app-secret}&grant_type=authorization_code

• Select POST as the HTTP method

This time no more parameters are needed, so feel free to jump to the Destination settings.

Note: Check out other data source options available, for example, Google Drive to Google Sheets and many more.

Destination

• Select Google Sheets as the destination app
• Connect to your Google account
• Select a spreadsheet and a sheet where your access and refresh tokens need to be placed

Click Save and Run to get your tokens. There you go!

We have not used the third block, Schedule, this time, because obtaining access and refresh tokens is a one-time action. 

Important note: The Help Scout access and refresh tokens expire after 2 days or 48 hours. The refresh token is needed to retrieve a new access token. If you need to implement recurring data exports from Help Scout, check out the Help Scout to Google Sheets template.

---

Now that you have an access token, you can connect Help Scout to Google Sheets.

Connect Help Scout to Google Sheets

The essence of getting data from Help Scout lies in sending GET requests to the Help Scout Mailbox API. With Coupler.io, you can do this without the need for any coding. Just select JSON as a source app and use the following fields:

JSON URL 

The Help Scout Mailbox API has the following basic JSON URL:

https://api.helpscout.net/v2/{endpoint}

• {endpoint} – a postfix to the basic JSON URL that corresponds to a particular type of data to retrieve.

For example, to get a list of mailboxes for your Help Scout account, the JSON URL will look like this:

https://api.helpscout.net/v2/mailboxes

HTTP method

When we obtained the access token, we used the POST request. To get data from Help Scout, you’ll need to use the GET method.

HTTP headers

Each GET request must contain the Authorization header:

Authorization: Bearer {your-access-token}

URL query string

Optionally, you can use URL query parameters to filter the data being retrieved. For example, you can filter the list of conversations by mailbox and status using the following URL query parameters:

mailbox: {mailbox_id}
status: open

Path

The Path parameter allows you to select nested objects from the JSON response (e.g. path.to.object). This means that without using the Path parameter, your data will be placed in one row. For example, here is a list of mailboxes without Path:

Here is how it should look if you use the Path parameter:

_embedded.mailboxes

Now we’re done with the theory – time to get your hands dirty 🙂 Let’s export a conversation from Help Scout to demonstrate how it works.

How to export Help Scout conversations to Google Sheets

List of all active conversations

Here are the parameters for the Coupler.io JSON source to get a list of all conversations in your Help Scout account. 

JSON URL:

https://api.helpscout.net/v2/conversations

HTTP method:

GET

HTTP header:

Authorization: Bearer {enter-your-access-token}

Path: 

_embedded.conversations

This will import all active conversations from all mailboxes in your Help Scout account. Each request is paginated to 25 conversations, not rows. 

Note: To get the rest of the records, you’ll need to specify the page number using the page query parameter in the URL query string, for example, page: 2.

The exported data may have multiple rows per conversation like this: 

It’s not a mistake or bug, the rows are created per tag associated with each conversation. Scroll to the right to the tag column to see the difference.

Get a filtered list of conversations

Let’s filter our conversations data, for example, by a specific mailbox and the status “active“. Here is how the configuration in Coupler.io will look:

JSON URL:

https://api.helpscout.net/v2/conversations

HTTP method:

GET

HTTP header:

Authorization: Bearer {enter-your-access-token}

URL query string:

mailbox: {mailbox-ID}
status: active

Path: 

_embedded.conversations

Here is a table with all the filtering parameters for Help Scout conversations data.

Parameter	Description	Examples
embed: threads	Allows embedding/loading of sub-entities	embed: threads
mailbox	Filters conversations by a specific mailbox ID. Use comma separated values for more mailboxes.	mailbox: 34 mailbox: 34,78
folder	Filters conversations by a specific folder ID.	folder: 12
status	Filter conversations by status (defaults to active): – active – all – closed – open – pending – spam	status: active
tag	Filters conversation by tags. Use comma separated values for more tags.	tag: plan tag: plan,blue
assigned_to	Filters conversations by assignee ID.	assigned_to: 11
modifiedSince	Filters conversations modified after a particular timestamp.	modifiedSince: 2021-03-03T12:05:23Z
number	Looks up a particular conversation by conversation number.	number: 654
sortField	Sorts the result by a specified field: – createdAt – customerEmail – customerName – mailboxid – modifiedAt – number – score – statussubject – waitingSince	sortField: createdAt
sortOrder	Descending (default) or ascending sort order.	sortOrder: desc
query	Advanced search query.	query: (number: 654)
page	Page number	page: 3
customFieldsByIds	Filters conversations by custom fields, using custom field IDs. This filter must be accompanied by a mailbox parameter as well. Expected format is id:value,id:value	customFieldsByIds: 13:plan customFieldsByIds: 13:plan,24:0

How to export conversation threads from Help Scout

To export a list of threads from your Help Scout conversation, you’ll need to use the following parameters:

JSON URL:

https://api.helpscout.net/v2/conversation/{conversation-ID/threads

HTTP method:

GET

HTTP header:

Authorization: Bearer {enter-your-access-token}

Path: 

_embedded.threads

This will import the details of all threads associated with a particular conversation, including the body text of the messages. 

What else can you export from Help Scout to Google Sheets?

The Mailbox API allows you to get versatile data from Help Scout. With Coupler.io, you can easily fetch these records and import them into Google Sheets, Excel, or BigQuery. 

• Reports – you can get a number of reports that allow you to track and analyze your productivity and performance including the Happiness report, Chat report, Customers Helped report, etc.
• Customers, Users, Teams – you can get details about your customers, users, and teams from Help Scout.
• Workflows, Ratings – another set of data that is available for export without any coding.

Visit the Help Scout API documentation to learn more about JSON URL and query parameters available for other data entities.

Do you want to have a ready to use Help Scout integration?

At Coupler.io, we keep creating new integrations that satisfy the needs of our customers. If Help Scout is one of these and you’d like to have it ready-to-use like Pipedrive to Google Sheets, i.e. to configure with a few clicks and drop downs, let us know about it by filling out this form. Good luck with your data!