Google sheet not updating custom function return value

Question

I am very new to Google Apps Script (as well as JavaScript, for that matter), but I have been trying to tinker with it for fun.

I have tried writing a script to fetch API price data in Google Sheets, but am finding that the returned value is not updating when re-evaluating the script in the same cell.

Below is a script to fetch bitcoin price data from Coinbase's API. The script parses the JSON response of the request, as is described here.

function getBTCPrice() {
  var url = "https://api.coinbase.com/v2/prices/BTC-USD/spot";
  var response = UrlFetchApp.fetch(url);

  var jsonSpotPrice = response.getContentText();
  var parseSpotPrice = JSON.parse(jsonSpotPrice);
  var price = "$" + parseSpotPrice.data.amount;

  return price
}  

Now, if I type =getBTCPrice() in some cell, and then re-evaluate a few moments later, I get the same price; however, if I evaluate the script in a different cell, I get a different result.

I've read some stuff about Google caching values in cells, so that perhaps the script isn't evaluated because the value of the cell has not changed. Is this the case here? If so, is there a workaround?

Any help is greatly appreciated!

Answer 1

I finally figured it out! Instead of trying to call the custom function from an actual sheet cell (which apparently stores cached values), the trick is to call the function within a script.

Using my above script:

function getBTCPrice(url) {
  var response = UrlFetchApp.fetch(url);

  var jsonSpotPrice = response.getContentText();
  var parseSpotPrice = JSON.parse(jsonSpotPrice);
  var price = "$" + parseSpotPrice.data.amount;

  return price; 
}

You can then call this function from another script. Specifically, I was looking to assign the updated price to a cell. Below is an example, which assigns the price to the active spreadsheet, in cell A1:

function updatePrice(){
    var a = getBTCPrice("https://api.coinbase.com/v2/prices/BTC-USD/spot");
    SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(a);
}

You can then proceed to set an appropriate time trigger. And that's all there is to it!

Answer 2

Have a look at this answer on Refresh data retrieved by a custom function in google spreadsheet.

As the answerer says, the trick is to

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.

Vik