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.


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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.