COVIDNOW | What is Open Data? | Tips to ...

COVIDNOW | What is Open Data? | Tips to Import COVIDNOW Data to Google Sheets

Sep 17, 2021

The CovidNow website designed by Henry Lim, Calum Lim, Sheng Han Lim and Roshen Maghhan with the collaboration with our Ministry of Health and the open data community. View more about the developer background at https://covidnow.moh.gov.my/about/.

The new website covidnow.moh.gov.my is significantly better in terms of User Interface (UI) and User Experience (UX) when you compare it with the existing covid-19.moh.gov.my/terkini-negeri which no longer update since the launch for the CovidNow website.

So, what is Open Data and how useful or important is Open Data?

Open Data is data that can be freely use, publish or distribute by anyone. Open data should also be easy to access and understand which lead to most of the data is published with .csv or excel format.
One of the reason behind that Open Data might consume time and resources since not all data can be published directly or raw, as some of the sensitive data will need to keep confidentially, hence in government or organization, they will need to filter or structure the data before it can be publish openly for easier access or easy understandable.

The good about Open Data is that with the current technology, knowledge and Data Science, we can use the Open Data to get more insight or prediction like for example, how long will it takes before the ICUs being overcrowded or given the rate of vaccine today, how long will we achieve herd immunity in this case.
Of course, Open Data is not tie to only in health care, it can be in every sector such as government, economy, social, organization, transport, and everything else!

The Open Data will surely helps to improve our quality of life, increasing performance and efficiency of public services, contributes to ideas for developing new business models or innovation services, improving social welfare and more.

How to Extract/Import Data from COVIDNOW to Google Sheets?

To extract data such as the daily new cases or the rate of vaccination, you can either pull the data from CovidNow website - which is relatively easier or you can pull it from the source - Github.

To pull the data from Github, use the formula ImportHTML() with the Github URL and select table 1. For example, pulling the cases Malaysia data, use the formula below:
=Importhtml("https://github.com/MoH-Malaysia/covid19-public/blob/main/epidemic/cases_malaysia.csv","table",1)
*You may encounter not enough column error, just add more column to the right.

To extract or import the data via CovidNow website, you can use the formula ImportXML() or by using the UrlFetchApp.fetch() function inside Google App Script.

For ImportXML() formula, you will need to learn how to use the XPath Query and inspect the html element of the CovidNow website (Press Ctrl+Shift+I for Google Chrome to inspect element).
For example, if you wish to pull the daily new local cases data, use the formula: =index(IMPORTXML("https://covidnow.moh.gov.my/","//div[@class='grid gap-0 sm:gap-2']/div/div/div/div[@class='chip bg-gray-300 px-2 font-semibold']"),1,1)
The index() formula helps to select the correct data to display ignoring others after the ImportXML() formula that helps to extract the data from CovidNow website.
The XPath query can be understand as: div[@class='grid gap-0 sm:gap-2'] //look for the <div> tag that has the class attribute of grid gap-0 sm:gap-2. Then we are going to select the <div> tag that is nested inside by inputting /div. Finally we are going to select the <div> tag that has the class attribute of chip bg-gray-300 px-2 font-semibold.
As the result, you will get only the local new daily cases.

To access App Script in Google Sheets, go to Tools > Script Editor.
Then with the UrlFetchApp.fetch() function, you will need to copy down the sample below to the Script Editor and change yourself to suit your needs:

function getCOVIDNOW(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var res;
  
 var data = UrlFetchApp.fetch("https://covidnow.moh.gov.my/").getContentText();
    try {
    //Logger.log("data: "+data);
    var values = data.match(/<div class="number flex justify-center gap-1.5"><div tabindex="-1" class="relative"><span>[0-9, \n]+/)[0]; //get total local cases value only
    Logger.log("values: "+values);
    var num = values.indexOf("\n")+1; //check for newline
    Logger.log("num: "+num);
    res = values.substring(num,values.length); //remove all html
    res = res.match(/[0-9,]+/)[0]; //get numbers only
    Logger.log("res: "+res);
 
    } catch(e) {
      res ="invalid";
    }
 
    ss.getRange("C5").setValue(res); //set value at cell C5
}

After that, insert a button to run the App Script by clicking on Insert > Drawings. Create your button in Google Drawings and press Save. Click on the 3 dots on the top right of the drawing and click Assign a Script. Input the script function name, in the example is getCOVIDNOW . (If you have previously assign a script and would like to change the function, press Ctrl on your keyboard while clicking the button.)

We do also give out a copy of Google Sheets with sample formula of importing data from GitHub and also CovidNow website via various ways as stated above.

Copy the Google Sheets HERE. You can edit or view the script at Tools > Script Editor.

We hope this helps and share this posts if you think it could help more!

Follow us:
Telegram: t.me/cre8tivenow
Buy Me A Coffee (Click on Follow to get our updates directly send to your mailbox for free!)
Facebook: facebook.com/cre8tivenow
Instagram: instagram.com/cre8tivenow
Email: [email protected]

Enjoy this post?

Buy Creative Now Enterprise a coffee

More from Creative Now Enterprise