Maps API – Script for getting map coordinates from street address in a Google Sheet

This script is to get latitude/longitude coordinates from street addresses listed in a table in a Google Sheet.

I found it a bit challenging to cobble together this script from various others, in particular navigating the results object. See how it goes for you. Paste it into the Google Sheets script editor.

Before you use it:

Addresses were in the K column. Target column for coordinates was U.

The “setBounds” function limits the area of the search. In this case, the bounds I have set are for New South Wales.

function getLatLong() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 5;
  var lastRow = sheet.getLastRow();           
  var addresses = sheet.getRange("K" + startRow + ":K" + (lastRow - 4)).getValues();
  var range;
  for (var row = startRow; row <= sheet.getLastRow(); row++) {
    try {
      var address = addresses[row - startRow]; // sheet.getRange("K" + (row)).getValue();
      range = sheet.getRange("U" + (row));
      var geo = Maps.newGeocoder()
      .setBounds(-37.248325, 140.807099, -28.130686, 153.595185)
      .geocode(address);
      
      if(geo.status=="OK"){
        var lng = geo.results[0].geometry.location.lng;
        var lat = geo.results[0].geometry.location.lat;
        sheet.setActiveRange(range).setValue(lat + ', ' + lng);
      } else {
        sheet.setActiveRange(range).setValue('nope');
      }
    }
    catch(err){
      sheet.setActiveRange(range).setValue(err);
      return;
    }
  }
}