Refresh data Retrieved by a Custom Function in Google Spreadsheet

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)?

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)

 

I know this is a bit of an old question. But this method doesn’t require any user action except making a change.

What I did was similar to tbkn23.

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.

Written By:

Sarmad Gardezi A blog scientist by Mind and a Passionate Blogger by heart. Sarmad is Freelancer, Entrepreneur and Google Apps Scripts Developer from Islamabad, Pakistan.

Subscribe to Email Newsletter