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 Looker Studio (formerly 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.
Make sure that the total data is maximum 3.9 megabytes in size, and that the result fits that limitation as well.
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 Looker Studio or Google Sheets.
- Open a new Google Sheets file.
- Click Extensions → Supermetrics → Launch to open the sidebar.
- Under Data source, select JSON/CSV/XML.
- Fill in the details under Report configuration.
- Fill in the API request URL to the Type URL field.
- Select the Data type accordingly, in this example, JSON.
- In this example, we’ll set the JSONpath as results.* to refine the relevant values from the result.
- Finally, click Get data.
Your data will be inserted starting from the cell you have selected in the spreadsheet.
- In Looker Studio, go to Create → Data source.
- Select the JSON/CSV/XML by Supermetrics data source.
- Authorize the connector.
- In the Parameters view, fill in the details for the configuration.
- Select the Data type accordingly, in this example, JSON.
- To the Source URL field, fill in the API request URL.
- In this example, we’ll set the JSONpath as results.* to refine the relevant values from the result.
- 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.