How To Create Automatically Refreshing Functions In Google Sheets

Introduction

I recently needed to create a stock ticker-type integration in a Google Sheet where I could retrieve stock quotes every minute and update several cells. This required a little bit of digging to get it to work correctly and quickly. Here’s what I did to put together refreshing functions in Google Sheets.

Getting Started

First, let’s create a new Google Sheet, or perhaps open an existing Google Sheet. Next, we’re going to want to click on Extensions -> Apps Script. This will open the script editor for Google Sheets. If you’re unfamiliar with Apps Script, it may look a little daunting, but don’t worry, I’ll walk you through the entire process.

Under the Files section in the left navigation, you should see Code.gs, which is more than likely already selected for you. If your Code.gs already contains anything, feel free to delete it all if you just created your Sheet.

The Basic Flow

For this to work correctly were going to want to do two things. First, we’re going to need to create a function that does what we want and returns a value. I think that idea is pretty straight forward. The second thing we’re going to need is an updater function. Being that Google Sheets has no version of setTimeout() or setInterval() we’ll need to have a completely separate function that gets invoked externally by an interval.

But for now, let’s focus on getting the first part in there.

Creating The Function

The first thing to know about Google Sheets functions that are directly callable from within the cells themselves is that the function name needs to be completely upper cased. In the case of what I’m trying to achieve, I named mine STOCK_QUOTE(). So let’s build the function itself:

function STOCK_QUOTE(symbol) {
  if(!symbol){
    symbol = 'msft'
  }
  const response = UrlFetchApp.fetch(`https://finance.yahoo.com/quote/${symbol}`).getContentText();
  const $ = Cheerio.load(response);
  const quote = $(`fin-streamer[data-field=regularMarketPrice][data-test=qsp-price]`).text();
  return Number(quote);
}

The above basically does a screen scrape of the Yahoo Finance page based on a stock symbol and returns the value. But for those of you who want the blow by blow, here’s what’s going on.

The function takes in a symbol argument which should be pretty obvious, it’s the stock ticker symbol such as “msft”. The first if simply checks for non-argument calls and defaults to “msft” which allows for the function to be invoked by using the Debug button on the toolbar.

You may notice that if you try to run the function as is verbatim, that it won’t work. That’s actually due to my use of an external library called Cheerio. You can find more about that library at https://github.com/tani/cheeriogs.

If you’d like to incorporate the Cheerio library, click the + sign on under the Libraries section of the left navigation and enter 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0 as the Script ID and follow the steps through the wizard.

Next, I make an external web call using UrlFetchApp object and load that content into Cheerio as $. How very jQuery-ey or just jQuery of me? I don’t know, tomato…tomato. Hopefully, you heard the difference in your head as you read that.

Once I have the content available to query, sizzle syntax is used to grab the current price for the stock.

Once we have that done, we can test it in our sheet. For simplicity purposes, if this is your first time setting something like this up, it would probably be better to do something like the following to test:

function STOCK_QUOTE(symbol) {
    return `symbol: ${symbol}`;
}

This way you can verify the function works from the cell.

Calling From Sheets

Let’s go ahead and add our new function to our sheets. Select any cell you with to use this function with and add something similar to the following:

=STOCK_QUOTE(msft)

If everything works as it should you should either see Loading followed by the actual stock quote value, or the output from the more simple function above. Either way, progress!

Calling Every Minute

Now that we have our main function in play, we’re going to need a way to have it refresh itself, every minute in my particular case. To do this let’s define another function which will take on that responsibility.

function refresh() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const formula = "=STOCK_QUOTE";
  const re = sheet.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true);
  const formulas = [];
  
  const cells = re.findAll();
  for(var cell of cells){
    const f = cell.getFormula().toString();
    formulas.push(f);
  }

  re.replaceAllWith("0");

  SpreadsheetApp.flush();
  for(let i=0; i<cells.length; i++) {
    cells[i].setFormula(formulas[i]);
  }
}

Look a bit confusing? That’s ok, I’m going to walk through everything this does. First of all, notice the function name is lower cased. This is not going to need to be called from the Google Sheet itself. Instead, we will invoke this by using a trigger. More on that later, but for now, let’s understand what’s happening here.

First, the active sheet reference is retrieved. This is the sheet that currently has the focus.

Next, we define what we want to find as we look through the cells and if it matches the formula prefix, we know we want to update it.

We then create a text finder that looks to match a cell’s formula against our defined formula. In this case, it will be looking for the =STOCK_QUOTE prefix.

Next, we create an array for holding all the formulas as we find them in cells. This is part of an optimization approach. A little more on that later.

So, we begin searching the cells using re.findAll() that will regular expression match and find all cells starting with what we’ve defined in the formula. As each of these are found, we retrieve the formula value as a string and store it in our formulas array.

Once we have all the current formulas in a safe place (ie. the formulas array) we replace all the cell values with 0 and flush the spreadsheet. This call basically makes the change and submits the changes to be recorded by Google Sheets. This can be thought of as the actual moment the Sheet changes.

Lastly, we want to iterate over the cells once more applying the stored formulas from the formulas array.

The overall approach here is to pull the values out of the cells, flush the sheet, then stick new formulas back into the cells and have those evaluated rendering new values.

Setting Up The Trigger

To set up a trigger, mouse over the left of the Apps Script and find the alarm clock icon labeled Triggers.

Click on the Triggers navigation and find the + Add Trigger button in the bottom right.

You will then be presented with the Add Trigger dialog.

Change the Choose which function to run to be refresh. Change the Select event source to be Time-driven, Change the Select type of time based trigger to be Minutes timer and lastly change the Select minute interval to be Every minute. Click Save when you’re done.

We now have an interval which will call our function refresh() every minute. This will in turn find all the STOCK_QUOTE() functions we have defined in our Sheet cells and force them to update.

Conclusion

This took a bit of finagling to get to work correctly and in an optimized fashion. I hope this helps with your future Google Sheet endeavors and please feel free to ask any questions. I’ll do what I can to help. Thanks as always for stopping by.