How to Track Coronavirus (COVID-19) Cases in India with Google Sheets

by
Advertisements

The Coronavirus (COVID-19) tracker visualises the progress of the coronavirus epidemic across several states in India over time using sparklines in Google Sheets.

The Government of India website features a live dashboard that displays the number of Coronavirus (COVID-19) cases in various Indian states in near real time. This is the best place to stay up to date on ongoing COVID-19 cases in India.

COVID-19 Tracker for India

The official website gives current statistics, but there is no historical data available to show how the number of verified cases rose in India over time. That’s one of the reasons I created the COVID-19 Tracker in Google Sheets.

Advertisements

The tracker takes data from the official website every few minutes and utilises Sparklines to show how the coronavirus epidemic in India has expanded over time. Since March 10, the government has been actively producing reports, and all data may be viewed via Google Sheets.

Covid-19 India tracker

test it

JSON API for COVID-19

If you are a developer, I have also exposed the data as a JSON API that will give you with the most recent state-by-state statistics about COVID-19 cases as accessible on the Ministry of Health and Family Welfare of India’s website. Also Learn How to Bundle Create React App in a Single File

Advertisements

The COVID-19 Tracker’s Operation

The Coronavirus Tracker is created in Google Apps Script and scrapes numbers from the mohfw.gov.in website every several minutes using time-based triggers.

/**
 * Scrape the homepage of mohfw.gov.in (Ministry of Health, India)
 * website for latest numbers on Coronavirus positive cases in India
 */
const scrapeMOHWebsite = () => {
  const url = 'https://www.mohfw.gov.in/';
  const response = UrlFetchApp.fetch(url);
  const content = response.getContentText();
  return content.replace(/[\r\n]/g, '');
};

Because Google Apps Script does not allow HTML parsers like Cheerio, we had to create one from scratch using regex. It captures the page’s HTML text, looks for the table tag, and then pulls data from individual table cells.

This parser is likely to break if they modify the layout of the website.

Advertisements 
/**
 * Parse the webpage content and extract numbers from the HTML
 * table that contains statewise data on Covid-19 Cases in India
 */
const getCurrentCovid19Cases = (json = true) => {
  const states = {};
  const html = scrapeMOHWebsite();
  const [table] = html.match(/<div id="cases".+?>(.+)<\/div>/);
  const rows = table.match(/<tr>(.+?)<\/tr>/g);
  rows.forEach((row) => {
    const cells = row.match(/<td.+?>(.+?)<\/td>/g).map((cell) => cell.replace(/<.+?>/g, ''));
    const [, stateName, indianNationals, foreignNationals] = cells;
    if (/[a-z\s]/i.test(stateName)) {
      states[stateName] = Number(indianNationals) + Number(foreignNationals);
    }
  });
  return json ? states : JSON.stringify(states);
};

We can easily write to a Google Spreadsheet using Apps Script after we get the data in JSON format. The script creates a new column every day while keeping the previous data for comparison.

/**
 * Write the parsed data into a new column in Google Sheet
 * All the historic data is also preserved in the sheet.
 */
const writeNewCovid19CasesToSheets = (covid19Cases) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  const states = sheet
    .getRange(3, 1, sheet.getLastRow() - 2, 1)
    .getValues()
    .map(([state]) => [covid19Cases[state] || 0]);
  sheet
    .getRange(2, sheet.getLastColumn() + 1, states.length + 1, 1)
    .setValues([[new Date()], ...states.map((count) => [count])]);
};

The COVID-19 tracker in Google Sheets now has a JSON API for importing data straight into your apps and websites.

We published the script as a web app with the doGet callback method to publish a JSON API. When an external app calls the Google script URL, the ContentService service delivers the raw JSON result.

Advertisements 
const doGet = () => {
  const key = 'Covid19India';
  const cache = CacheService.getScriptCache();
  let data = cache.get(key);
  if (data === null) {
    data = getCurrentCovid19Cases(false);
    cache.put(key, data, 21600);
  }
  return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.JSON);
};

All the code is open-source and you are free to use in any project.

Leave a Comment