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

  • 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 if you set it up in Datawrapper properly.
This is how the widget will look like — if you set it up properly in Datawrapper.

Step 1: Copy sample sheet and scripts

=ImportJSON("https://covid19.mathdro.id/api/confirmed", "/", "noInherit,noTruncate,noPrefixHeaders", doNotDelete!$A$1)
=TRANSPOSE(ImportJSON(“https://pomber.github.io/covid19/timeseries.json"; “/US”; “noInherit,noTruncate,noHeaders”; doNotDelete!$A$1))
=IF(AND(H3>=-0.0449, H3<=0.0449), “➙”, IF(AND(H3>=0.045),”➚”, “➘”))

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.

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

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Simon Haas

Simon Haas

Data journalist at Neue Zürcher Zeitung (NZZ)