0

I am using Google Sheets as a mileage tracker to calculate the distance from my home to various addresses. This formula had been working for me for a few months, but recently stopped:

=ROUND((importxml("https://maps.googleapis.com/maps/api/distancematrix/xml?&origins="&"My+Home+Address"&"&destinations="&A1&"&sensor=false&units=imperial&alternatives=false&key=MY_API_KEY","/DistanceMatrixResponse/row/element/distance/value")/1609.344)*2,1)

I am using my own valid API key and have verified that the query works outside of Google Sheets. The funny thing is, if I change "/distance/value" to "/duration/value", Sheets returns a response just fine.

Here's what the returned XML looks like for the Distance Matrix:

<DistanceMatrixResponse>
<status>OK</status>
<origin_address>Home Address, USA</origin_address>
<destination_address>Destination Address, USA</destination_address>
<row>
<element>
<status>OK</status>
<duration>
<value>1983</value>
<text>33 mins</text>
</duration>
<distance>
<value>29971</value>
<text>18.6 mi</text>
</distance>
</element>
</row>
</DistanceMatrixResponse>

How do I get the "distance" to work again??

Tim B
  • 1

1 Answers1

0

This works for me:

=IMPORTXML("https://maps.googleapis.com/maps/api/distancematrix/xml?&origins="&B1&"&destinations="&B2&"&sensor=false&alternatives=false&key="&B3,"/DistanceMatrixResponse/row/element/distance/text")

BTW, if you get rid of units=imperial, it defaults to metric and you won't have to /1609.344

enter image description here

ADW
  • 4,177
  • 1
  • 14
  • 22
  • Thanks! It worked for me too. Unfortunately, I have no idea why. I was also able to change the units to imperial (since I need the result in miles) and get the value (rather than the text) successfully. – Tim B Mar 16 '20 at 04:13