mlucool / geocode-google-sheets Goto Github PK
View Code? Open in Web Editor NEWA google apps script to geocode addresses to lat/lng using the google geocode api.
License: MIT License
A google apps script to geocode addresses to lat/lng using the google geocode api.
License: MIT License
Awesome script–thanks for sharing!
I ran into an error after ~175 addresses were geocoded. However, the error doesn't provide any context: Action not allowed
. The details of the message say the same.
Any ideas what could be happening? Did I hit an API limit?
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
*/
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?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.