Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have a Google Sheet with a list of addresses, and a column that geocodes the addresses with mapquest via Google App Script. This works great, except it "recalculates" the addresses on page load. Recalculation is set to "On Change"
.
What can I do make the cells not recalculate on page load?
You could use apps script to add a new recalculation menu item to your sheet's menu and link that item to the function that performs the calculations. That way whenever you want to recalculate you could do it on demand.
For this to work I believe all of the calculation logic would have to exist in the apps script as opposed to using built in sheet functions.
Adding custom menus using apps script.
UPDATE - I kind of missed the point of the original question but I believe my answer still applies. Clearly you don't need a "recalculate" menu item. Instead use the same concept to do the geocoding on demand when the menu option is clicked. You would probably want to add a check to the script on whether or not the column's value was already encoded or not before attempting the conversion.
What can I do make the cells not recalculate on page load?
Very simple, turn Iterative calculation on. Name a cell freeze and put a checkbox. Then apply this to the beginning of any formula you want to be able to freeze.
A1 should be the address of the cell you are in (a circular reference) rand() is just a placeholder you can put any formula you want.
=if(freeze,A1,rand())
If you have formulas that output to multiple cells you will have to create a helper cell for each one of them to "compact" the output into a single cell and use the second cell to "extract" them into multiple cells due to a glitch in how Google Sheets is able to detect circular references.
It's not possible. As the dialog shows, the recalculation setting only affects certain built-in functions.
Instead of using a custom function you should use another way to run your script and edit it accordingly as now is described on In google sheets can I wrap a standard function in a custom function to control when it is run?
Summary:
Store the formula on a global variable or using the PropertiesService, the use copyTo to overwrite the formula range with it's results to "freeze" that range. When you need to refresh the values, first clear the formula results range then use setFormula to put the formula again in place.
