9

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.

  • My question is just about populating the Latitude & Longitude fields. I do not need it on a map. – Devdatta Tengshe Jun 23 '14 at 5:59
  • did any of the solutions below work for you? – DPSSpatial Mar 25 '15 at 19:58
  • @mapBaker: Not really. I wanted the latitude & longitude to be populated In-situ. None of these answers show how it can be done. – Devdatta Tengshe Mar 26 '15 at 4:41
  • what do you mean 'in-situ'? – DPSSpatial Mar 26 '15 at 15:07
  • I wanted to populate the colums in the existing google sheet, instead of having to downloading it and processing the data somewhere else – Devdatta Tengshe Mar 26 '15 at 16:13
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/

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

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.)

3

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

  • That doesn't work without an API key anymore. – wnstnsmth Nov 8 '18 at 15:54
0

I came across the same question and here you may find the answer using the script editor: http://www.digital-geography.com/geocoding-google-spreadsheets/#.U6fem5R_uSo

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

very good tutorial on how to get geocoordinates from address found here http://grindgis.com/software/microsoft-excel/geocoding-excel-and-google

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

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.

Edit: I'm a co-founder of Geoocodio. Apologies for any confusion about that.

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.