Get the distance between two Addresses in google sheets with mode transit

Question

I am trying to get the distance between A an B with the mode transit.

This is my code on google sheets(excel)

=importXML("http://maps.googleapis.com/maps/api/directions/json?origin="& A4  & "&destinations=" & D4 & "&mode=transit&arrival_time=1391374800&key=MYKEYHERE"; "//distance/text")

It says the the "Url can not be called". Im sitting on this problem for some time and i would appreciate any help.

Other then importing XML and in the code it says /json?origin I don't really understand the problem.

EDIT: I found this in the Doc

<travel_mode>TRANSIT</travel_mode>
    <start_location>
     <lat>40.7563670</lat>
     <lng>-73.9907530</lng>
    </start_location>
    <end_location>
     <lat>40.8179080</lat>
     <lng>-74.0656630</lng>

But im not sure how to input it in the line above. I do the all the long and lat for the destination and origin.

Answer 1

here is the answer

 =importXML("https://maps.googleapis.com/maps/api/distancematrix/xml?&origins="& A4 & "&destinations=" & D4 & "&mode=transit&key=YOURKEYHERE"; "//distance/text")