Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Script to force update IMPORTXML cell function and populate into new row at regular time intervals.
// by u/NeutronStarsAreHeavy for Revoke Article 50 Petition Tracker.
// Function to check time, so that the save data function executes at specified times. Triggered every minute (set-up in GSuite Developer Hub)
function checkTime() {
var now = new Date();
var minute = now.getMinutes();
if (minute == 00 || minute == 15 || minute == 30 || minute == 45) {
// execute saveData
saveData();
}
// terminate
else {
return;
}
}
// Function to append IMPORTXML in cell(1,2) to force update. Inserts row before row(3) to populate with updated data. Calculates and populates other quanities of interest.
function saveData() {
var id = "1rgpDCb5Hsm2XGagN69TXpM-LW8A0gwD4xrozYgBjKDw";
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("DATA");
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var content = "";
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
var re2 = /((\?|&)(update=[0-9]*))/gi;
var re3 = /(",)/gi;
var errors = 0;
// insert new row (row3)
sheet.insertRowsBefore(3,1);
// add timestamp
var timeZone = Session.getScriptTimeZone();
var timestamp = Utilities.formatDate(new Date(),timeZone,"yyyy-MM-dd HH:mm");
sheet.getRange(3,1).setValue(timestamp);
// force IMPORTXML call
do {
var now = new Date();
var time = now.getTime();
content = formulas[0][1];
if (content != "") {
var match = content.search(re);
if (match !== -1) {
var updatedContent = content.toString().replace(re2,"$2update=" + time);
if (updatedContent == content) {
updatedContent = content.toString().replace(re3,"?update=" + time + "$1");
}
sheet.getRange(1,2).setFormula(updatedContent);
}
}
var PetitionCount = sheet.getRange(1,2).getValue();
if (isNaN(PetitionCount)) {
Utilities.sleep(500);
errors++;
}
else {
break;
}
}
while (errors!=0);
// populate new row with updated IMPORTXML call
sheet.getRange(3,2).setValue([[PetitionCount]]);
// calculate and populate petition count change
var PetitionCountChange = PetitionCount - sheet.getRange(4,2).getValue();
sheet.getRange(3,3).setValue([[PetitionCountChange]]);
// calculate and populate percentage of Leave votes
var percentLeaveVoters = PetitionCount / 17410742;
sheet.getRange(3,4).setValue([[percentLeaveVoters]]);
// error messages
if (PetitionCountChange == 0) {
sheet.getRange(3,5).setValue("SOURCE NOT UPDATED");
}
if (sheet.getRange(3,2).isBlank()) {
sheet.getRange(3,5).setValue("SOURCE UNAVAILABLE");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment