Digital Geography

6. October 2013

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:

header line in the spreadsheet

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

we will create a 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:

deployment of the geocoding function

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.

usage of the macro

  • You will have a problem! But I think google maps is prob. Best choice to geocode at a global level.

  • Pingback: Geocoding Google Spreadsheets: the simpler way : digital-geography.com()

  • Very good description. It work’s!

  • Rory

    I altered the code to update the spreadsheet once, rather than once each line, as the code above was very slow when updating my spreadsheet. The modified function geocodeSelectedCells follows:

    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;

    }

    //following is optimized code, using arrays and updating spreadsheet only once

    var geocoder = Maps.newGeocoder();

    var values = cells.getValues();//a 2D array

    var rows = cells.getHeight();

    var location ;

    for (var i = 0; i < rows; i++) {

    location = geocoder.geocode(values[i][0]);

    if (location.status == 'OK') {

    values[i][1] = location["results"][0]["geometry"]["location"]["lat"];

    values[i][2] = location["results"][0]["geometry"]["location"]["lng"];

    }

    Utilities.sleep(200) //limit on speed with which geocode is called–kept on getting errors otherwise

    }

    cells.setValues(values);

    };

  • Pingback: Digital Geography()

  • Pingback: QGis Tutorial: webmap creation with QGis, QGis2leaf and GoogleSpreadsheet | archaeoINaction()

  • alangalan

    Amazing.

  • Federico Botto

    If you have an empty row, this script would return an error, so simply adding a if(address != ”){ in the line 24 end closing it in line 37 would save that error.

  • Steffen A. Pfeiffer

    Just a question … would it be difficult to build a reverse geocoding macro?

  • Dave

    Is there a limit to the number of addresses you can geocode via this method?

    • rkcs

      I too would like to know if there is a limit. I have a sheet w/ 10,000 address that I’d like to geocode. I was told that there’s a 2500 limit per day if you use the google geocoding api.
      Is this the same case with this? Can I just select 2500 addresses and geocode them each day?

  • Andrey Orekhov

    how can i geocode multiply columns? i have city and adress in two different columns