How to create a live-updating visualization with JSON data
In this tutorial, you'll learn how to create live-updating charts with JSON data using Google Apps Script. (Don't worry, you don't have to know Google Apps Script. But you do need to use Google sheets.)
We prepared an example sheet on Google Sheet using this data, so all you have to do is replace the URL with the JSON data you want to use for your data. Note that JSON data can be very differently structured, so this might not work with a specific JSON you want to read. But simply give it a try, especially if it looks similar to the JSON structure in the image above.
Before we dive in, create a copy of our pre-prepared Google sheet & scripts by clicking here 👉 CLICK HERE



Add scripts to your pre-existing Google sheet
If you want to set up these script files in your pre-existing Google sheet, you could also do so by opening Google Apps Script in your existing sheet (Tools > Script editor), then adding these .gs files.
ImportJSON.gs can be download from this GitHub page. For autoUpdate.gs, create a new .gs file by clicking on the + button, then copy & paste the following script and save the file as autoUpdate.gs:
function autoUpdateJSON() { // Enter the URL of the JSON here: const url = "https://covid19.mathdro.id/api/confirmed"; // Enter the name of the Sheet that you'd like the JSON to be imported into here: const sheetName = "Sheet1"; importJSONData(url,sheetName); function importJSONData(url,sheetName) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var data = ImportJSON(url, "/", "noInherit,noTruncate,noPrefixHeaders", new Date().getTime()); for (var i = 0; i < data.length; i++) { sheet.getRange(i+1, 1, 1, data[i].length).setValues(new Array(data[i])); } } }




Now you need to set up that the script runs automatically at regular time intervals. To do so, go back to the Apps Script page (Tools > Script editor).
then find the timer icon on the left and click Triggers:


You can then chose how often you want it to run. Here, we've selected the update to run every hour. Once you've selected, click Save :


