If you use the Google APIs, you’ll have been hit with the “service invoked too many times in one day” problem. In Backing off on rate limiting I showed how to deal with rate limited APIs, but that doesn’t help when your quota applies across a whole day. It’s better to try to find a strategy to minimize the calls in the first place.
You can take a copy of this sheet and the code here

Mileage table

Someone had posted a question on the GAS community, asking how to create one of those mileage matrixes showing the distance between two points. They had tried but hit the rate limit exceeded. Applying Backing off on rate limiting got them a little further, but then they hit the “too many times in one day” problem. The maximum for a free consumer account is 2,500 per day and for a business acccount 100,000 per day. Another couple of annoying quota restrictions are a maximum number of characters per uri encoded direction instructions, and a restriction on the number of waypoints allowed in a direction.

The DirectionFinder

There is another API – the Direction Matrix API – which might have been good for this problem but the terms of use say –

Use of the Distance Matrix API must relate to the display of information on a Google Map; for example, to determine origin-destination pairs that fall within a specific driving time from one another, before requesting and displaying those destinations on a map. Use of the service in an application that doesn’t display a Google map is prohibited.

Instead though, we are going to use the Directions API, which also has the same terms of use, but is probably more applicable for creating maps with. I’m also going to include a few extra goodies like lat/long, duration of travel and so on. If using this code, please ensure you follow the terms and create a map with the result. You can read the terms of use here.

Although these functions will work with or without an API key, I recommend you set up a project in the cloud console, get a public Api key and use it. Although the quotas are the same, the main benefit is that you’ll be measured per API key rather than ip address for your daily usage.

One of the things I noticed about the DirectionFinder of the Directions API, is that it allows waypoints. So let’s say you wanted to create a mileage matrix of points a,b,c,d,e The simple approach would be to calculate each combination of AB, AC , AD, AE, BC, BD, BE, CD, CE ,DE , which would take 20 calls. That could be halved to 10 if you assume that AB is the same as BA (in other words we have a symmetrical table). However you can do all that in 6 calls using waypoints. A-B-C-D-E , A-C-E, A-D, A-E, BD, or in 1 call if you add dummy waypoints. As it turns out there is a quota on the number of waypoints too, which makes calculating the calls a little complex.

The algorithm tries to make as long a route as possible with linked waypoints, and doesn’t need the mileage chart to have symmetrical from and to points. What’s left over is then joined up with dummy waypoints to minimize the number of calls. I’m sure it can be optimized further, but the table below shows the call saving that could be made using waypoints depending on whether you are using the free API or a business account – although this is complicated by the quota on url direction length (which I can’t see from within GAS)

 Points  No of routes   Assume AB = BA, but dont use waypoints  Use waypoints – free api max 8  Use waypoints – business account max 23
 5  20  10  5  5
 7  42  21  7  7
 10  90  45  15  15
 50  2450  1225  141  85

Whether to use the GAS API?

When I started this, I used the built in Maps service of GAS. However, it failed inexplicable with “server error” from time to time and seemed very fragile, so instead this uses the Directions JSON API directly, which is more robust and allows more control. For example. one thing I found is that certain routes would generate a server error 500 for no apparent reason. Splitting the routes in two on such a failure (although no quota was exceeded), would make it work again.

The waypoint code

It takes two arrays, one of sources and one of destinations and returns a set of route plans.

Applying the routes

Now we can apply the routes to the direction finder, using the waypoints to minimize api calls as follows

And we can get the input from and to cities for this from a sheet like this, and populate the results

Finally I am using exponential backoff to deal with invoking the API too quickly.

And a snip of the result looks like this – but remember that you need to use these results to generate a Google Map as per the terms of use.

For more like this see Google Apps Scripts Snippets
For help and more information join our forum, follow the blog, follow me on Twitter