How to create a Coronavirus Dashboard in Google Data Studio using Two Minute Reports

In this tutorial, we are going to see how to create a custom dashboard using external web-services like REST APIs inside Google Data Studio without writing a single line of code.

We will create a Covid-19 live tracker dashboard like the below that automatically updates periodically and fetches data of all time covid cases automatically.

In this tutorial, you will learn

  • How to get data from REST APIs in Google Data Studio
  • How to format, sort the incoming API data
  • How to make dynamic API requests with formulas
  • How to update data periodically
  • How to publish as an interactive webpage

Firstly, we have to identify the API source to feed the data for our dashboard. For our example, we’ll be using this free (https://github.com/mahabub81/covid-19-api) API with enough data endpoints for our dashboard.

Connect Google Data Studio with the Coronavirus API

  • First, we’ve to add our data source to our GDS report
  • Select API Bridge as the Data Source type, which is the JSON API connector of Two Minute Reports, and enter https://mahabub81.github.io/covid-19-api/api/v1 in the Base URL section, we can find the Base URL from the API documentation.
  • Since our API documentation prefixes https://mahabub81.github.io/covid-19-api/api/v1 in all its endpoints, we add it as the base URL.
  • There is no authentication required for using the API, so we skip other options and proceed to save.

Let’s explore the API and get some RAW data to play with, in Google Data Studio. There are so many endpoints to choose from, from the Covid-19 API.

Get data from a REST API in Google Data Studio

I enter just “/world-summary-time-series.json” in the URL Suffix as we know the rest of the URL (“https://mahabub81.github.io/covid-19-api/api/v1”) was already specified as the Base URL when adding the data source. I skip other advanced options and press Run Query, to see the data.We can easily create the charts using by selecting the data and going to Insert -> Chart. We format the chart as per our liking and create the two charts necessary for our dashboard.

There are multiple options to choose from in the Data Queries section of API Bridge, to learn about each of them in more detail, check our API Bridge knowledge base.

Using JMES Path to Format & Sort the incoming API data

if you are already familiar with JMES Path you can write the JMES Path, or you can use this nifty utility to create the JMES Path for you.

  • In select fields, select the fields that we are interested in
  • In Sort by, select latest.active and select descending so that the countries with the most active cases show up at the top
  • Then copy the entire text from the Query section. You can change the JMES query to your needs.
  • Am going to bring iso2 field to the front, so that its easier to create charts in Google Data Studio, so the updated JMES Path query looks like this
  • [] | reverse(sort_by(@, &latest.active)) | [].{iso2: iso2, country_region: country_region, active: latest.active, confirmed: latest.confirmed, deaths: latest.deaths, last_updated_at: latest.last_updated_at}
  • Paste it in the JMES Path and re run the query.

Hope this article was of some use to you Thanks and Cheers!

Was this helpful?

Next Article

How to use JMESPath?