{"id":6146,"date":"2020-04-21T16:10:15","date_gmt":"2020-04-21T16:10:15","guid":{"rendered":"https:\/\/max-drake.cc\/?p=6146"},"modified":"2020-04-22T10:47:07","modified_gmt":"2020-04-22T10:47:07","slug":"how-to-import-json-data-into-google-sheets-to-create-a-live-covid-19-chart","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=6146","title":{"rendered":"How to import JSON data into Google Sheets to create a live COVID-19 chart"},"content":{"rendered":"\n

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

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

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.<\/p>\n\n\n\n

Article<\/h4>\n\n\n\n

The original article How to Import JSON Data Into Google Sheets With Auto Refresh \u2014 and Create a COVID-19 Widget in Less Than 5 Minutes<\/a><\/strong> has links to all the bits. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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. <\/p>\n\n\n\n

Also handy to know that there are some good functions available online (with a bit of searching). <\/p>\n\n\n\n

The final table looks very polished. <\/p>\n\n\n\n

Datawrapper<\/h4>\n\n\n\n

You need to share the google sheet<\/span><\/strong> file, or it wont load the data, and load from external data source<\/span><\/strong>, that way it’ll update. If you pull in as Google sheet it will be static data<\/em>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

On the Refine Tab add the following ticks as noted.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To change to flags select column and use the switch to display flags<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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. <\/p>\n\n\n\n

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. <\/p>\n\n\n\n