Search API Connector Documentation

Print

Import Freshdesk Data to Google Sheets

In this article we’ll be connecting to the Freshdesk API from Google Sheets, using the API Connector add-on for Sheets. Freshdesk is a cloud-based customer support platform with a rich collection of API endpoints that allow you to easily collect data from just about anywhere within your customer support pipeline. So let’s jump in and see what we can fetch!

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CONNECT TO FRESHDESK API

You will need to create an account and login to Freshdesk to gain access to your API key. Once logged in, navigate to the main dashboard, click on your profile pic in the top right corner and click Profile Settings.

Your API key will be on the right side, ready to go!

freshdesk-img2

PART 2: CREATE YOUR FRESHDESK API REQUEST URL

You should navigate over to their great documentation now to figure out what data you want to fetch from Freshdesk. In this tutorial, we will first pull up all the support tickets on our domain. To do that, we will use the following URL to access all pending tickets:

https://YOUR_DOMAIN.freshdesk.com/api/v2/tickets

Just enter the domain you registered with where it says YOUR_DOMAIN.

Great! You’ve got a URL, so now let’s integrate Google Sheets and API Connector to get started with the data fetching process.

PART 3: PULL FRESHDESK API DATA INTO SHEETS

We can now enter this URL into API Connector to get our data.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    freshdesk-img3
  3. Next, you need to encode your API key. To encode your credentials, type your API key into this form, using the format API key:x (yes a kissy face). Your encoded credentials will appear underneath.

    The encoding script runs in your browser, and none of your credentials are seen or stored by this site.
     

     

     

  4. Copy the value from above and head over to API Connector. Use the following format to insert a key value pair: Authorization: Basic {Encoded API key} so that your instance of API Connector looks like this now:
    freshdesk-img5
  5. You’re good to go! Click Run and check out all your tickets.freshdesk-img6

PART 4: MORE EXAMPLE API URLS

Let’s check out a few more from the documentation that may look interesting. To get all contacts, or all clients who have raised tickets in your domain, use the following URL:

https://YOUR_DOMAIN.freshdesk.com/api/v2/contacts

To get a list of all your full and part-time support agents, use this one:

https://YOUR_DOMAIN.freshdesk.com/api/v2/agents

To check out all your knowledge base groups, use this one:

https://YOUR_DOMAIN.freshdesk.com/api/v2/solutions/categories

And another super useful one! Get the result of feedback surveys and clients’ satisfaction ratings:

https://YOUR_DOMAIN.freshdesk.com/api/v2/surveys/satisfaction_ratings

PART 5: HANDLE FILTERING

  1. Freshdesk allows you to filter your responses by adding query strings to the end of your URL. For example, their documentation for filtering tickets shows that you can add filter=[filter_name], requester_id=[id], email=[requester_email], company_id=[id], and updated_since=[date] to your API request URLs.
    freshdesk-img6
  2. In addition to Freshdesk’s query string filtering, API Connector provides its own filtering functionality. JMESPath filtering (paid feature) lets you specify exactly which fields to pull. This gives you more precise control over which fields to display in your sheet. For example, with JMESPath filtering, you can create a filter for just a subset of ticket fields like this:
    [*].{priority:priority, group:group_id, technician:responder_id, company:company_id, client:requester_id, status:status, title:subject, ticketnum:id, type:type, queue:custom_fields.cf_queue, created:created_at, updated:updated_at}
  3. Both filtering types can be used together. Freshdesk’s native query string filtering lets you request less data from the server, which speeds up response time from the server and limits the initial data set. API Connector’s JMESPath filtering processes the data once the response has been received, which gives you better control about exactly which data gets displayed and speeds up the process of printing data into the sheet.

PART 6: HANDLE PAGINATION

By default, Freshdesk limits the number of records returned at one time to 30, as described here.freshdesk-img7

To get more, add the “per_page” parameter, like this:

https://YOUR_DOMAIN.freshdesk.com/api/v2/tickets?per_page=100

You can also combine with date-based parameters, like this:

https://YOUR_DOMAIN.freshdesk.com/api/v2/tickets?per_page=100&updated_since=2020-12-01T01:00:00Z

If your response still returns more than 100 records, you can shorten your time range to reduce the number of results, or changing your date range to retrieve additional sets of data. Alternatively, you can loop through pages automatically with pagination handling (paid feature). Freshdesk’s documentation shows that next page URLs are provided in a field called “link”, so you would loop through your records like this:
freshdesk-img8

1 thought on “Import Freshdesk Data to Google Sheets”

  1. Thanks Ana for this great work.
    As I connected to the Freshdesk API I needed to make some changes to the headers Authorization value. It worked using only the API key with a “:” at the end (no API nor x) and encode this string. Hope this saves some time for somebody using Freshdesk and Mixed Analytics.
    Best regards

    Reply

Leave a Comment