How to Connect JSON to Google Data Studio

By Melanie Rankin | 6 min read

Unless you’re already an expert in programming languages, visualizing JSON data in Google Data Studio can be a headache. Data Studio offers no direct connection with JSON files or APIs, and the manual processes for getting your JSON data into Data Studio is, in one word, convoluted. Luckily you have several third party platform options which can integrate your JSON data quickly, without the usual pain. 

We’ve compiled the best options for JSON to Google Data Studio integration, with a brief how-to and analysis of each, so that you can make an informed decision based on your needs. This article will cover manual options for JSON integration as well as the third-party platforms Dataddo, Panoply, and Supermetrics. 

JSON to Data Studio, Manual Connection

We have a piece of bad news: there’s really no way to directly funnel your JSON data or a URL into Google Data Studio. Data Studio always needs to connect to a bridging platform or service. If you are dead set on not downloading a third party service, there are a few work-arounds, but we find that the most straightforward method involves connecting your data to Google Sheets before routing it to Data Studio via the Google Sheets-Google Data Studio connector. 

Screen Shot 2021-02-04 at 3.00.51 PM

You’ll first need to add a script to Google Sheets that allows you to import raw JSON data. Then you can input the JSON url of whatever page you are trying to pull data from and configure it within Google Sheets to a format which is readable and transformable for Data Studio. You then import this spreadsheet to Data Studio, and the job is done. If you decide to go for this option, be sure to read the full run-down of how it works. 

Please note that for the sake of brevity, we’ve left a lot of details out of this description, such as the tutorial needed for adding scripts to Google Sheets and the JSON language functions required to transform the data to proper columns in Sheets. And keep in mind: this is the simplest option there is. 

Screen Shot 2021-02-04 at 3.32.47 PM

It goes without saying that creating this connection by hand, while it may not cost money, will cost you plenty of time, and comes with the added risk that your Google Sheets will break down (depending on the volume of data you’re loading). If it does, do you have the technical know-how to fix it? 

The single easiest way to avoid this frustration and ensure a reliable data connection is with a third party data management platform. We’ve outlined the options one-by-one: 

Dataddo: Universal JSON connector

Dataddo is a cloud-based, no-coding platform that can be used by anyone, from any industry, from complete non-programmers to coding gurus. It connects your data sources to any data destination and specializes in being the flexible option - any data source or destination you need to integrate which is not already available within the platform can be added on your request, within a few business days, at no additional charge. 

For JSON connections, Dataddo offers a “universal connector” which connects to any website or service where the user can provide Dataddo with the API URL of the website. Making the connection involves providing only: 

  • The API endpoint of the website you are pulling data from
  • Any authorization necessary (for private addresses)
  • A short script to define the metrics and dimensions of your data (the support team is happy to create this for you)

The Dataddo team is constantly available for a free consultation to assist should you run into any difficulties or snags during the process. 

Searching for Json

After joining your JSON data to Dataddo, you will be prompted with the API information necessary to join Dataddo to Google Data Studio and finish the live connection - then you’re done!

With over 20 destination options for your JSON data and unlimited data source connections, Dataddo is an excellent choice for flexibility. Create and define multiple destinations for your JSON API with just a few clicks, and manage your other Data Studio sources all from one location. Plus, Dataddo’s pay-per-source pricing scheme ensures that you only ever pay for the data you’re using. 

Panoply: JSON file upload

Panoply is another cloud-based data connection platform with an added element - it is interwoven with Amazon Redshift (a data warehouse) to store data while keeping it analysis-ready. 

To connect JSON to Google Data Studio in Panoply, you will use their “File Upload” function by clicking “Add Source” and “File Upload” from your main account page in Panoply. This will prompt you to open a .json file from your computer, which Panoply will transform into a table according to your specifications. Connecting this data to Data Studio will need to be done from your Data Studio account by connecting Panoply as a Data Studio data source. 

panoply file upload 2

(source: panoply.io/docs)

It’s important to note that this JSON integration is not applicable for JSON APIs or URLs. Panoply’s JSON connector is for static JSON files. You can upload a collection of raw JSON data to your Panoply account, but you cannot create a live connection to a changing or updating data source/website. Of course, you can extract JSON from a website, but if you need to do this manually each time you are looking for up-to-date numbers, the process becomes counterproductive. 

In addition, Panoply’s pricing leaves something to be desired, as it is fairly limiting in terms of data storage in its warehouse and the number of sources you can connect to Google Data Studio. If you’re not interested, there is one more possibility: 

Supermetrics: Custom JSON/CSV Connector

As opposed to the previous third party options which are both cloud-based, Supermetrics is a downloadable extension which you can apply to a handful of data destinations in order to bridge your data easily and cleanly from many sources. 

For JSON integrations, Supermetrics offers its JSON/CSV/XML connector which allows users to bring data into Data Studio from any source with an openly accessible API. It also works with extracted files of JSON data. To implement the service, start from your Google Data Studio account, add the Custom JSON/CSV/XML connector from Supermetrics as a new data source, and then input either the relevant API endpoint or import a file from your computer/Google Drive. 

Supermetrics json data studio connector

(source: supermetrics.com/blog/custom-json-data-studio)

While it’s great that the Supermetrics connector allows you to connect both JSON files and URLs, the tool does not come without limitations. Because Supermetrics is an extension and must be applied to each destination individually, it’s impossible to centrally manage your data if you have data flowing between multiple sources and destinations. Not to mention, with Supermetrics you pay per data destination and are still limited to the number of data sources you can connect to said destination. 

Why Choose Dataddo? 

We recommend Dataddo not only for the easy connections you can make to any JSON URL, but the ability to make as many data connections to Google Data Studio as you need and the option to manage all of these connections from a single point: your Dataddo control panel. Plus, with plans priced per data source, the affordability of the platform can’t be beat. Choose Dataddo for a seamless experience while moving your JSON data.

Connect JSON to Google Data Studio

Just a few quick steps and you'll find all your JSON data in your favorite BI tool.  

Connect JSON

 


Category: Industry Insights

Comments