I’m trying to develop a function in excel that returns the traveltime between two locations. I used this code i found online, yet always get a #value error. When I plug in the strUrl in my browser with my apikey the request works.
Help is very much appreciated.
' Returns the number of seconds it would take to get from one place to another Function TRAVELTIME(origin, destination, apikey) Dim strUrl As String strUrl = "https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=" & origin & "&destinations=" & destination & "&key=" & apikey Set httpReq = CreateObject("MSXML2.XMLHTTP") With httpReq .Open "GET", strUrl, False .Send End With Dim response As String response = httpReq.ResponseText Dim parsed As Dictionary Set parsed = JsonConverter.ParseJson(response) Dim seconds As Integer Dim leg As Dictionary For Each leg In parsed("routes")(1)("legs") seconds = seconds leg("duration")("value") Next leg TRAVELTIME = seconds End Function