Get the latest count of confirmed cases, recoveries and deaths from the COVID-19 Coronavirus via an HTTP API
Tags: node, javascript, serverless, tutorial
Published: 2020-02-06 00:54:30

<p>A few days ago I started searching for an API to programmatically get the latest data about the global coronavirus outbreak. I came across several visualization and scraping projects, but I couldn't find a simple way to query the latest data programmatically — so I created an HTTP API that returns the latest data in JSON format using a <a href="https://pipedream.com/@pravin/http-api-for-latest-jhu-csse-2019-ncov-wuhan-coronavirus-data-set-p_G6CLVM/edit">Pipedream workflow</a>. </p><p>The API fetches the latest number of confirmed cases, recoveries and deaths from a public <a href="https://docs.google.com/spreadsheets/u/1/d/1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM/htmlview?usp=sharing&amp;sle=true">Google Sheet</a> published by the team at the <a href="https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6">Center for Systems Science and Engineering (CSSE) at John Hopkins University</a>, and returns both the raw regional breakouts as well as summary stats in JSON format. It also caches the data for up to 5 minutes to improve performance. The raw data is aggregated by the team at the CSSE from multiple sources including the <a href="https://www.who.int/emergencies/diseases/novel-coronavirus-2019/situation-reports">WHO</a>, <a href="https://www.cdc.gov/coronavirus/2019-ncov/index.html">CDC</a>, <a href="https://www.ecdc.europa.eu/en/geographical-distribution-2019-ncov-cases">ECDC</a>, <a href="http://www.nhc.gov.cn/yjb/s3578/new_list.shtml">NHC</a> and <a href="https://3g.dxy.cn/newh5/view/pneumonia?scene=2&amp;clicktime=1579582238&amp;enterid=1579582238&amp;from=singlemessage&amp;isappinstalled=0">DXY</a>, and updates are published to Google Sheets multiple times per day.</p><h2 id="using-the-api">Using the API</h2><p>To use the API, just make an HTTP request to the following endpoint URL:</p><pre><code>https://coronavirus.m.pipedream.net/</code></pre><p>You can test it by loading the URL <a href="https://coronavirus.m.pipedream.net/">in your browser</a> or app, by running <code>curl <a href="http://coronavirus.m.pipedream.net/">https://coronavirus.m.pipedream.net/</a></code> in a terminal, or by <a href="https://pipedream.com/@pravin/test-http-api-for-2019-ncov-coronavirus-data-published-by-jhu-csse-p_zACR6x">copying and running this workflow</a>.</p><p>The API returns:</p><ol><li><strong>Global, Mainland China and Non-Mainland China summary stats</strong> (count of cases, recoveries and deaths)</li>
<li><strong>Raw data </strong>(counts by region as published in the Google Sheet)</li><li><strong>Metadata</strong> (including when data was last published and the cache status)</li></ol><p><strong>Note: </strong>Data is cached using <a href="https://docs.pipedream.com/workflows/steps/code/#managing-state">$checkpoint</a> to improve performance. The cache is updated if it's more than 5 minutes old (<a href="https://pipedream.com/@pravin/http-api-for-latest-jhu-csse-2019-ncov-wuhan-coronavirus-data-set-p_G6CLVM/edit">view the workflow code</a> or read more below).</p><h2 id="sample-api-response">Sample API Response</h2><p>Following is a sample of the data returned by the API. <strong>Note:</strong> the <code>rawData</code> array is truncated in this example to only show a single result — query the endpoint URL to retrieve the full response.</p><pre><code>{
  "apiSourceCode": "https://pipedream.com/@/p_G6CLVM",
  "cache": {
    "lastUpdated": "2 minutes ago",
    "expires": "in 3 minutes",
    "lastUpdatedTimestamp": 1580925783250,
    "expiresTimestamp": 1580926083250
  },
  "summaryStats": {
    "global": {
      "confirmed": 24630,
      "recovered": 1029,
      "deaths": 494
    },
    "mainlandChina": {
      "confirmed": 24405,
      "recovered": 1020,
      "deaths": 492
    },
    "nonMainlandChina": {
      "confirmed": 225,
      "recovered": 9,
      "deaths": 2
    }
  },
  "rawData": [
    {
      "Province/State": "Hubei",
      "Country/Region": "Mainland China",
      "Last Update": "2/5/20 16:43",
      "Confirmed": "16678",
      "Deaths": "479",
      "Recovered": "538"
    },
  ],
  "dataSource": {
    "googleSpreadsheetId": "1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM",
    "range": "Feb05_1220PM!A1:F1000",
    "dataLastPublished": "44 minutes ago",
    "googleSheetLastModified": "2020-02-05T17:27:39.593Z",
    "publishedBy": "John Hopkins University Center for Systems Science and Engineering",
    "ref": "https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6"
  }
}e</code></pre><h2 id="capabilities">Capabilities</h2><p>The workflow behind this API uses the following capabilities and it runs on Pipedream for free (<a href="https://pipedream.com/@pravin/http-api-for-latest-jhu-csse-2019-ncov-wuhan-coronavirus-data-set-p_G6CLVM/edit">view, copy, modify and run the workflow code</a>):</p><ul><li><a href="https://docs.pipedream.com/workflows/steps/triggers/#http">HTTP trigger</a></li><li><a href="https://docs.pipedream.com/workflows/steps/code/#code">Node.js code steps</a></li><li><a href="https://www.npmjs.com/package/googleapis">Google</a> and <a href="https://momentjs.com/">Moment.js</a> <a href="https://docs.pipedream.com/workflows/steps/code/#using-npm-packages">npm pacakges</a> (to use any npm package, just <code>require</code> it — no <code>package.json</code> or <code>npm install</code> required)</li><li><a href="https://docs.pipedream.com/workflows/steps/code/auth/#auth-in-code-steps">Auth managed by Pipedream</a> (for Google Sheets and Drive)</li><li><a href="https://docs.pipedream.com/workflows/steps/code/#managing-state">$checkpoint</a> (maintains state across workflow executions)</li><li><a href="https://docs.pipedream.com/workflows/steps/triggers/#http">$respond() </a>(returns an HTTP response to a client)</li><li><a href="https://docs.pipedream.com/workflows/steps/#step-exports">Step exports</a> (provides observability into data and enables workflows to pass data to later steps via the <code>steps</code> object)</li></ul><h2 id="how-it-works">How It Works</h2><p>The HTTP API works by <a href="https://pipedream.com/@pravin/http-api-for-latest-jhu-csse-2019-ncov-data-set-p_G6CLVM/edit">triggering the Node.js code in this workflow</a> on every request to <a href="http://coronavirus.m.pipedream.net/"><code>https://coronavirus.m.pipedream.net/</code></a>.  The workflow consists of multiple steps to fetch and cache new data, transform and aggregate it, and finally respond to the client.</p><figure class="kg-card kg-image-card"><img src="https://blog.pipedream.com/content/images/2020/02/coronavirusworkflowdemo.gif" class="kg-image"></figure><p>Next, I'll explain some of the key steps in the workflow.</p><h3 id="steps-trigger">steps.trigger</h3><p>When you select an HTTP / Webhook trigger, Pipedream automatically generates a unique endpoint URL to trigger your workflow code. Since I'm sharing the endpoint URL publicly (<a href="http://coronavirus.m.pipedream.net/"><code>https://coronavirus.m.pipedream.net/</code></a>), anyone can make a request to execute the code and get the response.</p><h3 id="steps-filter_favicon_requests">steps.filter_favicon_requests</h3><figure class="kg-card kg-image-card"><img src="https://blog.pipedream.com/content/images/2020/02/pipedream.com_@pravin_http-api-for-latest-jhu-csse-2019-ncov-wuhan-coronavirus-data-set-p_G6CLVM_edit_e-1XOsWT8mQeeER7cB9AIgVSMEJhK.png" class="kg-image"></figure><p>The first code step filters out duplicate requests caused by web browsers requesting a <code>favicon.ico</code> asset when loading the endpoint URL  — if the the triggering URL contains <code>favicon.ico</code>, the workflow execution ends early and no additional steps or code are executed.</p><pre><code class="language-node.js">if (steps.trigger.event.url.indexOf('favicon.ico') !== -1) {
  $end('Terminating workfow for favicon request')
}</code></pre><h3 id="steps-get_data">steps.get_data</h3><p>Next, we either refresh the cache with the latest data from both Google Sheets and Google Drive (<a href="https://www.npmjs.com/package/googleapis">using the <code>googleapis</code> npm package</a> with auth managed by Pipedream), or we return the data cached to <code>this.$checkpoint</code>.</p><p>First, we initialize <code>this.checkpoint</code> if it is <code>undefined</code>:</p><pre><code class="language-node.js">if (typeof this.$checkpoint === 'undefined') {
  // initialize this.$checkpoint
  this.$checkpoint = {}
  this.$checkpoint.data = {}
  this.$checkpoint.ts = 0
  this.$checkpoint.lastModified = ''
  this.$checkpoint.range = ''
  this.$checkpoint.spreadsheetId = ''
  this.$checkpoint.lastPublished = 0
}</code></pre><p>Then we determine whether the cache should be updated. There are two processes that will trigger a cache refresh:</p><ol><li>I'm running <a href="https://pipedream.com/@pravin/update-cache-for-jhu-csse-2019-ncov-data-set-p_LQCJb8/edit?e=1XOoQ8yqtbkRYXh79ypF7Tv0xqh">a separate workflow on a schedule to update the cache every 4 minutes</a>. That workflow simply makes a request to the endpoint URL for this workflow and passes <code>refresh</code> as the value of the query parameter <code>action</code>, and the <a href="https://docs.pipedream.com/environment-variables/#environment-variables">environment variable</a> <code>process.env.CSSE_NCOV_REFRESH_TOKEN</code> as the value for of the query parameter <code>token</code>. The goal for this secondary process is to improve performance for end users, since it's slower to fetch and process live data from Google Sheets (ideally, the only time the cache is updated is via this out of band process).</li><li>However, if a user attempts to retrieve data by making a request to the API and the cache is older than 5 minutes, then that should also trigger a real-time lookup to Google Sheets will be triggered (this should only happen if #1 above fails)</li></ol><pre><code>this.dataExpiry = 5 * 60 * 1000

if (((new Date().getTime() - this.$checkpoint.ts) &gt; (this.dataExpiry)) || 
    (event.query.action === 'refresh' &amp;&amp; event.query.token === 
     process.env.CSSE_NCOV_REFRESH_TOKEN)) {
  this.updateCache = true
} else {
  this.updateCache = false
}
</code></pre><p><strong>Note:</strong> I'm using the step export <code>this.updateCache</code> to determine whether to fetch new data because I'm going to reference this value in a later step (I'll be able to reference this value as <code>steps.get_data.udpateCache</code>). Using step exports also provides default observability into exported data, so I can easily see what condition was triggered for each event:</p><figure class="kg-card kg-image-card kg-card-hascaption"><img src="https://blog.pipedream.com/content/images/2020/02/pipedream.com_@pravin_http-api-for-latest-jhu-csse-2019-ncov-wuhan-coronavirus-data-set-p_G6CLVM_edit_e-1XP5x0MtupiurQ0pHOBYtzbHvJB--2-.png" class="kg-image"><figcaption>Select an event to inspect the execution and exports for each step</figcaption></figure><p>Finally, if <code>this.updateCache</code> is <code>true</code>, then we fetch the latest data using the <code>googleapis</code> npm package and save it to <code>this.$checkpoint</code> (which maintains state across workflow executions). Otherwise, we simply return the value of <code>this.$checkpoint</code>.</p><pre><code>if (this.updateCache === true) {
  // fetch the latest data from the Google Sheet
  console.log('Fetching new data')
  const {google} = require('googleapis') 

  const auth = new google.auth.OAuth2()
  auth.setCredentials({ 
    access_token: auths.google_sheets.oauth_access_token 
  })
  const sheets = await google.sheets({
    version: 'v4', 
    auth
  });

  this.$checkpoint.spreadsheetId = params.spreadsheetId

  let response = await sheets.spreadsheets.values.get({
    spreadsheetId: this.$checkpoint.spreadsheetId,
    range: params.range
  })
  this.$checkpoint.data = response.data
  this.$checkpoint.ts = new Date().getTime()
  
  // get the date/time the file was last modified
  auth.setCredentials({ 
    access_token: auths.google_drive.oauth_access_token 
  })
  const drive = await google.drive({version: 'v3', auth});
  this.$checkpoint.lastModified = (await drive.files.get({
    fileId: this.$checkpoint.spreadsheetId,
    fields: params.fields
  })).data.modifiedTime

  // check if the tab name was updated
  // which indicates new data was published
  if (response.data.range !== this.$checkpoint.range) {
    this.$checkpoint.range = response.data.range
    this.$checkpoint.lastPublished = this.$checkpoint.lastModified
  }
} else {
  console.log('Return cached data')
}

return this.$checkpoint</code></pre><p><strong>Note:</strong> I connected my Google Sheets and Drive accounts to this step and used the <code>auths</code> object in code to securely pass the oauth access token to Google's API for authentication. E.g.,</p><pre><code>const auth = new google.auth.OAuth2()
auth.setCredentials({ 
  access_token: auths.google_sheets.oauth_access_token 
})</code></pre><p>If you copy the workflow into your account to modify and run it yourself, then you'll need to connect your own accounts.</p><figure class="kg-card kg-image-card"><img src="https://blog.pipedream.com/content/images/2020/02/image-3.png" class="kg-image"></figure><h3 id="steps-transform_data">steps.transform_data</h3><p>The data returned from Google Sheets in the previous step is an array of arrays representing the rows and columns of data in the sheet. This step makes the data more ergonomic by transforming the data into an array of JSON objects, with each value matched with its respective key (based on the value in the header).</p><pre><code>const transformedData = [], originalData = steps.get_data.$return_value.data.values
let rowCount = 0

originalData.forEach(function(row){
  if (rowCount &gt; 0) {
    let obj = {}
    for (let i=0; i&lt;row.length; i++) {
      obj[originalData[0][i]] = row[i] 
    }
    transformedData.push(obj)
  }
  rowCount++
})

return transformedData</code></pre><h3 id="steps-summarize_data">steps.summarize_data</h3><p>This step returns a JSON object with the total count of confirmed cases, recoveries and deaths, as well as subtotals for Mainland China and non-Mainland China. The data is cached to <code>this.$checkpoint</code> and it uses the <code>updateCache</code> export from <code>steps.get_data</code> to determine whether to update the cache, or return previously cached data.</p><pre><code>if (steps.get_data.updateCache === true) {
  console.log('updating cached stats')

  // initialize the stats object
  const stats = {
    global: { confirmed: 0, recovered: 0, deaths: 0 },
    mainlandChina: { confirmed: 0, recovered: 0, deaths: 0 },
    nonMainlandChina: { confirmed: 0, recovered: 0, deaths: 0 },
  }

  function incrementTotals(statsObj, regionObj) {
    statsObj.confirmed += parseInt(regionObj.Confirmed)
    statsObj.recovered += parseInt(regionObj.Recovered)
    statsObj.deaths += parseInt(regionObj.Deaths)
    return statsObj
  }

  steps.transform_data.$return_value.forEach(function(region){
    // increment global totals
    stats.global = incrementTotals(stats.global, region)
    if (region['Country/Region'] === 'Mainland China') {
      // calculate totals for mainland china
      stats.mainlandChina = incrementTotals(stats.mainlandChina, region)
    } else {
      // calculate totals for non-mainland china regions 
      stats.nonMainlandChina = incrementTotals(stats.nonMainlandChina, region)
    }
  })

  this.$checkpoint = stats
} else {
  console.log('using cached stats')
}

return this.$checkpoint</code></pre><h3 id="steps-respond_to_client">steps.respond_to_client</h3><p>Finally, we construct the body based on data exported and returned from previous steps, and use the <a href="https://momentjs.com/">moment.js</a> npm package to provide human readable relative dates/times. We use $respond() to issue the response, set the <code>content-type</code> header to <code>application/json</code> and <code>JSON.stringify()</code> the data before returning it as the response body.</p><pre><code>const moment = require('moment')

const body = {}
const lastUpdatedTimestamp = steps.get_data.$return_value.ts
const expiresTimestamp = steps.get_data.dataExpiry 
                         + steps.get_data.$return_value.ts

body.apiSourceCode = `https://pipedream.com/@/${steps.trigger.context.workflow_id}`
body.cache = {
  lastUpdated: moment(lastUpdatedTimestamp).fromNow(),
  expires: moment(expiresTimestamp).fromNow(),
  lastUpdatedTimestamp,
  expiresTimestamp
}
body.summaryStats = steps.summarize_data.$return_value
body.rawData = steps.transform_data.$return_value
body.dataSource = {
  googleSpreadsheetId: steps.get_data.$return_value.spreadsheetId,
  range: steps.get_data.$return_value.range,
  googleSheetLastModified: steps.get_data.$return_value.lastModified,
  dataLastPublished: moment(steps.get_data.$return_value.lastPublished).fromNow(),
  dataPublishedBy: `John Hopkins University Center for Systems Science and Engineering`,
  ref: `https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6`
}

await $respond({
  immediate: true,
  status: 200,
  headers: {
    'content-type': 'application/json'
  },
  body: JSON.stringify(body)
})</code></pre><h2 id="feedback">Feedback</h2><p>Try out the public endpoint at <a href="http://coronavirus.m.pipedream.net/"><code>https://coronavirus.m.pipedream.net/</code></a> or <a href="https://pipedream.com/@pravin/http-api-for-latest-jhu-csse-2019-ncov-wuhan-coronavirus-data-set-p_G6CLVM">copy, modify and run it yourself for free on Pipedream</a>. Please let us know if you have any feedback – you can join our public Slack at <a href="https://pipedream.com/community">https://pipedream.com/community</a>. And <a href="https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6">be sure to check out the great work the team at CSSE is doing</a>.</p>