Batch Geocode - Google App Script

gistfile1.js

/*****************************************************************************\
* Batch Spreadsheet Geocoding Script                                          *
* Author: Alan Christopher Thomas                                             *
* http://alanchristopherthomas.com/                                           *
\*****************************************************************************/

function onOpen() {
    // Add the Geocode menu
    SpreadsheetApp.getActiveSpreadsheet().addMenu("Geocoder", [{
        name: "Geocode addresses",
        functionName: 'geocode'
    }]);
}

function geocode() {
    // Get the current spreadsheet, sheet, range and selected addresses
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = SpreadsheetApp.getActiveRange();
    var addresses = range.getValues();

    // Determine the first row and column to geocode
    var row = range.getRow();
    var column = range.getColumn();

    // Set default destination columns
    var destination = new Array();
    destination[0] = column + 1;
    destination[1] = column + 2;

    // Prompt for latitude and longitude columns
    var response = Browser.inputBox("Coordinate Columns",
        "Please specify which columns should contain the latitude " +
        "and longitude values [ie. 'C,D', 'A,F', etc]. Leave blank to " +
        "insert new columns.",
        Browser.Buttons.OK_CANCEL);
    if (response == 'cancel') return;
    if (response == '')
        sheet.insertColumnsAfter(column, 2);
    else {
        var coord_columns = response.split(',');
        destination[0] = sheet.getRange(coord_columns[0] + '1').getColumn();
        destination[1] = sheet.getRange(coord_columns[1] + '1').getColumn();
    }

    // Initialize the geocoder and set loading status
    var geocoder = Maps.newGeocoder();
    var count = range.getHeight();
    spreadsheet.toast(count + " addresses are currently being geocoded. " +
                      "Please wait.", "Loading...", -1);
  
    // Iterate through addresses and geocode
    for (i in addresses) {
        var location = geocoder.geocode(
            addresses[i]).results[0].geometry.location;
        sheet.getRange(row, destination[0]).setValue(location.lat);
        sheet.getRange(row++, destination[1]).setValue(location.lng);
        Utilities.sleep(200);
    }

    // Remove loading status
    spreadsheet.toast("Geocoding is now complete.", "Finished", -1);
}

Thanks for sharing this gist. I folked it for my use to grab county values.

Nice work. This is great.