Custom Google Sheets add-on for timed formula refreshes and cell mapping.
Select a range of cells to occasionally refresh in order to force formulas to recalculate even when there are no changes.
Refreshes are necessary for custom third-party formulas that return live data from the internet. The formulas would otherwise only be recalculated when the inputs change, which may never be the case.
The active sheet can be refreshed by clicking the Set Range button. The add-on will refresh all sheets with ranges set automatically every 15 minutes.
I created Formula Refresher to make it easier to get up-to-date Bitcoin prices in my spreadsheet in combination with a third-party CRYPTOFINANCE formula that someone else created. Here's the info for the CRYPTOFINANCE formula:
=CRYPTOFINANCE("BTC/EUR", "change", "24h")
You can change the refresh interval in the script editor. Just be aware that the Sheets API limits you to 20,000 HTTP requests per day. If you refresh hundreds of formulas that make HTTP requests on a 1 minute interval, then it won't take long before the formulas start returning errors telling you to wait for 24 hours.
Note that an automated refresh may interrupt/cancel a manual formula entry taking place at the same time.
Relate cells and generate formulas in different areas of the spreadsheet by using linking information such as ids and dates.
This add-on was designed with currency exchange rate calculations in mind and works well even when currency exchange dates, i.e. keys, are defined out-of-order and in different tables or sheets.
The formula field allows the user to generate formulas and reference the mapped values with special @target and @data keywords.
Note that Google Sheets ships with a built in function called DGET that allows you to define a "database" range of cells, for example a table of currency exchange rates, and then "query" it with SQL like queries. I stopped using the Formula Generator part of my add-on once I discovered the wonders of the DGET function.
I never published the add-on on the add-on store, so you will have to get it up and running locally to try it.
To install this add-on:
- Open a Google Spreadsheet
- Navigate to Tools -> Script Editor
- Create a new project and copy the Code.gs and Page.html files
- Navigate to Run -> Test as add-on...
- Select a spreadsheet to deploy against
- In the spreadsheet, navigate to Add-ons -> Spreadsheet Utils -> Show
© Mark Hennessy
Licensed under the MIT License