Open navigation

How to connect JSON/CSV/XML data from an API

If you can't find a data source you want to report from in Supermetrics, you can connect JSON, CSV, or XML formatted data from an API as a custom data source in your Google Sheets spreadsheet or Data Studio report.


This guide covers a sample use case of connecting data through an API request URL. Learn more about connecting data from simple CSV files.


Before you begin

As the JSON/CSV/XML data source is applicable for custom cases, you need to find out if the data source has an API and get familiar with the relevant documentation to determine whether it can be used as a data source.


The example in this guide shows a working integration for JSON formatted data. This is to demonstrate the general steps of the setup, but these steps may not be directly applicable to other data sources.


Use case: iTunes Search API

Request URL

The iTunes Search API allows you to search for content such as apps, podcasts, or music within the iTunes Store, App Store, iBooks Store and Mac App Store.


The API documentation confirms that an API key is not required. The request URL must be in the correct format:

https://itunes.apple.com/search?parameterkeyvalue


This example would look up all albums for artist id 909253, Jack Johnson:

https://itunes.apple.com/lookup?id=909253&entity=album

Next, we'll use the request URL to set up the connection to Data Studio or Google Sheets.


Data Studio Google Sheets
  1. In Data Studio, go to Create → Data source.
  2. Scroll down to Partners connectors. Alternatively, use the search bar and select the JSON/CSV/XML by Supermetrics data source.
  3. Authorize the connector.
  4. In the Parameters view, fill in the details for the configuration.
    1. Select the Data type accordingly, in this example, JSON.
    2. To the Source URL field, fill in the API request URL.
    3. In this example, we’ll set the JSONpath as results.* to refine the relevant values from the result.
  5. Click Connect.
    • The fields of the data source will be based on the data available in the API. You can now create a report and start using the data fetched from the API.


Did you find it helpful? Yes No

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