Digital Geography

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:

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”:

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:

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…

24
Leave a Reply

avatar
13 Comment threads
11 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
16 Comment authors
stevepepeDerry BrownRico TrevisanYhonatantitaniumapple Recent comment authors

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

  Subscribe  
newest oldest most voted
Notify of
Nicolás Cerrini
Guest
Nicolás Cerrini

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

Ryan Tate
Guest
Ryan Tate

Works great!

Ender
Guest
Ender

Amazing! thank you so much!

PR
Guest
Geolicious

“Approve”

Rodrigo Wolburg Chávez
Guest
Rodrigo Wolburg Chávez

greaaaaat !!!!!! thanks

Eamon
Guest
Eamon

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
Guest
Justin Smith

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
Guest
Eamon

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

Justin Smith
Guest
Justin Smith

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
Guest
Eamon

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

PromoNet
Guest

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

Michael Thwaite
Guest
Michael Thwaite

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

ggagin
Guest
ggagin

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

Riccardo
Guest

wait a day or use another coder 😉

ggagin
Guest
ggagin

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

Riccardo
Guest

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
Guest

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
Guest
titaniumapple

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
Guest
Yhonatan

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

Rico Trevisan
Guest
Rico Trevisan

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

Riccardo
Guest

look right:.. there is a donation button 😉

Riccardo
Guest

Thank you Rico for your donation!

Derry Brown
Guest

You rule!

stevepepe
Guest
stevepepe

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 »