How to Connect JSON to Looker Studio FOR FREE

By Melanie Rankin | 7 min read

Unless you’re already an expert in programming languages, visualizing JSON data in Looker Studio can be a headache. Looker Studio offers no direct connection with JSON files or APIs, and the manual processes for getting your JSON data into Looker 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 Looker 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 Looker 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 Looker Studio. Looker 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 workarounds, but we find that the most straightforward method involves connecting your data to Google Sheets before routing it to Looker Studio via the Google Sheets-Google Looker Studio connector. 

Connect your data to Google Sheets

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 that is readable and transformable for Looker Studio. You then import this spreadsheet to Looker 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. 

Click on Tools and select Script editor

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. 


Connect your data to Looker Studio FOR FREE with Dataddo's Free forever plan.


 

For JSON connections, Dataddo offers a “universal connector” that 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. 

json connector

After joining your JSON data to Dataddo, you will be prompted with the API information necessary to join Dataddo to Looker 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 Looker 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 elementit is interwoven with Amazon Redshift (a data warehouse) to store data while keeping it analysis-ready. 

To connect JSON to Looker 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 Looker Studio will need to be done from your Looker Studio account by connecting Panoply as a Looker Studio data source. 

Upload file in Panoply

(source: panoply.io/docs)

It’s important to note that this JSON integration is not applicable to 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 Looker 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 that 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 Looker 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 Looker 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. 

Add the Custom JSON/CSV/XML connector from Supermetrics as a new data source, and then input the API endpoint or import a your file

(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 Looker 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 beaten. Choose Dataddo for a seamless experience while moving your JSON data.

 

Connect JSON to Looker Studio FOR FREE

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

Start for Free


Category: Industry Insights, tips-tricks

Comments