function geoAPISelectedCells() {
var sheet = SpreadsheetApp.getActiveSheet();
var addresses = sheet.getActiveRange();
// We expect only the column to be encoded selected
if (addresses.getNumColumns() == 0) {
Browser.msgBox("Please select a address/location column to encode");
} else if (addresses.getNumColumns() != 1) {
Browser.msgBox("Please select only one address/location column to encode");
return;
}
// Find where to put results
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var headerValues = headerRange.getValues();
var pidColumn = -1;
var fadColumn = -1;
var streetNumColumn = -1;
var streetColumn = -1;
var cityColumn = -1;
var unitColumn = -1;
var stateColumn = -1;
var zipColumn = -1;
var countryColumn = -1;
var latColumn = -1;
var lngColumn = -1;
var neighborhoodColumn = -1;
var countyColumn = -1;
var row = null;
for (row in headerValues) {
for (var col in headerValues[row]) {
if (headerValues[row][col].toLowerCase() == "lat") {
latColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "lng") {
lngColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "projectid") {
pidColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "fulladdress") {
fadColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "streetnumber") {
streetNumColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "street") {
streetColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "city") {
cityColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "state") {
stateColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "zip") {
zipColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "country") {
countryColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "neighborhood") {
neighborhoodColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "county") {
countyColumn = parseInt(col) + 1;
} else if (headerValues[row][col].toLowerCase() == "unit") {
unitColumn = parseInt(col) + 1;
}
}
}
// Let's Encode
row = 1;
// Skip header if selected
if (addresses.getRow() == 1) {
++row;
}
var cell = null;
for (row; row <= addresses.getNumRows(); ++row) {
cell = addresses.getCell(row, 1);
var address = cell.getValue();
// Geocode the address
if (address == "") {
continue;
}
//var location = geocoder.geocode(address);
try {
var location = UrlFetchApp.fetch('https://maps.googleapis.com/maps/api/geocode/json?address=' + address + '&key=YOUR-API-KEY');
//Logger.log("location: " + location);
var json = location.getContentText();
var data = JSON.parse(json);
// Only change cells if geocoder seems to have gotten a valid response.
if (data.status == 'OK') {
lat = data["results"][0]["geometry"]["location"]["lat"];
lng = data["results"][0]["geometry"]["location"]["lng"];
fulladr = data["results"][0]["formatted_address"];
var number = "";
var street = "";
var unit = "";
var city = "";
var state = "";
var zip = "";
var country = "";
var neighborhood = "";
var county = "";
var address_comp = data["results"][0]["address_components"];
for (var i=0; i<address_comp.length; i++) {
if(address_comp[i].types[0] == "street_number") number = address_comp[i].long_name;
if(address_comp[i].types[0] == "route") street = address_comp[i].long_name;
if(address_comp[i].types[0] == "locality") city = address_comp[i].long_name;
if(address_comp[i].types[0] == "administrative_area_level_1") state = address_comp[i].short_name;
if(address_comp[i].types[0] == "postal_code") zip = address_comp[i].short_name;
if(address_comp[i].types[0] == "country") country = address_comp[i].short_name;
if(address_comp[i].types[0] == "neighborhood") neighborhood = address_comp[i].short_name;
if(address_comp[i].types[0] == "administrative_area_level_2") county = address_comp[i].short_name;
if(address_comp[i].types[0] == "subpremise") unit = address_comp[i].short_name;
}
var ifunit = "";
if(unit != "") {ifunit = "unit" + unit}
var prid = number + '-' + street + '-' + ifunit + '-' + city + '-' + state;
projectid = prid.toLowerCase().replace(/ /g,'-').replace(/[-]+/g, '-').replace(/[^\w-]+/g,'');
setValue(sheet, cell.getRow(), pidColumn, projectid);
setValue(sheet, cell.getRow(), fadColumn, fulladr);
setValue(sheet, cell.getRow(), streetNumColumn, number);
setValue(sheet, cell.getRow(), streetColumn, street);
setValue(sheet, cell.getRow(), unitColumn, unit);
setValue(sheet, cell.getRow(), cityColumn, city);
setValue(sheet, cell.getRow(), stateColumn, state);
setValue(sheet, cell.getRow(), zipColumn, zip);
setValue(sheet, cell.getRow(), countryColumn, country);
setValue(sheet, cell.getRow(), neighborhoodColumn, neighborhood);
setValue(sheet, cell.getRow(), countyColumn, county);
setValue(sheet, cell.getRow(), latColumn, lat);
setValue(sheet, cell.getRow(), lngColumn, lng);
Utilities.sleep(2);
}
} catch(e) {
// if the script code throws an error,
// do something with the error here
}
}
function setValue(sheet, row, column, val) {
sheet.getRange(row, column).setValue(val);
}
}
Now we are encountering an "Exceeded maximum execution time" error after about 130 rows. What do you think is the best way of handling this?
Hello,
First - thank you for sharing this, very useful. We've modified the script to use googleapis for more than 2500 calls per day, and return all fields.
`
/* Usage: Select a continuous column of address and select Add-Ons->Geocode Selected Cells
* Encoding will put the results in the corresponding columns
* Based off of: https://github.com/mlucool/geocode-google-sheets/blob/master/Geocode.gs
*/
`
Now we are encountering an "Exceeded maximum execution time" error after about 130 rows. What do you think is the best way of handling this?