Refresh Tool
One of the challenges of working with spreadsheets and data that can get stale quickly is how to refresh your spreadsheet. Doing a Google search for how to refresh your spreadsheet shows just how much of a pain it is. Common attempts to address this issue include manually deleting data and undoing it, hacking together custom functions that include time functions, or creating your own scripts to run periodically. All of these solutions either don’t work, require a lot of manual work, or require a knowledge of coding and working with Google Scripts.
That’s why we created our Refresh Tool. The Refresh Tool is a completely free, open source tool to help you automatically refresh you Google sheets so that you get the latest data when you want it. It works out of the box with the Sheets Market Data add-on.
Why is it not included in the add-on?
This tool was originally intended to be apart of our add-on but Google places restrictions on add-ons using time-based triggers that are much stricter than for regular users. Therefore, instead of releasing a feature that we didn’t think would make our customers happy, we decided to open source it so that they could get the best experience possible.
How to Install
Heads Up!
All the code for this tool is located on Github.
The first step is to open up the sheet you want to use the Refresh Tool in. Next, click the menu Tools > Script editor.
This will open up a new window with an empty Google Script editor view.
Delete all of the existing code in the editor. Then copy and paste the code located in the Code.js file in the Github repository into the blank editor. Save the file.
It will ask you for a name for this project, choose whatever name you want.
Next, go to File > New > HTML file.
It will then ask you to enter a new file name. Important, set the file name as RefreshRate (leave off the .html extension).
This will open a new html file with some code in it. Delete all the code in this file. Then open up the file in the Github repository called RefreshRate.html and copy and paste all of the code in this file into the empty html file in the Google Script editor. Save this file.
You are now ready to use the Refresh Tool!
How to Use
After installing the Refresh Tool for the first time, you’ll have to reload your spreadsheet’s browser page. If everything was installed properly, you should now see a new menu in your spreadsheet called Sheets Market Data Refresh Tool.
To use the tool, select the menu Sheets Market Data Refresh Tool > Set Refresh Rate.
Heads Up!
If this is the first time setting it up, Google sheets will ask for your permission to run the script.
Click Continue, then select your user account, and allow the tool access.
A side bar menu should now open up on the righthand side of your sheet.
This sidebar menu allows you to set the refresh rate you want (1 minute, 5 minutes, 15 minutes, 30 minutes, or 1 hour) and a schedule for what hours to run the refreshes.
Once you’ve set your desired refresh rate and schedule, the Sheets Market Data add-on functions in your sheet will be automatically refreshed accordingly!
Limitations
Please be aware that there are limitations and quotas for Google scripts and add-ons. The ones of note for this particular tool are the URL fetch calls and Triggers total runtime. Pro users can better avoid these limits by using data ranges.
If you run into errors telling you you’ve reached a quota or limitation, try using a less frequent refresh rate or a more limited refresh schedule.
Note
Also be aware there may be data delays. That means, even if you choose a quick refresh rate, the data you get may be delayed. Learn more about data delays with our data provider here.