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®ion=hk","//geometry/location/lat")
Longitude:
=ImportXML("http://maps.googleapis.com/maps/api/geocode/xml?address=" & $C224 & ",hong+kong&sensor=false®ion=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®ion=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.
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®ion=hk","//geometry/location/lat")
Longitude:
=ImportXML("http://maps.googleapis.com/maps/api/geocode/xml?address=" & $C224 & ",hong+kong&sensor=false®ion=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®ion=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