title: Google maps api in Google spreadsheets for distance (miles or meters) calculation between addresses/locations
body: <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>