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?


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.


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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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