Implementing Re-Calc Strategies in Google Sheets
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.
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 Google Sheet anytime by either pushing the Re-calc button or selecting “Trigger Recalc” from the Alpha Vantage Demo menu.
Try it!
