Data & beer; Beer & data.

JSON APIs in Excel

Posted: August 12th, 2012 | Author: | Filed under: Projects | Tags: , , , , | 1 Comment »

My wife & I were talking about moving to a new place and one potential candidate came up that didn’t seem to solve my #1 priority of spending less time in transit.  It was closer to our nearest freeway but at an exit further away than our current place.  (Pro tip: skip the article and just download the code right here).

She seemed pretty keen on the place but I couldn’t quite justify a new place that didn’t save me any time.  I figured there are a few places I visit on a pretty regular basis, so I needed some way to work out if, on average, I’d be spending less time travelling.

A quick scan of the Google Maps API showed that I can pretty easily pull out the distance & duration between two addresses (as simple strings).  Now to get the data into some form that can be manipulated easily.

Excel 2010 provides a few ways of importing data; the Google API supports XML so I checked that first.  Unfortunately the XML import isn’t live, it’s only a once-off import, and I want to be able to update a value and have it update my sheet straight away.

Excel also supports a “Web Query” for importing data, which is pretty cool, you give it a URL and it will take a <table/> out of a web page and bring it in for you to manipulate.  Cool, but not helpful here.

Since it didn’t look like Excel could do it natively using it’s default tools I got my hands dirty.  This question on Stack Overflow had the goods: a class library in VBA that pulls a string data feed from a URL and a link to this project that provides a VBA class for parsing JSON string data.

With those two modules neatly embedded in my sheet, all it took was a few lines into a custom function that retrieved the JSON API result and parsed it to the single value I needed.  Since I don’t want to get black-listed from the API, I popped a (very) simple cache in place.  Don’t get too confident though, it only caches to memory, so will still need to retrieve every calculation each time you load the sheet.

Dim DistCache As New Scripting.Dictionary

Function CalculateDistance(startAddress As String, endAddress As String)
  Dim key As String
  key = startAddress & "|" & endAddress

  If DistCache.Exists(key) Then
    v = DistCache(key)
  Else
    Dim request As New SyncWebRequest
    request.AjaxGet ("http://maps.googleapis.com/maps/api/directions/json?origin=" & startAddress & "&destination=" & endAddress & "&sensor=false")
    Dim json As String
    json = request.Response

    Dim parser As New JSONLib
    Set result = parser.parse(json)
    Set routes = result("routes")
    Set route = routes(1)
    Set legs = route("legs")
    Set leg = legs(1)
    Set dist = leg("distance")
    v = dist("value")
    DistCache(key) = v
  End If

  CalculateDistance = v
End Function

I saved the spread sheet (with non personal data) – if you want to check it out you can download it here.  Remember you will need to enable macros before it will work.  To check out the source, hit the “Developer” tab and click “Visual Basic”.  It looks like this place will save me a heap of time after all – now that the hard bit is done we just have to buy it :)


One Comment on “JSON APIs in Excel”

  1. 1 Arndt Dienstbier said at 12:27 am on August 28th, 2014:

    Hi!
    There is a little ‘bug’ in JSONLib.parseNumber:
    CInt() is limited to 32767. A distance above 32767 meters will raise an overflow error.
    Use CLng() instead.
    Regards!


Leave a Reply