Google maps api in Google spreadsheets for distance (miles or meters) calculation between addresses/locations
<p>First, the context:</p>

<p>I have one spreadsheet with two columns containing hundreds of addresses (e.g. postcodes). I found this short script that allows me to quickly calculate the driving distance between the two postcodes</p>

<pre><code>  function dist(pointa,pointb) {
     Utilities.sleep(1000);
     var directions = Maps.newDirectionFinder()
        .setOrigin(pointa)
        .setDestination(pointb)
        .setMode(Maps.DirectionFinder.Mode.DRIVING)
        .getDirections();
     var route = directions.routes[0];
     var txtA = route.legs[0].distance.value;
     return txtA;
   }
</code></pre>

<p>Where pointa is address 1 (origin) in cell A1 and pointb is address 2 (destination) in cell B1. using the script above I can do this in cell C1: <code>=dist(A1,B1)</code> and I get the distance in meters. If I do this: <code>=dist(A1,B1)/1609</code> I get a fairly accurate distance in miles.</p>

<p>Second, the problem:</p>

<ol>
<li>Because of the continuous information requests, there are quite a few errors coming up which affect the stability of this short code with a great potential. For just a few addresses it is not a problem but it kind of misses the point when working with hundreds of addresses.</li>
<li>Error 1: <em>Service invoked too many times in a short time: urlfetch protected host rateMax. Try <code>Utilities.sleep(1000)</code> between calls. (line 3).</em> As you can see above, I have included the <code>Utilities.sleep()</code> but it does not correct this error</li>
<li>Error 2: <em>TypeError: Cannot read property "legs" from undefined. (line 9).</em> This is extremely frustrating. I can redress it if I add a space in one of the addresses. Then if I close the tab and re-open it the error is back and I can correct it if I remove the space. </li>
</ol>

<p>All things considered and thinking that something like this would be useful for more people, are there any ideas on how I can make it more stable. It would be even better if there is a way to call the distance between two addresses and then automatically stop the script from looking every time the spreadsheet is accessed or refreshed i.e. if there is a need to refresh, then the function has to be entered again.</p>

<p>Any help would be greatly appreciated. In case it is not obvious I have to point that I am on a steep learning curve when it comes to the basics of google script; please excuse any major obvious mistakes, constructive criticism is encouraged.</p>

<p>Many thanks,</p>