Google Sheet JSON Data Import

Overview

A simple script (used in the Covid-19 Stats report!) demonstrating how to dynamically add rows of JSON data into a Google Sheet. Comments have been added for guidance on what each section of the script is doing.

To get started, simply open a new Google Sheet, go to Tools > Script Editor, and paste this script in (you can remove the default function text that will be pre-loaded when you open the window).

Note that this script is programmed to drop data into a tab of the sheet named "Dataset" and it will also reference a date on a tab named "Trigger" to ensure that it's not adding in duplicative data. So if you don't have this structure, this script will not work until you make adjustments.

For a more in-depth example that uses custom fields and authorization tokens, see the CallRail Reporting Google Sheet Script.

function importJSONData() {

  //get date from two days ago (most recent data available from CDC)

  var now = new Date();

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var date = new Date(now.getTime() - MILLIS_PER_DAY*2);

  var submission_date = Utilities.formatDate(date, 'GMT-5', 'yyyy-MM-dd').toString();

  //establish url & options

  var url = 'https://data.cdc.gov/resource/9mfq-cb36.json?submission_date='+submission_date+'T00:00:00.000';

  var options = {

    method: 'GET',

    headers: { 'cache-control': 'no-cache',

              Connection: 'keep-alive',

              'accept-encoding': 'gzip, deflate'

             }

  };

  //get data & format

  var output = UrlFetchApp.fetch(url, options);  

  var content = output.getContentText();

  var json = JSON.parse(content);

  //establish sheet & range

  var sheet = SpreadsheetApp.getActive().getSheetByName('Dataset');

  var range = sheet.getRange('D:D');

  var startRow = range.getValues().filter(String).length + 1;

  //get max date from dataset and find datedif in milliseconds for making sure that we don't log data that's already been logged

  var triggerSheet = SpreadsheetApp.getActive().getSheetByName('Trigger');

  var maxDateLogged = triggerSheet.getRange('A1').getValue();

  var dateDif = (date - maxDateLogged);

  //set up data feed

  var rows = [],

      data;

  for (i = 0; i < json.length; i++) {

    data = json[i];

    rows.push([data.submission_date.replace("T00:00:00.000", ""), data.state, data.new_case, data.new_death]);

  }

  //log data if data exists and if not already present

  if(rows.length > 0) {

    if(dateDif > 86399999){

      var dataRange = sheet.getRange(startRow, 3, rows.length, 4);

      dataRange.setValues(rows);

      sheet.getRange(startRow, 3, rows.length, 1).setNumberFormat("M/d/yy");

    }

    else{

      Logger.log('Data for this date is already logged.');

      SpreadsheetApp.getActiveSpreadsheet().toast('Data for this date is already logged. No data has been added.');

    }

  }

  //log for no data

  else {

    Logger.log('No data to record.');

    SpreadsheetApp.getActiveSpreadsheet().toast('No data to record.');

  }

}