11

I have a spreadsheet in Google Docs with about 20-30 rows, with the following 6 columns :

ID, Name, Address, Cell_No, Latitude, Longitude where the last two columns are empty.

What is an easy way, for a novice user, who is a non-programmer, to geocode the address and populate the Latitude and Longitude fields with result of Geocoding?

I just need to populate the Latitude & Longitude fields. I do not need to see any data on a map.

7

1) There's a bit of code you can add to your spreadsheet and run a macro. The instructions are detailed here: https://vilimpoc.org/blog/2013/07/11/google-spreadsheet-geocoding-macro/ with the code also available at https://github.com/nuket/google-sheets-geocoding-macro

enter image description here

2) CartoDB can pull in a google doc spreadsheet directly from your account, then you can export it back out:

enter image description here

enter image description here

3) Download your Google Drive Spreadsheet to a CSV, then use the MMQGIS Geocode with CSV plugin against the Google Maps API to geocode your spreadsheet

| improve this answer | |
4

GPS Visualizer is a handy site which performs batch geocoding.

To use this free utility, simply enter addresses in the box to the left, one per line, and click "Start geocoding" to find their latitude and longitude.

Note that "Google's policies do not allow output in text format" so you'll need to use another geocoder like Bing or MapQuest.

(Alternatively, and assuming this doesn't break the ToS, you can use Google as the geocoding engine, then download the results as a KMZ and extract the coordinates from this.)

| improve this answer | |
  • 1
    Now you need your own API key fro Google, Bing or MapQuest if you wish to geocode hundred addresses in GPS visualizer. It has been changed prorably around 2018. – MKR Mar 26 at 10:00
4

I found this Google Sheets formula in an answer in the Google Forums useful -

=IMPORTXML("http://maps.googleapis.com/maps/api/geocode/xml?address="&C1, "/GeocodeResponse//location")

Replace "C1" in the formula, with the cell that holds the address to geocode

| improve this answer | |
  • That doesn't work without an API key anymore. – grssnbchr Nov 8 '18 at 15:54
  • 1
    Noe you should add your api &key= to URL and it will work – SERG Apr 10 at 6:46
1

Install the geocode plugin avasilable in the Google Sheets add-ons. It's a VBA excel macro, but it will help you if you are not clued up enough with coding.

Then in your main toolbar you should see the Geocode option. CLick it and you have 2 options: from address to lon,lat and other way round.

Remember, that script can do only 1000 records per day! Otherwise you won't be able to carry on, even with your own macro!

enter image description here

| improve this answer | |
0

You can geocode a spreadsheet using Geocodio. You can upload the spreadsheet or just copy/paste the list of addresses, and it'll turn the addresses into latitude/longitude it for you. The first 2,500 addresses per day are free.

Disclosure: I'm a co-founder of Geoocodio.

| improve this answer | |
-1
  1. Use free Google Sheets Add-on
  2. Select cells
  3. Watch it populate in columns Lat & Lon
| improve this answer | |
-2

One more solution for geocoding and other mapping tricks (isolines and heatmaps) in Google Sheets: Maps for Sheets

| improve this answer | |
  • Not original solution added to a very old question. – Val P Mar 7 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.