Open navigation

How to connect data from JSON/CSV/XML files

If you can't find a data source you want to report from in Supermetrics, you can connect a downloaded dataset to your Google Sheets spreadsheet or Looker Studio (formerly Data Studio) report. Any data in a valid CSV, JSON, XML, TSV, or Text format can be connected as a custom data source through Supermetrics. 


Before you begin

Connector setup

When you first open this connector in Looker Studio or Google Sheets, you'll be asked to fill out information to set it up. Learn more about each of these parameters.


Data type

Select your file's data type. See "File setup" below for more details on the specific requirements for each type of file.

Source URL or Google Drive path / Type URL

This is the address for your file. It can be a URL to any service as long as it gives a file to process.


The max length for the URL is 2082 characters. This is a limitation set by Google Apps Script. If your URL is longer, use a URL shortener.


Make sure that the file can be accessed directly from a URL. Alternatively, upload the file to a Google Drive location. If you use Google Drive, update the sharing setting of the file to "Anyone with the link can view".


Add dates in the URL or Drive path with these placeholders, using edate instead of sdate for the end date: 

  • #sdate# for a start date in Y-M-D
  • #sdate_ymd# for a start date as YMD
  • #sdate_u# for a start date as a Unix timestamp

Learn how to use dynamic date selections with files that support end dates.

Post string (optional)

If you're fetching data from an API, the post string is a parcel of data that will be sent to the endpoint you specify.

HTTP headers (optional)

If you need to, for example, provide your login credentials, they are typically given here in JSON format. This field can usually be left blank, but if you know you do need to use it, try:
{ "yourUsernameToken":"yourUserName", "yourPasswordToken":"yourPassword" }

JSONpath (optional)

This allows you to specify a relative path to JSON data you want to access, if it's not by default on first level of address you gave in first field. Usually, none is needed.

File setup

The file you're connecting to needs to adhere to specifications of the type of data you're connecting to. For a CSV file, it should only contain comma-separated data. Set the field headers on the first row, and the data in matching order to the following rows.

  • A JSON file needs to be in JSON format, an XML file needs to be in XML format, a TSV file needs to be tab-separated, and a Text file should contain some text.
  • Make sure the file can be accessed directly from a URL. Alternatively, upload the file to a Google Drive location.
  • Ensure that the source file is maximum 3.9 megabytes in size, and that the result fits that limitation as well.


Instructions

Looker Studio Google Sheets
  1. Log in to Looker Studio.
  2. Click CreateData source.
  3. Search for JSON/CSV/XML by Supermetrics, and select the connector.
  4. Click Authorize.
  5. Log in with the Google Account you use with Supermetrics.
  6. Click Authorize.
  7. Verify in the opening pop-up window that the authorization was successful, and close the window.
  8. In the Parameters view, fill in the details for the configuration. Learn more about parameters.
    1. Select a Supermetrics team.
    2. Under Data type, select your file type.
    3. Fill in your parameters, especially the Source URL.
  9. Click Connect in the top right corner.

The headers from your data will be set as dimension fields in the data source file. You can now create a report and start using your CSV data with the data source file.


Changing parameters

You can make changes to the connector's parameters at any time.

  • In Google Sheets, open the Supermetrics sidebar in any Sheet and click the Report configuration dropdown.
  • In Looker Studio, click the Edit icon next to the data source, and then click Edit connection. Scroll down to see the parameter fields.


More resources


Did you find it helpful? Yes No

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