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.) 

Many APIs provide real-time data that you can use to create  live-updating charts. 
These data are often provided in JSON format like this one here by mathdroid on the number of COVID-19 cases:

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

1
Once you're on the Google sheet, from the top menu, go to File >  Make a copy to copy both the Google Sheet and the script.
2
From the top menu, go to  Tools > Script editor : 
3
You'll see an Apps Script file called ImportJSON which contains two script files called ImportJSON.gs and autoUpdate.gs.  (Note: If you can't open this Apps Script page and you're logged into multiple Google accounts, you may want to log out from all except the one you're using.) 

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]));
    }
  }
}
4
Click on autoUpdate.gs and replace the URL with your own URL to the JSON data:
5
Also, if you decide to change the name of the sheet you're importing the data to, make sure that the name matches the sheetName inside the function:  By default, the sheet is called Sheet1: 
6
Now, look at the sheet - you should now find that the data is in there: 
7

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: 

8
On the bottom right, click on Add Trigger:
9
Select autoUpdateJSON as the function to run and choose the event source as  Time-driven 
10

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 : 

11
That's it! Your sheet is ready. You can now go back to your Google Sheet and turn on sharing by clicking the "Share" button on the top-right corner:  Make sure that your Google Sheet is set so that Anyone with the link can view. Click on Copy link.
Now you can paste that link into step 1: Upload Data in Datawrapper after selecting Link external dataset configuration. If you're not familiar with what this is, check out our Academy article Automatic chart updates: How to publish charts with external data sources . 
If you have any questions, reach out to us at support@datawrapper.de

Related Articles