Digital Geography

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

14
Leave a Reply

avatar
12 Comment threads
2 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
11 Comment authors
BrianAndrey OrekhovrkcsDaveSteffen A. Pfeiffer Recent comment authors

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

  Subscribe  
newest oldest most voted
Notify of
trackback

[…] 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. […]

Jakob
Guest

Very good description. It work’s!

Rory
Guest
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 ;… Read more »

trackback

[…] have a look at the mentioned “macro” tool. Follow this Link, Riccardo already summerized, how to add this cool geocoding tool to your google spreadsheet and […]

trackback

[…] have a look at the mentioned “macro” tool. Follow this Link, Riccardo already summerized, how to add this cool geocoding tool to your google spreadsheet and […]

alangalan
Guest
alangalan

Amazing.

Federico Botto
Guest
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.

Riccardo
Guest

thankl you for the comment!

Steffen A. Pfeiffer
Guest
Steffen A. Pfeiffer

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

Dave
Guest
Dave

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

rkcs
Guest
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
Guest
Andrey Orekhov

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

Brian
Guest
Brian

You’re awesome! Thanks