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

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

Answer 2

In addition of Vikramaditya Gaonkar answer, you can use a installable trigger to get a refresh result each minute.

function getBTCPrice(input) {  

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

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

  return price

} 

function up(){

  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());

}

The parameter of getBTCPrice function is, in my case, cell A1 which is randomize each minute. For this, I create a installable trigger on up function

function up, time-driven, minute timer, every minute

Answer 3

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 4

I was also trying to make my custom function update, after searching I came up with the following function:

function updateFormulas() {
  range = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  formulas = range.getFormulas();
  range.clear();
  SpreadsheetApp.flush();
  range.setValues(formulas);
}

The function above update all formulas of the spreadsheet. In my experience to make a custom function update I had to change its value, so I get all the data of the sheet, then I get the formulas and store them into a variable, then I clear their values and apply this change with "flush", finally I update the values I have just cleared with the formulas I have stored.

I created this function and in my case I have set the trigger for 1 minute to execute it, every minute all functions of the table are updated.

I hope this helps you.