Recalculating custom functions in Google Sheets

I use Google Sheets to track my various stock investments. Sure, there are plenty of online tools that can do this for you, but I use Sheets because I like to develop my own metrics and see if they help me make smarter buy/sell decisions in the long run.

Having custom metrics means developing custom functions in Google Apps Script and then applying them inside Google Sheet cells. I use the great Yahoo API to fetch daily stock quotes and store them in a log sheet to save on API calls. So one example of a custom function is to pull the latest close price of a given stock from the log:

function LASTPRICE(ticker) {
  const headers = getQuotesHeaders();
  const lastPrices = getQuotesPrices().slice(-1).flat();
      
  const tickerIdx = headers.indexOf(ticker);
      
  if (tickerIdx === -1) {
    throw Error(`Company not recognized: ${ticker}`);
  }
  return lastPrices[tickerIdx];
}

In LASTPRICE, I pass in the company I'm interested in. I use some other functions to get the header row and price rows from the log sheet. I find the column index of the stock I'm interested in and I return the corresponding cell from the last row of the sheet since my API call appends daily quotes to the bottom of the sheet.

This works as expected, but there's just one problem with custom functions: they don't recalculate like regular Google Sheets functions do when you open or refresh a sheet. By design, custom functions recalculate only when their arguments change. But in my summary sheet I don't change them: I have a row per company and in LASTPRICE I simply refer to the ticker of the company listed in column A of that row. It's the underlying data in my log sheet that changes. I need LASTPRICE to recognize when new rows are added to the log and inspect the new last row.

I searched for solutions to this pesky problem. The consensus seemed to be that I needed to hack a workaround myself whereby I would pass in a second argument whose value I needed to change as often as I needed to have the function recalculate.

If you're thinking that this is lunacy then you're not alone.

So, we're to pass in a second argument that we have no intention to use in the function whatsoever, just so that Google Sheets sees the value change and bothers to recalculate the function? What kind of a world is this?

Whatever, I'll add in a second argument. But to protest my exacerbation with this forced hack I shall NOT declare it in my function signature! Thank you Javascript for letting me pass in arguments and completely ignore them in the function parameters!

Alright, so what's the fastest way to pass in a second argument that changes its value often enough so that the function always picks the last row from my log? I know, I'll just pass in the standard Google Sheet function NOW(), yay!

=LASTPRICE(A2, NOW())

End of story? Hardly! You try a stunt like that and Google Sheets is quick to chastise:

What? Come on, give me a break! This is, this is... I don't even know what this is. What ever happened to "Do no evil?"

Looks like I can't get away with it this easy. I need to construct a whole operation just to change the value of an argument I have no intention to use. My day is going on so nicely.

Take two:

function saveNewDate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config');
  const date = new Date();
  sheet.getRange('B1').setValue(date.toLocaleString('en-US'));
}

This is a simple function to update a cell value. Of course it requires me to create a new 'Config' tab to keep it separate from my summary data. I need to update my API function slightly to call this function whenever I fetch new data. No biggie.

Now I need to update all my custom functions to reference this new cell:

=LASTPRICE(A2, Config!$B$1)

This is ugly. I set up a named range for the cell, so can now use:

=LASTPRICE(A2, LastPull)

This will run when the API runs, but I can always have the custom functions recalculate by manually updating the config cell value. The functions will recalculate as soon as I update the cell, so that's nice.

This is not a robust solution. Every time I use a new custom function I have to remember to use the dummy field. But at least it gets the job done.

– Ben