Google Map API (Google Spreadsheet)

How to translate text address into Latitude / Longitude?  

We can use Google Doc + Google Geocode API to perform address to Coordinates on Google Spreadsheet.

Use the below 2 formulas:

Latitude:
=ImportXML("http://maps.googleapis.com/maps/api/geocode/xml?address=" & $C224 & ",hong+kong&sensor=false&region=hk","//geometry/location/lat")

Longitude:
=ImportXML("http://maps.googleapis.com/maps/api/geocode/xml?address=" & $C224 & ",hong+kong&sensor=false&region=hk","//geometry/location/lng")

Limitation:
1. Google limit only 50 import XML per worksheets
2. the import XML may return more than one place.  In this case, Goggle Doc will add a CONTINUE function under the function.  If you do not want the second result expand:

=noexpand(ImportXML("http://maps.googleapis.com/maps/api/geocode/xml?address=" & $B4 & ",hong+kong&sensor=false&region=hk","//geometry/location/lat"))

3. To limit the search result, I have added "Hong Kong" as a default search keyword so that if I search for "nathan", it will not return Nathan of Australia.

Comments

Popular Posts