Refresh data retrieved by a custom function in Google Sheet

I've written a custom Google Apps Script that will receive an id and fetch information from a web service (a price).

I use this script in a spreadsheet, and it works just fine. My problem is that these prices change, and my spreadsheet doesn't get updated.

How can I force it to re-run the script and update the cells (without manually going over each cell)?

Solution 1:

Ok, it seems like my problem was that google behaves in a weird way - it doesn't re-run the script as long as the script parameters are similar, it uses cached results from the previous runs. Hence it doesn't re-connect to the API and doesn't re-fetch the price, it simply returns the previous script result that was cached.

See more info here(Add a star to these issues, if you're affected):

https://issuetracker.google.com/issues/36753882
https://issuetracker.google.com/issues/36763858

and Henrique G. Abreu's answer

My solution was to add another parameter to my script, which I don't even use. Now, when you call the function with a parameter that is different than previous calls, it will have to rerun the script because the result for these parameters will not be in the cache.

So whenever I call the function, for the extra parameter I pass "$A$1". I also created a menu item called refresh, and when I run it, it puts the current date and time in A1, hence all the calls to the script with $A$1 as second parameter will have to recalculate. Here's some code from my script:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Refresh",
    functionName : "refreshLastUpdate"
  }];
  sheet.addMenu("Refresh", entries);
};

function refreshLastUpdate() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}

function getPrice(itemId, datetime) {
  var headers =
      {
        "method" : "get",
        "contentType" : "application/json",
        headers : {'Cache-Control' : 'max-age=0'}
      };

  var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers);
  var jsonObj = eval( '(' + jsonResponse + ')' );
  return jsonObj.Price;
  SpreadsheetApp.flush();
}

And when I want to put the price of item with ID 5 in a cell, I use the following formula:

=getPrice(5, $A$1)

When I want to refresh the prices, I simply click the "Refresh" -> "Refresh" menu item. Remember that you need to reload the spreadsheet after you change the onOpen() script.

Solution 2:

You're missing the fastidious caching ~~bug~~ feature. It works this way:

Google considers that all your custom functions depend only on their parameters values directly to return their result (you can optionally depend on other static data).

Given this prerequisite they can evaluate your functions only when a parameter changes. e.g.

Let's suppose we have the text "10" on cell B1, then on some other cell we type =myFunction(B1)

myFunction will be evaluated and its result retrieved. Then if you change cell B1 value to "35", custom will be re-evaluated as expected and the new result retrieved normally. Now, if you change cell B1 again to the original "10", there's no re-evaluation, the original result is retrieved immediately from cache.

So, when you use the sheet name as a parameter to fetch it dynamically and return the result, you're breaking the caching rule.

Unfortunately, you can't have custom functions without this amazing feature. So you'll have to either change it to receive the values directly, instead of the sheet name, or do not use a custom function. For example, you could have a parameter on your script telling where the summaries should go and have an onEdit update them whenever a total changes.

Solution 3:

What I did was similar to tbkn23. This method doesn't require any user action except making a change.

The function I want to re-evaluate has an extra unused parameter, $A$1. So the function call is

=myFunction(firstParam, $A$1)

But in the code the function signature is

function myFunction(firstParam)

Instead of having a Refresh function I've used the onEdit(e) function like this

function onEdit(e)
{
   SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}

This function is triggered whenever any cell in the spreadsheet is edited. So now you edit a cell, a random number is placed in A1, this refreshes the parameter list as tbkn23 suggested, causing the custom function to be re-evaluated.

Solution 4:

There are settings where you can make NOW() update automatically:

enter image description here

Solution 5:

If your custom function is inside a specific column, simply order your spreadsheet by that column.

The ordering action forces a refresh of the data, which invokes your custom function for all rows of that column at once.