Sample Use Cases - How to use Custom JSON/CSV connector

Before starting you should find out if the data source actually has an API and get familiar with the relevant documentation. These example have been derived from real working integrations for getting JSON formatted data, but the information might not be applicable to the connector configuration you need to use.

1. Use case - Online customer service software with Supermetrics for Google Sheets

According to the relevant documentation, this particular online customer service software requires Base64 encoded combination of username and API key, separated by a semicolon (USERNAME:API_KEY).

Step 1: Obtain your API key by signing into the customer service software. This time it was located under “Account” menu.

Step 2: As we are doing the integration with Supermetrics for Google Sheets, let’s use Google Sheets for creating the Base64Auth as well.

You can use Tools -> Script editor:

And create a script like this:

Once you are done with your script, click File -> Save.

After this you use the following formula for the encoding part:

Step 3: Now we can open the sidebar from Add-ons -> Supermetrics -> Launch sidebar, and start configuring the connector

Select query type “Custom data”, insert the API endpoint URL to the “URL or Drive file path” and fill in other necessary information as well. Please refer to the API documentation for further details. 

For example something like this:
Type URL or Drive file path: https://api.platform.domain/users
Data Type: JSON
HTTP headers: {
"Authorization": "Basic XXXX",
"X-API-Version": 2

In this case the XXXX needs to be changed to the string you got from the previous step. The "https://api.platform.domain/users" will return information about the customer service representatives from the underlying system.

Step 4: Hit “Get Data to Table” to get the query results into your spreadsheet file:

2. Use case - iTunes Search API with Supermetrics for Data Studio

The Search API allows you to search for content within the iTunes Store and Apple Books Store.

According to the relevant documentation, this particular API does not require an API key. You just have pass a fully-qualified URL content request in the following format:

Step 1: Construct the URL with relevant parameters, for example:

This example would look up all albums for artist "Jack Johnson".

Step 2: Select "Data type: JSON", place the URL you have constructed to the "Source URL or Google Drive path" input box. In this case you will have to use JSONpath as well to extract the relevant values from the result set:

Step 3: Click "CONNECT" and "ADD TO REPORT" and select relevant fields to get data to your Data Studio dashboard:

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.