This repository is for the Refresh Tool by Sheets Market Data.
This tool was created to help assist users of the Sheets Market Data add-on for Google Sheets to have the ability to refresh their data at periodic intervals without having to manually do so.
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're releasing our Refresh Tool. The Refresh Tool is a completely free, open source tool to help you automatically refresh you Google sheets so that you have the latest data. It works out of the box with the Sheets Market Data add-on but can also be configured to work with other add-ons or functions.
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.
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 this 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 this respository 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!
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.
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 (optional).
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!
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. If you are using this tool with the Sheets Market Data add-on, we offer better ways to 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.
For more information about the Sheets Market Data add-on, visit our website. For additional details and documentation for the Refresh Tool, click here.