Geocoding Postal Addresses with Google Maps API

March 20, 2017

Let’s say you have a bunch of postal address in a spreadsheet and you wish to geocode these addresses (get the latitude and longitude information).

While there are several web apps available that that can geocode addresses, one of the easier options is available inside Google Apps Script.

Just past the list of postal address in a column in Google Docs spreadsheet and then run the following Google Apps Script to transform them into latitudes and longitudes.

function geocode_Addresses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var locationInfo = sheet.getRange(1, 1, sheet.getLastRow() - 1, 1).getValues();
  var geoResults, lat, lng;

  for (var i = 0; i < locationInfo.length; i++) {
    geoResults = Maps.newGeocoder().geocode(locationInfo[i]);

    // Get the latitude and longitude
    lat = geoResults.results[0].geometry.location.lat;
    lng = geoResults.results[0].geometry.location.lng;
    sheet.getRange(i + 1, 2, 1, 1).setValue(lat + ',' + lng);
    Utilities.sleep(1000);
  }
}

Important: The sleep function is important else Google Apps Script might return an error saying Service Invoked Too Many Times.

Google Apps Script Google Maps

ShareWhatsAppTwitterFacebook

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory