19

I have a spreadsheet in Google Sheets 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.

Michael Mior
  • 103
  • 4
Devdatta Tengshe
  • 41,311
  • 35
  • 139
  • 263

8 Answers8

11

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

Stephen Lead
  • 21,119
  • 17
  • 113
  • 240
DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110
6

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

mvark
  • 171
  • 1
  • 3
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.)

Stephen Lead
  • 21,119
  • 17
  • 113
  • 240
  • 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. – Geographos Mar 26 '20 at 10:00
2

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

Geographos
  • 4,087
  • 2
  • 27
  • 88
2

Openstreetmap (OSM) API can help finding lat,lon information without any subscription/ API key requirement.

  1. B1 = Address e.g 1600 Pennsylvania Avenue NW, Washington, DC 20500

  2. Replace space with "%20" in address

    B2 =substitute(B1," ","%20")

  3. Create search link with response in xml format:

    B3 ="https://nominatim.openstreetmap.org/search?q="&B2&"&format=xml&addressdetails=1"

  4. Get lat and lon data in XML format and parse it with XPath filters.

    B4 =ImportXML(B3,"//place[1]/@lat") B5 =ImportXML(B3,"//place[1]/@lon")

Implementation Example: Get Latitude, Longitude of given address:

Gün Karagöz
  • 121
  • 5
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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
mjwh
  • 19
  • 2
0

For non programmers I would recommend the following approach: Use Convert Address to Lat Long tool (CSV2GEO) that is designed to perform batch geocoding by non programmers. Here are the steps:

  1. Create your Google Sheet enter image description here
  2. Then go File-Download->Excel or CSV
  3. Load the CSV2GEO tool and upload the file by either dragging it or simply browse from your local directories. enter image description here
  4. Click on the header of column C , where the address is stored and select the appropriate address tokens. In the example select all. enter image description here
  5. Click "Process Data" and the system will process the first ten rows and display them for you. Check if the addresses appear ok. You can also download into a file the first ten for detailed comparison. If all good, click "Get All Data". Somewhere along the way if you are not registered or logged in, the system will ask you to do that. And of course, the tool provide daily free geocoding of hundred addresses.

Disclosure: I am the founder of CSV2GEO.

ivan
  • 66
  • 4
-1
  1. Use free Google Sheets Add-on
  2. Select cells
  3. Watch it populate in columns Lat & Lon
Shinigami
  • 1