Geocoding using Apps Script APIs

Repair.gs

var sheet = SpreadsheetApp.openById(sheetID).getSheetByName("Responses");

var rowConfig = "timestamp name location favorite note lifespan season contact lat lng city zone approve feature".split(" ");

function getCol(row, param) {
  return row[rowConfig.indexOf(param)];
}

function setCol(row, param, value) {
  row[rowConfig.indexOf(param)] = value;
}

function buildAddress(result) {
  var address = {};
  result.address_components.forEach(function(c) {
    var key = c.types[0];
    var val = c.long_name;
    address[key] = val;
  });
  return address;
}

function backfillGeocoding(start) {
  start = start || 0;
  var last = sheet.getLastRow();
  var geocoder = Maps.newGeocoder();
  for (var i = start; i <= last; i++) {
    var range = sheet.getRange(i, 1, 1, rowConfig.length)
    var row = range.getValues()[0];
    var zone = getCol(row, "zone");
    if (!zone) {
      Logger.log("missing zone for row #" + i + JSON.stringify(row));
      //continue;
      var lat = getCol(row, "lat");
      var lng = getCol(row, "lng");
      var location = getCol(row, "location");
      var coded;
      var address;
      if (lat) {
        coded = geocoder.reverseGeocode(lat, lng).results.shift();
      } else if (location != "Location found" && location != "Finding your location...") {
        coded = geocoder.geocode(location).results.shift();
      }
      var address = buildAddress(coded);
      if (!lat) {
        setCol(row, "lat", coded.geometry.location.lat);
        setCol(row, "lng", coded.geometry.location.lng);
      }
      if (address.locality) setCol(row, "city", address.locality);
      setCol(row, "zone", address.country == "United States" ? address.administrative_area_level_1 : address.country);
      Logger.log(row);
      range.setValues([row]);
      //return;
    }
  }
}

function run() {
  backfillGeocoding(1260);
}