# Implementing Re-Calc Strategies in Google Sheets

Sep 9, 2019 · 4 min read

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 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.”

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

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!

Written by

Written by