Geocode addresses with Google Sheets and Google Geocoding API

Recently I discovered a powerful tool from the awesome Google Developers Youtube channel: Google Apps Script. This will let you integrate Google Apps and APIs easily. In this post I will show an example of integration between Google Sheets and Google Geocoding API.

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.

1. Introduction

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 and particularly with Google Geocode API.

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.

2. Integrating Google Sheets with the Google Geocoding API

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:

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

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];

    var lat = lng = 0;
    if (res) {
      lat = res.geometry.location.lat;
      lng = res.geometry.location.lng;
    }

    latitudes.push([lat]);
    longitudes.push([lng]);
  }

  sheet.getRange('B1')
  .offset(0, 0, latitudes.length)
  .setValues(latitudes);
  sheet.getRange('C1')
  .offset(0, 0, longitudes.length)
  .setValues(longitudes);
}

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!

You can also save other data coming from the Geocoder in the sheet. For example here I save also the complete formatted address returned by the Geocoder in the fourth column:

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

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

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

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

    var lat = lng = 0;
    var formatted_address = '';
    if (res) {
      lat = res.geometry.location.lat;
      lng = res.geometry.location.lng;
      formatted_address = res.formatted_address;
    }

    latitudes.push([lat]);
    longitudes.push([lng]);
    formatted_addresses.push([formatted_address]);
  }

  sheet.getRange('B1')
  .offset(0, 0, latitudes.length)
  .setValues(latitudes);
  sheet.getRange('C1')
  .offset(0, 0, longitudes.length)
  .setValues(longitudes);
  sheet.getRange('D1')
  .offset(0, 0, formatted_addresses.length)
  .setValues(formatted_addresses);
}

Please refer to the documentation to know what other data are present in the object returned by the Geocoder.

3. Conclusion

In this tutorial you learned how to use the Google Geocoding API to automatically geocode addresses on a Google Sheets file.

Google Apps Script can do a lot more than integrating Google Sheets and Google Geocoding API. 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 scripts.

Have fun with Google Apps Script! 😉