How to quickly geocode a list of addresses using Google Apps Script

Recently I discovered a powerful tool from the awesome Google Developers Youtube channel: Google Apps Script.

I tried it writing a simple, but powerful example, experimenting with a use case that for sure will be useful to me in the future: geocoding a list of addresses taken from a spreadsheet.

If you are new to Google App Script (like I was) here you can see an introductory video tutorial.

Another tutorial explains how to integrate Google Sheets with Google Maps.

I started from there and I created a script to geocode (that is going from an human readable address to the geographical coordinates of the location) a list of addresses written in a Google Sheets.

To test the script just create a new Spreadsheet in Google Drive, and write down a list of addresses in the first column, like this:

google_app_script1

Then click on Tools > Script editor… and write this code in the editor:

Click run, save the script with a name, allow the script to write data on your document, and ta-dah, your addresses will be geocoded and coordinates will appear in the second and third column!

google_app_script2

Google Apps Script is not limited to Sheets and Maps data; in the documentation you’ll find all services that can be used in your scripts. You can integrate GMail, Google Calendar, Docs, Drive, Forms and more; you can also connect to external public APIs from your script.

Advertisements

9 thoughts on “How to quickly geocode a list of addresses using Google Apps Script

  1. Thank you for this.

    I have an issue when running the script in that if Google doesn’t recognise an address it gives an error
    TypeError: Cannot read property “geometry” from undefined. (line 15, file “”)

    This is all new to me so no idea.
    Any suggestions for a work around?

    • Hey Shane, wrap lines 15 and 16 in an if(res){

      if(res){
      latitudes.push([res.geometry.location.lat]);
      longitudes.push([res.geometry.location.lng]);
      }
      }

  2. Sorry, my first response was a little incomplete:

    if(res){
    latitudes.push([res.geometry.location.lat]);
    longitudes.push([res.geometry.location.lng]);
    }
    else
    {
    latitudes.push([“0”]);
    longitudes.push([“0”]);
    }

  3. Sorry but with tne new “version”

    function myFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();

    var range = sheet.getDataRange();
    var cells = range.getValues();

    var latitudes = [];
    var longitudes = [];

    for (var i = 0; i < cells.length; i++) {
    var address = cells[i][0];
    var geocoder = Maps.newGeocoder().geocode(address);
    var res = geocoder.results[0];

    if(res){
    latitudes.push([res.geometry.location.lat]);
    longitudes.push([res.geometry.location.lng]);
    }
    else
    {
    latitudes.push([“0”]);
    longitudes.push([“0”]);
    }
    }

    sheet.getRange('B2').offset(0, 0, latitudes.length).setValues(latitudes)
    sheet.getRange('C2').offset(0, 0, latitudes.length).setValues(longitudes);
    }

    i have a new error: "Carattere non valido. (riga 21, file "Codice")Ignora"
    can you helo me?
    thanks

  4. This script doesn’t work as is, continously shows error codes Illegal character. (line 21, file “Code”)
    “latitudes.push([“0”]);” Also Is there a way to create a googlemaps link to each address without doing so manually (i.e. by modifying this script or another? I don’t really require Geocached coords rather just to autolink addresses to google maps links. Any and all help would be greatly appreciated.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s