0

I am using a function named getStockQuote() in Google Sheets.

=getStockQuote()

The getStockQuote function is pretty simple.

function getStockQuote() {
var url = 'SOME STOCK API SERVER';
var options = {
  'method' : 'post',
  'contentType': 'application/json',
  headers: {
      "Cache-Control": "no-cache"
    },
  // Convert the JavaScript object to a JSON string.
  'payload' : JSON.stringify({"columns":["QUOTE"],"ignore_unknown_fields":false,"symbols":{"query":{"types":[]},"tickers":["SPY"]}})
}

var response = UrlFetchApp.fetch(url, options);
return JSON.parse(response).data[0].d[0];
}

The above code works and fetches the Stock value.

However, I need the updated value of this function in my Google Sheets every 5 minutes. Currently, it only runs the function once and the value doesn't get updated.

I tried time-based triggers but still not able to retrieve updated value in Google Sheets every 5 mins.

kensplanet
  • 493
  • 8
  • 15
  • How did you utilize the time based trigger? Please share the function that you called with the timebased trigger. – Cooper Aug 23 '23 at 16:13
  • I just created a time-based trigger that runs the above function `getStockQuote ` every 5 mins. – kensplanet Aug 23 '23 at 17:36
  • The problem with that is that the above function returns to the trigger which can't do anything with it – Cooper Aug 23 '23 at 17:38
  • You will have to rewrite the function and send the ouput to a cell in the spreadsheet or a range of cells. – Cooper Aug 23 '23 at 17:41
  • I tried using `var cell = SpreadsheetApp.getActiveRange(); cell.setValue("STOCK VALUE")`. This gives an error in sheet saying, "You do not have permission to call setValue". – kensplanet Aug 23 '23 at 19:25
  • 1
    Custom functions are limited to [these services](https://developers.google.com/apps-script/guides/sheets/functions#advanced) so the function I'm suggesting that you write cannot be used as a Custom Function. That is to say that you cannot use it as a cell function. It's just a function that you can call from a menu or run from the script editor – Cooper Aug 23 '23 at 19:36
  • Might be instead of using it as a custom function, create it as an apps script function instead to pull the url on a cell value using [getRange()](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangea1notation) and [getValue()](https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues), and use [setValue()](https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluevalue) on a different cell and assign it to a time-based trigger. – Century Tuna Aug 23 '23 at 19:59
  • Although I'm not sure whether I could correctly understand your question, is this thread useful for your situation? https://stackoverflow.com/a/58579535 – Tanaike Aug 24 '23 at 01:07
  • So, in simple words I want my function `getStockQuote()` to behave exactly like `GOOGLEFINANCE()` or `NOW()` function, which refreshes frequently inside Google Sheets. In my case, it's 5 mins. – kensplanet Aug 24 '23 at 13:51

1 Answers1

0

I'm not sure, but the cells themselves probably will not update as you want. I would simply refresh the page in your case. For a triggered script, you should set the target cells to be updated.

Daniel Collier
  • 36
  • 2