Geocoding Google Spreadsheets

For our job site here on digital-geography I was searching for a way to auto geocode spreadsheet location cells. So I’ve found this very short announcement from Max. See it here in depth:

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

Next Max showed us the code to use. So go to Tools->Script Editor and click on “create new project” in the new tab of the script edditor. In the new dialog choose “Blank Project”:

Replace the initial lines with the code from Max:

function geocodeSelectedCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getActiveRange();
 
  // Must have selected 3 columns (Location, Lat, Lng).
  // Must have selected at least 1 row.
 
  if (cells.getNumColumns() != 3) {
    Logger.log("Must select the Location, Lat, Lng columns.");
    return;
  }
 
  var addressColumn = 1;
  var addressRow;
 
  var latColumn = addressColumn + 1;
  var lngColumn = addressColumn + 2;
 
  var geocoder = Maps.newGeocoder();
  var location;
 
  for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
    address = cells.getCell(addressRow, addressColumn).getValue();
 
    // 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"];
      lng = location["results"][0]["geometry"]["location"]["lng"];
 
      cells.getCell(addressRow, latColumn).setValue(lat);
      cells.getCell(addressRow, lngColumn).setValue(lng);
    }
  }
};
 
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item.
*
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
*
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name: "Geocode Selected Cells Worldwide",
    functionName: "geocodeSelectedCells"
  }];
  sheet.addMenu("Macros", entries);
};

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:

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”. Now you should see the new menu entry “Macros” and below this your newly created function.
To use the macro select the cell with the adress and the next two cells with “Lat” and “Lon” go to Macros->Geocode Selected Cells Worldwide and be happy with your new coordinates.