So You Want to Geocode an Address in Sheets

If you have fewer than a thousand addresses and you need them geocoded quickly and accurately, Google Sheets is generally the best way to do it. Here's how to set up the Apps Script necessary. Here is a demo sheet with everything set up, including the Apps Script code.

Create or open your sheet, and then in the Extensions menu, choose "Apps Script" to open the editor. Erase the placeholder contents in Code.gs and paste this in:

const SHEET_NAME = "data";
const ADDRESS_COLUMN = 2;
const LATITUDE_COLUMN = 3;

function geocode() {
  var coder = Maps.newGeocoder();
  // get a reference to our sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // find out how tall our table is
  var rows = sheet.getDataRange().getNumRows();
  // starting with the second row, below the header...
  for (var i = 1; i < rows; i++) {
    // get the address value
    // the i + 1 is because Sheets rows are 1-indexed, and JS is 0-indexed
    var address = sheet.getRange(i + 1, ADDRESS_COLUMN).getValue();
    // if there's no address, skip it
    if (!address) continue;
    // get any existing coordinate values
    var coords = sheet.getRange(i + 1, LATITUDE_COLUMN, 1, 2);
    var [ lat, lng ] = coords.getValues()[0];
    // if the address already has coordinates, skip it
    if (lat) continue;
    // get the results back from Google
    var { results } = coder.geocode(address);
    if (!results.length || !results[0].geometry) {
      console.log(address, results);
      continue;
    }
    // results is an array of items, with the best match first, so we'll go with that
    // uncomment this line to see what one looks like
    // console.log(results[0]);
    // extract coordinates from the top result object
    var { lat, lng } = results[0].geometry.location;
    // apply those back to the sheet
    coords.setValues([[lat, lng]]);
  }
}

You'll want to update the values at the top with your sheet name, plus the column numbers for the address and the lat/long pair (which should be two separate columns, but we only need the first). To get the column number, you can use the =column() formula in any cell.

Save the Apps Script project. When you do, the "Run" button in the toolbar should be enabled, and the drop-down will probably show "geocode()" in it. If it doesn't, select that from the menu. Then click Run. It will ask for permission to access your account--it's okay to authorize this, since it's only going to run code that we wrote. Then it will loop through all the rows of your table, checking the address cell on each, and getting the lat/long pairs for any empty coordinates from the Maps API. You can safely run this script as many times as you want--it won't overwrite any existing coordinates, if you set them manually, and you can have an row re-coded by deleting the coordinates and running the script again (say, if you changed the address).

thomaswilburn/geocoding.md