{"id":1170,"date":"2023-05-15T03:23:49","date_gmt":"2023-05-15T03:23:49","guid":{"rendered":"https:\/\/shop.cre8tivenow.com\/?p=1170"},"modified":"2024-02-18T13:29:26","modified_gmt":"2024-02-18T13:29:26","slug":"what-is-open-data-how-useful-is-it-tips-to-import-covidnow-data-to-google-sheets","status":"publish","type":"post","link":"https:\/\/shop.cre8tivenow.com\/zh\/what-is-open-data-how-useful-is-it-tips-to-import-covidnow-data-to-google-sheets\/","title":{"rendered":"\u4ec0\u4e48\u662f\u5f00\u653e\u6570\u636e\u53ca\u597d\u5904\uff1f | \u6559\u4f60\u5982\u4f55COVIDNOW \u6570\u636e\u5bfc\u5165 Google \u8868\u683c"},"content":{"rendered":"\n

The CovidNow website is designed by Henry Lim, Calum Lim, Sheng Han Lim, and Roshen Maghhan to collaborate with our Ministry of Health and the open data community. View more about the developer’s background at https:\/\/covidnow.moh.gov.my\/about\/<\/a>.<\/p>\n\n\n\n

The new website covidnow.moh.gov.my<\/a> is significantly better in terms of User Interface (UI) and User Experience (UX) compared with the existing covid-19.moh.gov.my\/terkini-negeri, which has no longer been updated since the launch of the CovidNow<\/a> website.<\/p>\n\n\n\n

So, what is Open Data and how useful or important is Open Data?<\/h2>\n\n\n\n

Open Data is data that can be freely used, published, or distributed by anyone. Open data should also be easy to access and understand, leading to most data being published in .csv or excel format.
One of the reasons is that Open Data consumes a lot of time and resources. Since not all data can be published directly or raw, some sensitive data will need to be kept confidential. Hence, governments or organizations will need to filter or structure the data before being published openly.<\/p>\n\n\n\n

The good about Open Data is that with the current technology, knowledge, and Data Science, we can use Open Data to get more insight or prediction. For example, how long will it take before the ICUs are overcrowded, given the rate of vaccine today? Or how long will we achieve herd immunity?
Of course, Open Data is not tied to only health care; it can be in every sector such as government, economy, society, organization, transport, and everything else!<\/p>\n\n\n\n

The Open Data will surely help improve our quality of life, increase the performance and efficiency of public services, contribute to ideas for developing new business models or innovation services, improve social welfare, and more.<\/p>\n\n\n\n

How to Extract\/Import Data from COVIDNOW to Google Sheets?<\/h2>\n\n\n\n

To extract data such as the daily new cases or the vaccination rate, you can pull the data from the CovidNow<\/a> website, which is relatively more straightforward, or directly from the source, Github.<\/p>\n\n\n\n

To pull the data from Github, use the formula ImportHTML() with the Github URL and select Table 1. For example, when 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)
<\/strong>*You may encounter not enough column errors; just add more columns to the right.<\/p>\n\n\n\n

To extract or import the data via the CovidNow<\/a> website, you can use the formula ImportXML() or the UrlFetchApp.fetch() function inside Google App Script.<\/p>\n\n\n\n

For the ImportXML() formula, you will need to learn how to use the XPath Query and inspect the HTML element of the CovidNow<\/a> website (Press Ctrl+Shift+I for Google Chrome to inspect the 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)<\/strong>

The index() formula helps select the correct data to display, ignoring others after the ImportXML() formula that helps extract the data from the CovidNow website.<\/p>\n\n\n\n

The XPath query can be understood as div[@class=’grid gap-0 sm:gap-2′] <\/strong> 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 a result, you will get only the local new daily cases.<\/p>\n\n\n\n

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:<\/p>\n\n\n\n

function getCOVIDNOW(){\n  var ss = SpreadsheetApp.getActiveSpreadsheet();\n  var res;\n  \n var data = UrlFetchApp.fetch(\"https:\/\/covidnow.moh.gov.my\/\").getContentText();\n    try {\n    \/\/Logger.log(\"data: \"+data);\n    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\n    Logger.log(\"values: \"+values);\n    var num = values.indexOf(\"\\n\")+1; \/\/check for newline\n    Logger.log(\"num: \"+num);\n    res = values.substring(num,values.length); \/\/remove all html\n    res = res.match(\/[0-9,]+\/)[0]; \/\/get numbers only\n    Logger.log(\"res: \"+res);\n \n    } catch(e) {\n      res =\"invalid\";\n    }\n \n    ss.getRange(\"C5\").setValue(res); \/\/set value at cell C5\n}<\/code><\/pre>\n\n\n\n
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, for example,  getCOVIDNOW<\/strong>. (If you have previously assigned a script and would like to change the function, press Ctrl on your keyboard while clicking the button.)<\/p>\n\n\n\n
We also give out a copy of Google Sheets with a sample formula for importing data from GitHub and the CovidNow<\/a> website via various ways, as stated above.<\/p>\n\n\n\n
Copy the Google Sheets HERE<\/a>. You can edit or view the script at Tools > Script Editor.<\/p>\n\n\n\n
We hope this helps, and share this post if you think it could help more!<\/p>\n\n\n\n