How to import JSON data into Google Sheets to create a live COVID-19 chart

This is in fact an article How to import JSON data into Google Sheets to create a live COVID-19 chart that I came across on my RSS feed from Datawrapper. I want to follow this as it sort of comes full circle.

I’d started my covid data exploration with Google Sheets and importXML() and now, after playing with API’s it seems a good time to revisit sheets and explore importJSON() which I havent used.

This allows me to get data and display it on a chart. Another thing that jumps out with this is that Datawrapper will call from a Google Sheet endpoint URL , that I wasn’t aware of. In fact, I’d sort of disregarded DataWrapper as I was linking to a CSV file and that was a bit static. I wanted something a bit more dynamic and this looks the right method to proceed with. Low Tech and free.

Article

The original article How to Import JSON Data Into Google Sheets With Auto Refresh โ€” and Create a COVID-19 Widget in Less Than 5 Minutes has links to all the bits.

Ther importJSON() seems to be an GAS file from github so not a native function inside Google Sheets. As shown below for a clean Sheet.

To trigger it a GAS script creates a new random number on a single sheet, when the importJSON() function reads that change it goes and calls the JSON file to update. A nice workaround if you can’t get the trigger to run directly.

Also handy to know that there are some good functions available online (with a bit of searching).

The final table looks very polished.

Datawrapper

You need to share the google sheet file, or it wont load the data, and load from external data source, that way it’ll update. If you pull in as Google sheet it will be static data.

On the Refine Tab add the following ticks as noted.

To change to flags select column and use the switch to display flags

This is something I found hard to navigate through in DataWrapper, there are some good settings but you need to know where they are and what they do. I suppose some familiarity with the programme would be good. I think I looked on Mr YouTube and din’t find anything too obvious to help.

I have got a ways down the road loading data into DataWrapper and nothing shows, so I get a bit frustrated and go to some other chart maker.

End comment

An elegant solution. I may have to try this oon a project. Maybe the NO2 for location may be a good one to try, although I haven’t seen if there is an API I can access for that particular idea to work.

I wonder if I should experiment with the DataWrapper tables in my JS map? May be worth a try.

This is definitely something handy. Also I like his cell notation for the Google Sheet table, I will have to explore how he does that, getting a double line in one Cell.

The “^” must mean move to another line. It must be a bit of code for DataWrapper because ity doesn’t do anything in google sheets.

My resulting play: