Implementing Re-Calc Strategies in Google Sheets

Efrem Sternbach
Sep 9, 2019 · 4 min read
Image for post
Image for post

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

Image for post
Image for post

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:

Image for post
Image for post

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.

Image for post
Image for post

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

Image for post
Image for post

We’ll use it to create our function

Image for post
Image for post

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 function to modify 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.”

Image for post
Image for post

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

Image for post
Image for post

The menu looks like

Image for post
Image for post

Select Assign script and type in “incrementTrigger”

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

Image for post
Image for post

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

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store