How to Connect JSON to Looker Studio (Google Data Studio)

JSON (JavaScript Object Notation) is a format for structuring data and is often used to exchange data between web applications. Looker Studio is a data visualization platform that is used to transform data into reports and dashboards. Although Looker Studio doesn’t offer a native connector for JSON files, there are other ways to visualize JSON data in Looker Studio.

There are manual ETL methods for connecting JSON to Looker Studio, but such processes can be quite technical. For a smooth and efficient transfer, you can utilize no-code platforms such as Coupler.io that automate the process of importing data to the visualization tool. 

In this article, we’ll explore how you can connect JSON API data to Looker Studio so you can seamlessly visualize and power reports with your data. 

Why connect JSON to Looker Studio automatically?

Google Looker (Data) Studio is a data analytics and visualization platform that makes it easy to create reports or dashboards so you can gain actionable insights from data and make better decisions. If you need to visualize JSON often, it’s a good idea to automate imports in Looker Studio. Below are some of the main benefits:

• Saves time

When you automate JSON data imports into Looker Studio, you can easily access your data without having to connect all over again. This eliminates the stress of performing repetitive tasks and allows you to focus on the analytics. If you have to load JSON data into Looker Studio every time, your reporting tasks will generally take more time to complete. 

• More data sources

Automating JSON imports allows you to integrate data from various sources. You can connect to apps that aren’t supported with Looker Studio’s connectors and automate such imports too.

It helps to analyze faster and build numerous reports in Looker Studio as virtually any source app is within your reach.

• Access to real-time data

If you’d like to analyze and gain immediate insights from your data, it’s best to automate JSON imports to Looker Studio. It allows you to power dashboards and measure the metrics quicker. Working with real-time data also informs your analytics with comparative data and progression.  

• Data backup

Although you can import JSON data to Looker Studio whenever you’d like to visualize it, setting up automatic imports of endpoint data can also be beneficial. You may use it to back up your JSON files for analytical purposes in case you’d like to visualize it later on. 

The best way to connect JSON API to Looker Studio 

You can send GET requests to fetch data from different APIs but it’s time-consuming when you have to do it repeatedly. An alternative is scripts, in Python, for example, that can automate the process to some extent, but these tend to be complex to set up and maintain.

For that reason, many users opt to use third-party solutions to connect JSON to Looker Studio. One example of such an app is Coupler.io – an all-in-one data analytics and automation platform that allows you to integrate data from numerous apps into preferred destinations. It’s a no-code solution that takes roughly five minutes to set up, after which the data will refresh according to a schedule you choose – for example, daily or hourly.

What’s more, using Coupler.io, you can connect to data from 70+ apps through direct integrations and hundreds more via JSON importer. Coupler.io can blend and transform your data and deliver it to a destination of your choice.

Speaking of destinations – when connecting JSON to Looker Studio, you have two options. You can either: 

• Connect JSON data directly to Looker Studio or 
• Send the data through an intermediary app, like BigQuery or Google Sheets 

Direct integration is the quickest as it takes a few minutes to set up. Sending data to a spreadsheet or a data warehouse may be useful too if you, for example, plan to apply some heavy transformations to the data prior to connecting it to Looker Studio.

We’ll show you how to set up both Looker Studio integrations in subsequent chapters. 

How to import JSON to Looker Studio – direct method

Now let’s talk about a direct integration. Here, we’ll use data from Asana, a popular project management app, that we’ll connect to Looker Studio.

Note: Coupler.io now offers a direct integration between Asana and Looker Studio which will arguably be a more convenient method for connecting both apps. However, read on to understand how exporting a JSON file works.

• For starters, once you’ve decided what kind of data you want to fetch, jump to the respective API docs and find the endpoint you want to use. For example, we want to get the list of projects from our Asana workspace.
• Following the same docs, figure out the authorization method you’ll need to provide. In Asana, for example, we need to set up a developer app and a personal access token.

• Next, create a Coupler.io account, a free 14-day trial is available, and no credit card is required. Alternatively, log in if you already have an account. Then, click Add new importer and pick:
  • JSON as a source
  • Looker Studio as a destination

• Name the importer and enter the JSON URL for the API endpoint you want to use.

• Select the HTTP method following the API documentation. In most cases, you’ll rely on a GET request.

• Next, take care of the authorization as outlined in the docs you use. Here is an example:

• Enter some URL query parameters to help narrow your results if you’d like and specify the desired columns. Leaving this field empty will result in importing all available columns.

• Optionally, add the path to select nested objects. Then, jump to the destination settings.

Setting up a JSON data source in Looker Studio

Now, you need to go about setting up a data source in Looker Studio. Follow the instructions in the wizard to authorize access (one-time only) and then set up a data source in Looker Studio. Click Connect and wait until the JSON data is imported.

• You’re all set! However, it’s recommended that you also set up a data refresh schedule at this point. This will push the fresh data to Looker Studio from your endpoint as frequently as you need and keep your dashboards up-to-date.

Click Save and Run, and the data will be refreshed right away. That’s all!

How to connect JSON source to Looker Studio via Google Sheets

Now, let’s look at the indirect method you can use to connect JSON to Looker Studio. We’ll first send the data to Google Sheets and then connect it to Looker Studio via a native connector.

• Set up Coupler.io as we described in the previous chapter. Pick JSON as a source and Google Sheets as a destination. Alternatively, you can use BigQuery if you prefer, but we wouldn’t recommend Excel here as it’s not that straightforward to connect it to Looker Studio next (as it is for Google’s products).

• Set up the JSON source precisely as we described above.
• Next, configure the destination app. For Google Sheets, connect and authorize access to your account and then choose the spreadsheet and sheet to your import.

• Then, set up a schedule and click Save and Run. Here’s sample data from Asana’s API endpoint populated in Google Sheets. 

• Next, we’ll connect Google Sheets to Looker Studio using a native connector. Log in to your Looker Studio account. Click the Create button and proceed.

Select Google Sheets from the list of Looker Studio’s native connectors.

• Choose the spreadsheet and worksheet containing JSON and click Connect.

That’s it! 

Summary: best connector for JSON and Looker Studio

As we discussed earlier, there’s no native method for connecting JSON to Looker Studio. If you’re after automating the process, Coupler.io can help you in this mission. Throughout this article, we described two approaches:

• Connecting JSON to Looker Studio directly through a direct connector.
• Sending JSON data to an intermediary app, like Google Sheets, and then connecting the sheet to Looker Studio.

Each has its pros & cons, so give both a try and see which works best for you.

Last but not least – at Coupler.io, we offer data consultancy services for those needing custom data projects, such as interactive dashboards, automated data flows, data integrations, data alerts, and more. If you or your team lack the expertise or simply time to go about such things, give us a shout and let’s talk about your project. 

Thanks for reading!