Google Spreadsheet script for finding lat&lng and/or provinces (NL)

geocode_cells.js

// Function to find the latitude and longitude of a location.
function geocodeSelectedCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getActiveRange();

  // Must have selected 4 columns (Street, City, Lat and Lng).
  // Lat and Lng may ofcourse be empty (but you have to select them anyway).
  // Must have selected at least 1 row.

  if (cells.getNumColumns() != 4) {
    Logger.log("Must select the Street, City, Lat and Lng columns.");
    return;
  }

  var streetColumn = 1;
  var cityColumn = 2;
  var addressRow;

  var latColumn = cityColumn + 1;
  var lngColumn = cityColumn + 2;

  var geocoder = Maps.newGeocoder().setRegion('nl').setLanguage('nl');
  var location;

  for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
    street = cells.getCell(addressRow, streetColumn).getValue();
    city = cells.getCell(addressRow, cityColumn).getValue();
    address = street + ' ' + city;
    // Geocode the address and plug the lat, lng pair into the 4th and 5th elements of the current range row.
    if (street.length) {  
      location = geocoder.geocode(address);
    }

    // Only change cells if geocoder seems to have gotten a 
    // valid response.
    if (street.length) {
      if (location.status == 'OK') {
        lat = location["results"][0]["geometry"]["location"]["lat"];
        lng = location["results"][0]["geometry"]["location"]["lng"];
        
        // Sleep 1 second to prevent reaching Geocode limit.
        Utilities.sleep(1100);
        cells.getCell(addressRow, latColumn).setValue(lat);
        cells.getCell(addressRow, lngColumn).setValue(lng);
      }
    }
  }
};

// Function to find the province of a location.
function provinceGeocodeSelectedCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getActiveRange();

  // Must have selected 2 columns (City and Province).
  // Province may also be empty.
  // Must have selected at least 1 row.

  if (cells.getNumColumns() != 2) {
    Logger.log("Must select the City and Province columns.");
    return;
  }

  var cityColumn = 1;
  var provinceColumn = cityColumn + 1;

  var geocoder = Maps.newGeocoder().setRegion('nl').setLanguage('nl');
  var location;

  for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
    city = cells.getCell(addressRow, cityColumn).getValue();
    // Geocode the city and plug the Province into the 2nd element of the current range row.
    if (city.length) {
      location = geocoder.geocode(city);
    }

    // Only change cells if geocoder seems to have gotten a 
    // valid response.
    if (location.status == 'OK') {
      var province;
      var components = location.results[0].address_components;

      for (var componentsIndex = 0; componentsIndex < components.length; componentsIndex++) {
        if (components[componentsIndex].types[0] === 'administrative_area_level_1') {
          var component = components[componentsIndex].long_name;
          province = component;
        }
      }

      if (city.length) {
        Utilities.sleep(1100);
        cells.getCell(addressRow, provinceColumn).setValue(province);
      }
    }
  }
};

/**
 * Adds a custom menu to the active spreadsheet, containing a menu item of each function.
 *
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 *
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {
      name: "Find Province",
      functionName: "provinceGeocodeSelectedCells"
    },
    {
      name: "Find Lat/Lng",
      functionName: "geocodeSelectedCells"
    }
  ];
  sheet.addMenu("Geocode", entries);
};