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

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

How it works

You get “no data” errors?

--

--

--

Data journalist at Neue Zürcher Zeitung (NZZ)

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Get started with React and LinkedIn Social login

React Hooks (Conceptual Understanding) — Part 1

Implementing sockets with NodeJS and the journey from novice to proficient

Nuxt.js and Vue.js: Reasons why they differ and when do they combine

app.js

Let,s Dive Into The Prime Concepts Of React

Why I love Jetpack Compose and what makes it different from other declarative frameworks.

3 🐈chy Open-Source Projects

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)

More from Medium

Get extra fields from User model in Strapi V4

ArcGIS Pro Python Clone Error, CondaVerification Error, Environments Clone Error solved!

Pairing Git with Webpack+React application

A beginners guide to Shopify Themekit and Schema

shopify developers banner