[Follow-up] A cleaner way to set the virus outbreak tracker
This is a brief follow-up for the previous series to log updating online data, and notify with email upon changes using Google Sheets and Google Apps Script.
To separate the discussion of different functionalities into readable lengths, there were some redundancy in the previous method.
But we can make things more streamlined in practice. Instead of logging per time interval and compare the latest two records, we can write a new record only when the last one differs from the most updated one. So, here is the new script.
var id = "1z1_W00OHKie8oPuCz7qIb10FeBFlZOTgY8_JtLMNQcM";function refresh() {
var ss = SpreadsheetApp.openById(id);
var updatesheet = ss.getSheetByName('updating'); // where the IMPORTXML function locates
var r = updatesheet.getRange("C3").getValue();
updatesheet.getRange("D3").setValue(r);
}function promptUpdate() {
var ss = SpreadsheetApp.openById(id);
var logsheet = ss.getSheetByName('log'); // where to store the data
var updatesheet = ss.getSheetByName('updating'); // where the IMPORTXML function locates
var lastrow = logsheet.getLastRow();
Logger.log(lastrow);
var past = logsheet.getRange(lastrow, 3, 1, 4).clearFormat().getValues()[0];
//var update = updatesheet.getRange("A2:E2").clearFormat().getValues()[0];
var update = ss.getSheetByName('dummy').getRange("A2:E2").getValues()[0];var now = Utilities.formatDate(new Date(), "GMT+8", "MM-dd-yyyy HH:mm:ss");
Logger.log(now)
if ((update[1] != past[0]) || (update[2] != past[1]) || (update[3] != past[2]) || (update[4] != past[3])) {
var emailAddress = "your@email.com";
var subject = Date() + " 2019-nCoV latest case numbers in China";
var message = "Last update: "+ update[0] + "; Confirmed: " + update[1] + "; Suspected: " + update[2] + "; Recovered: " + update[3] + "; Death: " + update[4];
GmailApp.sendEmail(emailAddress, subject, message);
logsheet.getRange(lastrow+1, 1, 1, 6).setValues([[now].concat(update)]);
}
logsheet.getRange("H2").setValue(lastrow);
logsheet.getRange("H4").setValue(now);
}
The demo sheet is here: https://docs.google.com/spreadsheets/d/1z1_W00OHKie8oPuCz7qIb10FeBFlZOTgY8_JtLMNQcM/
Also note that there is a daily total runtime limit of 90 minutes for normal Gmail accounts, and 6 hours for G Suite users, so we need to tune down the updating frequency. G Suite provides a 14-day free trial, so I gave it a try.
I won’t go into details about playing around with the G Suite dashboard and other controls here, but would like to remind of subtle changes when moving to G Suite, e.g. MailApp is GmailApp in G Suite.
Good health and see you next time.
