I am trying to transform addresses in my google sheet into lat/long. I have already gotten myself an API key for this purpose and found the following formula on the Google Forums:
=IMPORTXML("http://maps.googleapis.com/maps/api/geocode/xml?address="&C2, "/GeocodeResponse//location")
But no matter how I format the address, I get a #N/A.
In the response on Google forums they also mentioned having to insert my API into the link, which i assumed would be replacing the 'api' but they never specified where exactly i would need to put it.
The basic address formatting is:
Na Folimance 1979/1, 120 00 Praha
I have tried:
Na%Folimance%1979/1%12000%Praha
And also:
Na+Folimance+1979%2F1,+120+00+Vinohrady,+Czechia
which is how the address is formatted when I put it into Google maps.
Other responses about geocoding don't use the script above at all, so I don't know what I'm missing here.
I found the answer, the problem was not adress formatting, but the API key placement, if the address is in cell A1, the correct formula is:
=IMPORTXML("https://maps.googleapis.com/maps/api/geocode/xml?key=INSERTYOURKEYHERE&address="&A1,"/GeocodeResponse//location")
Hope this helps