Google Chart gallery made with Google Apps Script, Google Sheets, and The COVID Tracking Project Data API
A gallery of three Google Charts developed using Google Apps Script/Sheets, Javascript, HTML, CSS, and MDB 5 Bootstrap components:
This README file contains information on setting up the API endpoint and rendering each chart using Google Sheets as a backend.
In the Apps Script editor, create a function using the UrlFetchApp service to issue an HTTP request and recieve JSON object response from the data API:
function getJSON() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const s = ss.getSheetByName("data"); const s2 = ss.getSheetByName("state"); const url = "https://api.covidtracking.com/v1/states/daily.json"; const response = UrlFetchApp.fetch(url).getContentText(); const responseJSON = JSON.parse(response);Then, map JSON object to new array and write data to Google Sheets:
const result = responseJSON.map(r => [r.state, r.death, r.positive, r.date]); dataRange = s.getRange(2,1,result.length,4).setValues(result) }Here, create a function to get cleaned data from the spreadsheet, map/parse number data as integers, and return the values:
function returnData() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const s2 = ss.getSheetByName("state"); const readRange = s2.getRange(1,1,s2.getLastRow(),s2.getLastColumn()).getDisplayValues(); const chartData = readRange.map(r => [r[0],parseInt(r[1]),parseInt(r[2])]); chartData[0][2] = "Positive Cases"; chartData[0][1] = "Deaths"; return chartData; }In order to draw the geochart, a Google Maps API key is necessary. You can create a Google Cloud Platform project and generate an API key here. In the HTML of the webpage below, we load each chart package and register a callback function to execute when the charts finish loading:
<!DOCTYPE html> <html> <head> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> google.charts.load('current', { 'packages':['geochart','calendar','line'], mapsApiKey:'YOUR_MAPS_API_KEY' }); google.charts.setOnLoadCallback(getData);Next, we register a callback to our backend returnData() function:
function getData() { google.script.run.withSuccessHandler(drawSeriesChart).returnData(); }Here, we pass the data from our backend function into the data table, set chart options, instantiate and draw the chart, then pass the chart options as arguments:
function lineChart(returnData) { const data = new google.visualization.arrayToDataTable(returnData); const options = { chart: { title: 'COVID Positive Cases & Deaths Per Day', }, hAxis: { scaleType: 'log', }, width: 1200, height: 600, animation: { startup: true, duration: 20000, easing: 'in' } } const chart = new google.charts.Line(document.getElementById('chart_div')); chart.draw(data,google.charts.Line.convertOptions(options)); };This process is repeated ad nauseam for each chart until your eyes bleed. Then, the chart object is placed within a div in the HTML of the webpage:
<body> <div id="chart_div" style="width:400; height:300"></div> </body> </html>Lastly, in the app.gs file, we create a doGet function to generate an HtmlTemplate object, then call the evaluate() method to convert index.html file into an HtmlOutput object:
function doGet() { return HtmlService.createTemplateFromFile("index").evaluate(); }Please feel free to contact me with any suggestions or questions.
- v1.0.1 (April 1, 2021)