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.


If you're using a publicly available Google Sheet (meaning its sharing setting is "Anyone with the link can view), you can paste its whole URL into this field.


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. Open the Supermetrics Looker Studio gallery.
  3. Navigate to JSON/CSV/XML and click Try.
  4. Log in with the Google Account you use with Supermetrics.
  5. Click Authorize.
  6. Select the connections you want to use in your reports, or click Add new connection to connect a new account.
  7. Click Continue to Looker Studio.
  8. In the Parameters view, fill in the details for the configuration. Learn more about parameters.
  9. Under Data type, your file type.
  10. Fill in your parameters.
  11. 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.