Geocoding Google Spreadsheets: the simpler way

As we have tried to geocode addresses in Google Spreadsheets automatically we have found a solution. It turned out, that this solution is not usefull if you want to produce Lat/Lon automatically. I’ve played a little bit and would like to show you an easier and simpler way.

First off all create a spreadsheet with the header line Location, Lat and Lon:

google spreadsheet header
header line in the spreadsheet

Next Max showed us the code to use but we will change it: We will produce two functions which have the address as input and provide a result for the lattitude and one for the longitude… So go to Tools->Scipt Editor and click on “create new project” in the new tab of the script edditor. In the new dialog choose “Blank Project”:

geocoding with spreadsheets: blank project
we will create a blank project!

Replace the initial lines with the code from Max:

function getLat(address) {
  if (address == '') {
    Logger.log("Must provide an address");
    return;
  }
  var geocoder = Maps.newGeocoder();
  var location;
    // Geocode the address and plug the lat, lng pair into the
    // 2nd and 3rd elements of the current range row.
    location = geocoder.geocode(address);
    // Only change cells if geocoder seems to have gotten a
    // valid response.
    if (location.status == 'OK') {
      lat = location["results"][0]["geometry"]["location"]["lat"];
 return lat;
    }
};
 function getLon(address) {
  if (address == '') {
    Logger.log("Must provide an address");
    return;
  }
  var geocoder = Maps.newGeocoder();
  var location;
    // Geocode the address and plug the lat, lng pair into the
    // 2nd and 3rd elements of the current range row.
    location = geocoder.geocode(address);
    // Only change cells if geocoder seems to have gotten a
    // valid response.
    if (location.status == 'OK') {
      lng = location["results"][0]["geometry"]["location"]["lng"];
 return lng;
  }
};

Save the code and publish it by clicking on Publish->Deploy as Web App give it a name and save it as the first version. You should see something similar to this:

deploy as a web app in google spreadsheets
deployment of the geocoding function

<

p align=”justify”>Press deploy at the end. Press “OK” to close the last dialog you will see which gives you an adress for your webapp. Go back to your spreadsheet and reload it by pressing “F5”.
To use the function just type in a cell of your choice

=getlat(A2)

and in another

[=getlon(A2)

and you will see a nice and easy set of coordinates which can be used in a webmapping application by publishing your spreadsheet as a csv…

0 0 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

25 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Nicolás Cerrini
Nicolás Cerrini
9 years ago

Awesome work! Tried several solutions before, and this is the only one that worked for me… so simple!

Ryan Tate
Ryan Tate
9 years ago

Works great!

Ender
Ender
9 years ago

Amazing! thank you so much!

Geolicious
Geolicious
9 years ago
Reply to  Ender

“Approve”

Rodrigo Wolburg Chávez
Rodrigo Wolburg Chávez
9 years ago

greaaaaat !!!!!! thanks

Eamon
Eamon
9 years ago

Thanks a million for this piece of code. It inspired me to create a reverse geocode script!

function getAdd(lat, lng) {
// Return Address by taking the coordinates and reverse geocoding.
if (lat == “”) {
return “You have to provide latitudinal coordinates to the place”
} if (lng == “”){
return “You have to provide longitudinal coordinates to the place”
}

var response = Maps.newGeocoder().reverseGeocode(lat, lng); //Call the reverse Geocode service
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];

return result.formatted_address; //Output full address in cell

}

};

Justin Smith
Justin Smith
9 years ago
Reply to  Eamon

Hi there. This was working great for me up until this morning when Google Sheets starting giving me the following error: cannot find method reverseGeocode(object,(class)). Any reason why?

Eamon
Eamon
9 years ago
Reply to  Justin Smith

Just tested mine right now. Still working. Maybe it was a temporary issue.

Justin Smith
Justin Smith
9 years ago
Reply to  Eamon

That’s so odd, here’s what my function looks like–exactly like the one you posted. What am I doing wrong?

function getAdd(lat, lng) {

// Return Address by taking the coordinates and reverse geocoding.

if (lat == “”) {

return “You have to provide latitudinal coordinates”

} if (lng == “”){

return “You have to provide longitudinal coordinates”

}

var response = Maps.newGeocoder().reverseGeocode(lat, lng);

for (var i = 0; i < response.results.length; i++) {

var result = response.results[i];

return result.formatted_address;

}

};

Eamon
Eamon
9 years ago
Reply to  Justin Smith

Nothing wrong with the code. Something intermittent with the service.

PromoNet
9 years ago

Thanks for a really simply version of this! Is there a way to increase the envoke script limit?

Michael Thwaite
Michael Thwaite
9 years ago

Can I give you six stars for such simple step-by-step instructions? Thanks for speeding up my day.

ggagin
ggagin
9 years ago

Thanks! Just one problem: I have about 1000 addresses to geocode. Is there anything I can do about this error (see attached image)?

Riccardo
9 years ago
Reply to  ggagin

wait a day or use another coder 😉

ggagin
ggagin
9 years ago
Reply to  Riccardo

Haha, thanks! I’ll try again Thursday and let you know how it turns out.

Riccardo
9 years ago
Reply to  ggagin

or you can try to alter the code so it will just be called once to get lat and lon together, should be easy to do. and so you wont use 2000 service units for 1000 addresses.

Riccardo
9 years ago
Reply to  ggagin

use only lat function but alter it to look like:

if (location.status == ‘OK’) {
lat = location[“results”][0][“geometry”][“location”][“lat”];
lon = location[“results”][0][“geometry”][“location”][“lon”];
//attention here just pseudeocde:
//coord = concatenate(lat, lon, separated by “;”)
//return coord

}
};

titaniumapple
titaniumapple
9 years ago

Any way to get your current location in Google Sheets automatically? Is there code that will use Google Maps to give you exactly where you currently are?

Yhonatan
Yhonatan
9 years ago

infinite thanks .this code might get my app in the air much quicker and better than expected.

Rico Trevisan
Rico Trevisan
8 years ago

Wow, you just made me look like a genius in front of my partner! Thanks!

Riccardo
8 years ago
Reply to  Rico Trevisan

look right:.. there is a donation button 😉

Riccardo
8 years ago
Reply to  Rico Trevisan

Thank you Rico for your donation!

Derry Brown
8 years ago

You rule!

stevepepe
stevepepe
7 years ago

Thanks, this work brilliantly! Even better, it could throw customized native errors when it doesn’t work, and return a 2×1 array of lat & lon when it does. This avoids geocoding twice as many times as necessary (as addressed in an earlier comment), without needing to use other functions to separate the two numbers with a semicolon in between: function geoGetLatLon(address) { if (address == ”) { throw Error(“No address provided”); } var geocoder = Maps.newGeocoder(); var location; location = geocoder.geocode(address); if (location.status == ‘OK’) { lat = location[“results”][0][“geometry”][“location”][“lat”]; lng = location[“results”][0][“geometry”][“location”][“lng”]; return [[lat,lng]]; } else { throw Error(“Geocode failed”);… Read more »

ikik
ikik
4 years ago

hm I have searcheed for such a solution and it is great, but can it be used as arrayformula?