Building a Real Time Data server for Excel: Talking to the GoogleMaps APIs, part 7
Posted on Thursday, January 20th, 2011 at 11:04 am by Maurice Calvert.
This is part 7 of the tutorial that describes the techniques necessary to build an Excel RTD server. If you have just come to this page, I suggest you read all parts in order, here is Building a Real Time Data for Excel, part 1.
This subject is already extremely well documented here, so I’ll just point you to the relevant code.
- The call to GoogleMaps is at #7# in Geodesic.Resolve.Resolve.
- The resultant JSON object is made into a .Net object with System.Web.Script.Serialization.JavaScriptSerializer. This is technically obsolete, but trying to figure out how to use DataContractJsonSerializer was beyond my patience and JavaScriptSerializer #21# did the job perfectly.
- We parse the resultant object crudely in Geodesic.GetLocations.GetLocations #22# for geocode requests and Geodesic.GetRoutes.Getroutes #23# for Travel requests.
- Notice that in GetRoutes, we don’t parse the details of each step by default, they add nothing to the information we need for this application (simply the distance and the duration).
- Perhaps a word on Google’s Terms Of Service. In a nutshell, you may not make more than 2’500 geocode requests in a single 24-hour period. If you exceed this you will receive a 602 return code, as you will if you make requests too quickly in succession. Geodesix respects this and slows down automatically if it receives a 602.
- The corollary is that information obtained from Google is ‘valuable’ and we should at all costs avoid asking for it again. Geodesix does this by saving the cache to a ‘very hidden’ worksheet #24# and re-loading this cache when a workbook is re-opened #25#. This may be defensible, but it is barely in line with the T.O.S.
- The moral is: Please use this addin responsibly. I have no doubt that Google couldn’t care less if you geocode a few dozen locations occasionally and keep the answers. On the other hand, using Geodesix to perform bulk geocoding is immoral, irresponsible and could potentially spoil a very useful service that benefits the majority. Again, please play fairly and think of others.
Building a Real Time Data for Excel tutorial:
- Building a Real Time Data for Excel server, part 1
- Building an Excel RTD: Avoiding VSTO, part 2
- Building an Excel RTD: How RTD servers work, part 3
- Building an Excel RTD: Architecture, part 4
- Building an Excel RTD: Excel, multithreading and callbacks, part 5
- Building an Excel RTD: Providing easy-to-read function names, part 6
- Building an Excel RTD: Talking to the GoogleMaps APIs, part 7
- Building an Excel RTD: Avoiding Application Domain misery, part 8