How to Import JSON Data Into Google Sheets With Auto Refresh — and Create a COVID-19 Widget in Less Than 5 Minutes

There are tons of APIs out there that provide near real-time data that you can use to create automated charts and maps with free tools like Datawrapper. Coronavirus case data or stock market data for instance.

Those APIs usually provide data in JSON format. This is how you import that data into Google Sheets and make sure that it gets updated automatically. (Yes, it’s a bit quirky. There’s no coding or web server required though, plus people can still collaborate in your spreadsheet and collect data from other sources.)

Eventually, we create a nice-looking Datawrapper table widget showing the worst-hit countries by the novel coronavirus based on data collected by the Johns Hopkins University including:

• confirmed cases
• Covid-19 related deaths
• a seven-day average of the daily confirmed new cases and deaths
• how that average changed compared to the prior seven days

This is how the widget will look like — if you set it up properly in Datawrapper.

Step 1: Copy sample sheet and scripts

• Go to: https://docs.google.com/spreadsheets/d/17h6BCvGxecadXCIIfmxLta3wYLhKn7jLPnzrS4qOlEk/
• In Google Sheets click on File → Make a copy. All scripts (ImportJSON and triggerAutoRefresh) in Tools → Script Editor will be copied as well.

The spreadsheet should now be available in your Google Drive. Now you can populate your Google Spreadsheet with the ImportJSON script.

Just replace the URL in the corresponding sheet with your API of choice.

In the sample spreadsheet you find these in cell A1 of the allCountriesCases sheet:

=ImportJSON("https://covid19.mathdro.id/api/confirmed", "/", "noInherit,noTruncate,noPrefixHeaders", doNotDelete!$A$1)

and in cell J2 in topCountriesTimeline:

=TRANSPOSE(ImportJSON(“https://pomber.github.io/covid19/timeseries.json"; “/US”; “noInherit,noTruncate,noHeaders”; doNotDelete!$A$1))

The option parameter of ImportJSON is explained here.

If you’re wondering where the arrows in the Trend column come from: Those are calculated in the sheet topCountriesTimeline starting from cell I3:

=IF(AND(H3>=-0.0449, H3<=0.0449), “➙”, IF(AND(H3>=0.045),”➚”, “➘”))

Just change the numbers if you prefer other thresholds.

Almost done. Next step: Set up a script that generates a random number every x minutes thus triggering an auto-refresh in your Google sheet.

Step 2: Set up a Google “Trigger”

• Go to https://script.google.com/home/all
• Open the menu (three vertical dots) next to ImportJSON
• Click on Triggers
• Click on the blue button Add Trigger
• Choose which function to run: triggerAutoRefresh
• Select event source: Time-driven
• Select minute interval: 15 might be a reasonable value in our case

Step 3 (optional): Visualize your data

That’s it! You can now visualize your data with Datawrapper and embed the result on your website. This is how it works.

The first sheet datawrapper will be used for your visualization. Just make sure your Google sheet is set to Anyone with the link can view when you click the green Share button. In Datawrapper select Link external dataset as your source — not Import Google Spreadsheet.

Also, select English (en-US) as your Output locale in the Check & Describe tab in Datawrapper — or change the Locale of your spreadsheet in File → Spreadsheet settings accordingly. In the Visualize tab you should tick:

• Parse markdown
• Merge with emtpy cells
• Add first row to header

Flags can be displayed with Replace country codes with flags. The Countries column is for desktop, Country for mobile only.

How it works

Two scripts in your Google spreadsheet are doing all the work for you: ImportJSON.gs and triggerAutoRefresh.gs — a simple script that generates a random number in cell A1 of your sheet doNotDelete each time it gets triggered. If you change the sheet's name just make sure you change the function in triggerAutoRefresh.gs also. You can access both scripts in Google Sheets in Tools → Script Editor.

You get “no data” errors?

With this simple Google Apps Script you can reduce them to zero.

This article is based on a tutorial by Vadorequest (sheet currently broken though), widget inspired by spiegel.de & sueddeutsche.de, APIs used in sample sheet: pomber/covid19 & mathdroid/covid-19-api.