Get updated with 2019-nCoV (or any) real-time statistics with Google Sheets and Apps Script [3/3]
Step 3: Send email upon changes
Finally, after importing and logging data retrieved online, as not to pointlessly stare at the updating spreadsheet, you might want to be notified only when the numbers change.
Let’s write another function for it.
We will compare the past values on the last row of log with the updating ones, and email the updated data if they are different. Very surprisingly, I have tested that direct comparison of the two arrays like update != past will always return as unequal, so we compare the array elements instead.
var id = "xxx"; // Copy spreadsheet ID from URL https://docs.google.com/spreadsheets/d/xxx/edit#gid=569646613function emailNotify() {
var ss = SpreadsheetApp.OpenById(id);
var logsheet = ss.getSheetByName('log');
var lastheight = logsheet.getRange("H2").getValue();
var height = logsheet.getLastRow();
var past = logsheet.getRange(lastheight, 3, 1, 4).clearFormat().getValues()[0];
var update = logsheet.getRange(height, 3, 1, 4).clearFormat().getValues()[0];
//var update = ss.getSheetByName('dummy').getRange("B2:E2").getValues()[0]; if ((update[0] != past[0]) || (update[1] != past[1]) || (update[2] != past[2]) || (update[3] != past[3]) || (update[4] != past[4])) {
var emailAddress = "your@email.com";
var message = "Last update: "+ update[0] + "; Confirmed: " + update[1] + "; Suspected: " + update[2] + "; Recovered: " + update[3] + "; Death: " + update[4];
var subject = Date() + " 2019-nCoV latest case numbers in China";
MailApp.sendEmail(emailAddress, subject, message);
}
lastheight = height;
logsheet.getRange("H2").setValue(lastheight);
}
We will set a trigger again as in Step 2. Since we don’t know when the data will be updated, for demo, we copy the “updating” sheet to “dummy”, change the values, and run the emailNotify function for the positive test. In the test, we replace line 5 and set the update variable with the following line instead.
var update = ss.getSheetByName(‘dummy’).getRange(“B2:E2”).getValues()[0];
Run the function to test, and receive the email notification from your hard work!
Bravo! Now you, and maybe your friends who want to stay absolutely close to the news can get notified for any new numbers right away. (Or better still, adjust a trigger frequency comfortable to you~)
Also, if you don’t need the past values and don’t want an ever expanding file, in the last command in Step 2, you will fill on row at height instead of height+1. (You don’t need to open a new sheet as well. This is left for exercise 😼 The debug function is always your friend.)
The code and demo spreadsheet are available at https://github.com/ccneko/ncov-noti/tree/master and https://docs.google.com/spreadsheets/d/1Z1vM6TrL_faQszelblELX8RE_wUMUoHB497WKV9eJKs respectively. Please feel free.
Final remarks
This tutorial serves as a quick demo for basic web-scraping with minimal coding, spun up from insomnia after a cup of red tea at dinner. Compared to more hardcore methods, you don’t need to code for the web connection or browser client part, as well as the set up dataframes (tables) by yourself. You can basically do the same for anything you’re interested, from NBA results to view rate of your favourite article or song. The more niched the content is, the more practical it can be when there are not existing solutions suitable.
Web-scraping is a systematic way to record target information or gather a large amount of relevant data from online without tediously repetitive or infeasible manual effort. As news is not structured, the simple numbers cannot replace keeping track of news from various reliable sources for the detailed picture.
Meanwhile, along with the high awareness needed for the disease, it is also important to stay calm while discrete. A mind at peace helps maintain a strong body to ward off diseases. (Okay, so don’t stay up like me last night =w=) Well then, notifying yourself every other minute or scrolling the phone all day are probably no better idea than changing masks and washing hands frequently.
As a bioinformatician, if I have enough time and mood 😜, I can share more about the bioinformatic basics, such as the concepts of “gene”, “genome”, “sequence”, “sequencing” and even “phylogenetics”, etc. you might have heard recently. Let me know if you’re interested 😉
Stay healthy and be blessed!
References
[5] Tutorial: Sending emails from a Spreadsheet
https://developers.google.com/apps-script/articles/sending_emails
[6] App Script: Comparing 2 arrays — returns always unequal?
https://support.google.com/docs/forum/AAAABuH1jm0aDCWFHhhIpk/?hl=en
Please clap here as well if you like my post :)
Disclaimer: The article only represents personal ideas. This disclaimer informs readers that the views, thoughts, and opinions expressed in the text belong solely to the author, and not necessarily to the author’s employer, organization, committee or other group or individual.
