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…