Google Spreadsheet Script not Updating Dynamic Inf

Question

So, i made a spreadsheet for Minecraft and 'Feed The Beast' a modpack for the game. My spreadsheet lists all the mods that are updated for the latest version of Minecraft. Recently i made a script to go out and parse pages for mod version numbers. They change hourly-daily and i rather not do it manually.

function GetVersion(url, slice, num1, num2) {
  var r = UrlFetchApp.fetch(url).getContentText();
  var version = r.slice(r.indexOf(slice)).slice(num1, num2);

  return version;
}

In one of the cells Ill have the following

 =GetVersion("http://files.minecraftforge.net", "Build 7.", 12, 15)

Now this works fine and gets me the version number im looking for. The problem is, when an update happens and a new version comes out, the parser doesn't reflect this even if i close the window and reopen the spreadsheet or reload or whatever else! If I change the above slightly, like change the 15 to 16, it will refresh, but then will be stuck there again until i manually change it again.

How do I get it so it at least refreshed when i reload the sheet?

Edit: Alright first I tried to go into reosources and triggers and make a trigger very min and everytime the doc is opened. This didn work..............

Then I tried to get clever. Noticing that the formula reevaluates whenever I change it, i surmised that the formula itself or the cell parameters needs to change in order for this to happen. So I pass a dummy parameter though in the formula and change that parameter to update the cell.

But that's annoying and make me have to edit (or press a button) just to get shit to refresh.

So i got a brainwave and Im now passing GoogleClock() in the dummy parameter. All cells now update on their own every 60 seconds. yay

Is there a better way to do this?

Answer 1

Aside from the workaround of passing GoogleClock() as a parameter (which I agree is the best you are going to do with a custom function), the other option is to do away with a custom function, and use GAS to write the result directly to a cell, eg:

SpreadsheetApp.getActiveSpreadsheet().getRange('Sheet1!A1').setValue(version);

Then in Resources, Current project's triggers, you can set both an "on edit" and time-driven trigger (to be clear: these triggers won't work on a custom function called from a spreadsheet cell; all the "work" must be done by the script itself).

Answer 2

Using googleclock as a param is the best u can do if u want to use a custom cel formula. Instead call an update function from a menu item and/or onOpen / time trigger.