Efrem Sternbach
Sep 9 · 4 min read

The examples in this post require the Alpha Vantage Market Data Add-on to run.

Here we will describe an oddity of Google Sheets and how to work around it. Specifically, there is no way to re-calc a spreadsheet under Google Sheets. F2 Enter doesn’t work. No F9 function key to re-calc. Don’t bother to retype an argument since if the value doesn’t change neither does the function result. To illustrate, let’s look at an intraday series from Alpha Vantage

This is a series with an interval of 1 minute. Surely, I’d like to be able to refresh this during the day. Right now, the only way is to change one of the arguments to something different and then change it back. Pretty inconvenient, right?

Well, our workaround to this is to put a dummy argument into the Alpha Vantage functions to force a re-calc. This argument is always the last one in a function. For our example the function help looks like:

To implement a re-calc function, we’ll first select a cell to hold the re-calc trigger. For convenience we’ll call that cell RECALC_TRIGGER.

Next, we need to create a function to increment the trigger. We open the script editor

We’ll use it to create our function

It is important to note that the first time this function runs you will be asked to approve its permission to do so. This is not a big deal as you are just giving permission for your function to modify your spreadsheet.

For the purposes of this tutorial we’ll show how to create two convenient ways to call this function. The first way is to create a menu with an entry to call the function. We’ll add the following code to the top of Code.gs

This function will add a menu named Alpha Vantage Demo with an item named “Trigger Recalc.”

You’ll have to refresh your spreadsheet in the browser for this menu to appear.

We can also create a button. Go to Insert->Drawing in the spreadsheet menu and draw a button. Then save your work and drag it to the part of the spreadsheet where you want it. Right click the mouse to edit the button. You will see three dots in the upper right corner of the button which is where the menu is hiding

The menu looks like

Select Assign script and type in “incrementTrigger”

Now go back to your original function and add the re-calc trigger.

Here we’ve left out the argument for opt_outputsize since we’re fine with the default values.

Now you can update your intraday series anytime by either pushing the Re-calc button or selecting “Trigger Recalc” from the Alpha Vantage Demo menu.

Try it!

Alpha Vantage

Accessible and easy to use APIs for financial market data

Efrem Sternbach

Written by

Alpha Vantage

Accessible and easy to use APIs for financial market data

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade