Google Maps API and Excel---Works perfect except one field not sure why

[dumbwithdata]

I have been muddling together calls to Google Maps API with an Excel sheet and after lots of testing and borrowing samples from other peoples projects (that I do not quite understand) I have the whole package working perfectly. Basically the workbook has multiple sheets and each of these sheets has one destination and about 30 origins. The call goes out to Google for all 30 of these and the Excel looks the duration value for each. Well I have altered the call to get the 'duration_in_traffic' value as opposed to the duration value. However, I cannot get these results to appear in my sheet. If I change this to distance rather than duration_in_traffic it will return the value fine. I am lost as to what the issue is. I have tried to troubleshoot based on my limited knowledge and:

• If I make the call straight in Chrome, it comes back with all of the correct data so I know that this element is fine.
• I changed to distance item so I know that it is not some strange naming or the like that needs to be changed in the VBA.
• I tried multiple iterations with the 'duration_in_traffic' to see if some standard I was missing.
• I reviewed the XML results that come back when you call in Chrome and I cannot see any structural difference that would make the call for distance and duration the same but duration_in_traffic different.

The only thing I can think of, which is related to that last point, is that the 'duration_in_traffic' value is only provided one in a call whereas the distance and duration values are provided multiple times for each step and then a total one which is the one we grab, but not sure about this. This is what the relevant portion of the current call looks like:

Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    
    ' If statusNode is "OK" then get the values to return
    Set statusNode = myDomDoc.SelectSingleNode("//status")
    If statusNode.Text = "OK" Then
        If NoCache Then: Call CreateFile(CachedFile, myRequest.responseText) ' Cache API response if required
        ' Get the duration
        Set durationNode = myDomDoc.SelectSingleNode("//leg/duration/value")
        If Not durationNode Is Nothing Then G_DURATION = val(durationNode.Text) / 60 ' Now works with comma as decimal separator
    End If

Thank you for any help that you can provide and I am hoping that the issue is really only because I am doing cut and paste code with no real clue. Thank you.

 

[mole999]

are you using the latest api. google has a habit of changing it which causes stuff to fail

 

[dumbwithdata]

I believe I am. The original project that I borrowed most of the code for was using a call that did not require a key, but since I added the departure time, traffic model, and duration_in_traffic I had to go get a key for it so would assume all up to date. In addition, all of the data comes back perfect when I make the call straight from Chrome and review the results in Chrome so I assume the call is fine and I am just messing up something bringing it into Excel. I think there is something wrong with the syntax that I am using, but I know just enough to be dangerous and get myself in trouble. This line is the original and works fine:

SetdurationNode = myDomDoc.SelectSingleNode("//leg/duration/value")

And if I change it to this it also works fine:

SetdurationNode = myDomDoc.SelectSingleNode("//leg/distance/value")

But it does not work if I try this:

SetdurationNode = myDomDoc.SelectSingleNode("//leg/duration_in_traffic/value")

All of these have the same data types, so I am assuming that my problem is with the syntax I am using which I am guessing is caused by either fact that duration_in_traffic (DIT) is (1) on a different level of the hierarchy and they needs to be written differently or (2) possibly the underscore character is to blame...I am lost but these are my best guesses.

Thank you for responding so promptly.

 

[mole999]

is the place the data is coming from accessible for anyone, just wondering what the URL is

 

[dumbwithdata]

The data engine is available to anyone, though if you also want the parameters I mentioned above you need to get a key which is free with quote restrictions.

This is the URL with all the green/bold text being parameters that you pass to it. The resulting page is your results in either XML or JSON

https://maps.googleapis.com/maps/api/directions/xml?units=imperial&avoid=tolls&departure_time=1501572600&traffic_model=pessimistic& origin=Chicago&destination=Detroit&mode=driving&language=en-EN&key=YOURKEYYOURKEYYOURKEYYOURKEY