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.

Improve this question
0

8 Answers 8

Reset to default
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

Improve this answer
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

Improve this answer
3
  • 1
    That doesn't work without an API key anymore.
    – grssnbchr
    Nov 8, 2018 at 15:54
  • 2
    Noe you should add your api &key= to URL and it will work
    – SERG
    Apr 10, 2020 at 6:46
  • 1
    Confirmed, =IMPORTXML("https://maps.googleapis.com/maps/api/geocode/xml?address="& A1 &"&key=YOURKEYHERE", "/GeocodeResponse//location") works great! Thank you. See docs here to create an API key.
    – Greg Sadetsky
    Dec 1, 2021 at 2:19
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
  • 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, 2020 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

Improve this answer
1

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:

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
  • Note that this service only geocodes US and Canadian addresses.
    – moonhouse
    Sep 2, 2020 at 8:21
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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