Load JSON into Google Sheet recurrently

Loading JSON into Google Sheet is dead easy nowadays with Google Script Editor,

Step 1

On Google Sheet, go to Tools -> Script Editor, add your javascript code to load JSON data. In my case, I read Corona virus stats API,

function myFunction() {
  var corona = UrlFetchApp.fetch("https://api.covid19api.com/summary");
  var cObject = JSON.parse(corona);

  var header = Object.keys(cObject.Countries[0]);

  var  res = [];
  res.push(header);
  cObject.Countries.forEach(function(row){
      let row = header.map(function(key){
        return row[key];
      });
      res.push(row);
  });
  var ss = SpreadsheetApp.getActive();
   Logger.log(res.length);
  var rng = ss.getActiveSheet().getRange(1, 1, res.length, header.length );

  rng.setValues(res);
}

The code reads the data from the API, gets the headers values from the JSON first row, builds an array. Then the array is loaded into the active spreadsheet. You can change cObject.Countries to match your API response format. This way any JSON can be converted to a Google Sheet.

Step 2

Choose Run -> Run function -> myFunction, to execute the code. You can views logs by visting View -> Logs.

Step 3

To make this scirpt run frequently, configure Triggers. Open Edit -> Current project's triggers. Configure "Select event source" as "Time-driven" and choose the specific frequency you are interested in.

Thats it, your Google sheet will now represent the JSON data, having proper header row values.

Self advertisement

If you want to convert your Google Sheet into a website, checkout myquicksite.com