Google Spreadsheet Geocoding Macro

I’ve been doing a bit of nerding around with a side project, which involves editing a bunch of addresses in Google Sheets and having to geocode them into raw lat/lng coordinate pairs.

google-sheets-geocode-macro

I went ahead and coded up a quick App Script macro for Google Sheets that lets you select a 3-column wide swath of the spreadsheet and geocode a text address into coordinates.

Update 10 January 2016:

The opposite is now true too, you can take latitude, longitude pairs and reverse-geocode them to the nearest known address. Make sure you use the same column order as in the above image: it should always be Location, Latitude, Longitude.

I’ve moved the source to Github here:
https://github.com/nuket/google-sheets-geocoding-macro

It’s pretty easy to add to your Google Sheets, via the Tools -> Script Editor. Copy and paste the code into the editor, then save and reload your sheet, and a new “Geocode” menu should appear after the reload.

23 thoughts on “Google Spreadsheet Geocoding Macro”

  1. It flies!

    I found a small glitch, maybe because of HTML code?
    Replace < by < in line 22, so it reads:
    for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {

  2. Pingback: Digital Geography
    1. My guess: It could be a matter of formatting the address the way it would be in the country where it is used. There are seemingly endless ways in which addresses get punctuated…

    1. Something similar like:


      function reverseGeocode() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var cells = sheet.getActiveRange();

      var location;

      var addressColumn = 5; // this will be the destination where the location (address) should be added
      var addressRow;

      var lat = parseFloat("523.528.854");
      var lng = parseFloat("49.037.551");
      var latlng = new google.maps.LatLng(lat, lng);
      var geocoder = Maps.newGeocoder().setRegion('nl');

      // Must have selected 2 columns (Lat, Lng).
      // Must have selected at least 1 row

      if (cells.getNumColumns() != 2) {
      Logger.log("Must select the Lat and Lng columns.");
      return;
      }

      geocoder.geocode({ 'latLng': latlng }, function (results, status) {

      if (status == google.maps.GeocoderStatus.OK) {
      if (results[1]) {
      console.log("Location: " + results[1].formatted_address);
      }
      }

      });

      }

      /**
      * 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",
      functionName: "geocodeSelectedCells"
      }];
      sheet.addMenu("Macros", entries);
      };

  3. Great tool. Thanks for this.

    Quick question: I found an error saying

    TypeError: Cannot find function toLowerCase in object Mon Jan 25 2016 03:00:00 GMT-0500 (EST).

    Any thoughts about how can I get it fixed?

    Thanks in advance.

  4. Hello,
    First, thanks for this awesome piece of code.
    When I try to run the code on a large amount of postal addresses I get the following error message:
    “Service invoked too many times in a short time: geocode. Try Utilities.sleep(1000) between calls.”
    How would you get around that ?

    1. In case you haven’t already added this — after the if (location.status == 'OK') { block in either addressToPosition or positionToAddress — you’d have to add the Utilities.sleep(1000) call, to slow down the rate at which you’re asking Google to geocode things.

  5. This saved my life. Thank you! Is there a way to lift the daily restriction? I just received this message after doing 1,000 addresses.

    Message details
    Service invoked too many times for one day: geocode.

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

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