2

I am using importxml to extract prices from multiple websites. My main concern is to be able to refresh the prices whenever I want and not be dependent on Google's hourly update. I am using this script to append a timestamp at the end of the URL so that Google is forced to re-fetch each time.

function onOpen() {
    var e = SpreadsheetApp.getActiveSpreadsheet();
    var t = [{
        name: "Refresh This Sheet",
        functionName: "Refresh"
    }, {
        name: "Refresh All Sheets",
        functionName: "RefreshAll"
    }];
    e.addMenu("Looker", t)
}

function onInstall(e) {
    onOpen(e)
}

function Refresh(e) {
    var t = SpreadsheetApp.getActiveSheet();
    if (typeof e !== "undefined") {
        t = e
    }
    var n = t.getDataRange();
    var r = find("importxml", n);
    for (var i = 0; i < r.length; i++) {
        r[i].setValue(r[i].getFormula().replace(/"[^"]"/, "?apply_formatting=true&refresh=" + Date.now() + '","'))
    }
}

function RefreshAll() {
    var e = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var t = 0; t < e.length; t++) {
        Refresh(e[t])
    }
}

function find(e, t) {
    var n = t.getFormulas();
    var r = [];
    for (var i = 0; i < n.length; i++) {
        for (var s = 0; s < n[i].length; s++) {
            if (n[i][s].indexOf(e) > -1) {
                r.push(t.getCell(i + 1, s + 1))
            }
        }
    }
    return r
}

I am using the function refresh to update the values, but the problem is it's not replacing the timestamp at the end of the URL; instead it creates a unique timestamp and appends it to the URL each time.

What am I missing here? I believe I am not using the operators in replace correctly.

replace(/"[^"]"/,"?apply_formatting=true&refresh="+Date.now()+'","'
  • 1
    Another useful tool is jsbeautifier.org -- I used it to improve the presentation of your post. – user79865 Feb 28 '16 at 22:06
1

Issues

One problem with your script is that the regular expression /"[^"]"/ matches only a single character between double quote marks. To replace more characters between double quotes, use /"[^"]*"/ instead. The site regex101 is very helpful for debugging regular expressions.

Also, I don't quite see the logic of this replacement: to me it seems that it would kill the hostname of the website, not just change the parameter. Finally, I don't see why the updated formula is set with .setValue instead of setFormula.

Solution

Here is a replacement that works. Suppose this is a formula in your spreadsheet that you want to update, say in cell F2.

=importxml("http://example.com?apply_formatting=true&refresh=1234567890987", "//div[42]")

Here is a function that updates it:

function update() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange("F2");
  cell.setFormula(cell.getFormula().replace(/\?[^"]*"/, '?apply_formatting=true&refresh='+Date.now()+'"'));
}
  • Great...but this will only work if the url has a ? in the end. What would be the regex so that it adds the timestamp right before closing double qoutes – user1314051 Mar 1 '16 at 17:12
  • You can always put ? at the end of any URL, it doesn't hurt. – user79865 Mar 1 '16 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.