Digital Geography

13. October 2013

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…

  • Nicolás Cerrini

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

  • Ryan Tate

    Works great!

  • Ender

    Amazing! thank you so much!

    • Geolicious

      “Approve”

  • Rodrigo Wolburg Chávez

    greaaaaat !!!!!! thanks

  • 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

      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

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

        • 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

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

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

  • Michael Thwaite

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

  • ggagin

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

    • wait a day or use another coder 😉

      • ggagin

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

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

    • 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

    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

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

  • Rico Trevisan

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

    • look right:.. there is a donation button 😉

    • Thank you Rico for your donation!

  • You rule!

  • 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”);
    }
    };