Skip to content

Instantly share code, notes, and snippets.

@bencooper222
Last active April 4, 2017 21:38
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save bencooper222/17cf6988391e88c6e819affba806b428 to your computer and use it in GitHub Desktop.
This script calls the Google Maps geocoding API, gives locations and returns their "locality, province/state" strings. It's a bit glitchy on certain addresses but should meet the bulk of your needs
// this is designed for a 2 columns, n rows range with the addresses in column with index 0
var addresses = SpreadsheetApp.openByUrl("YOUR URL HERE").
getSheetByName("Assignments").getRange("YOUR RANGE HERE").getValues(); // there are several formats for range - your choice
//Logger.log(addresses[0][1])
function searchJson(data){
data = JSON.parse(data);
Logger.log(data);
var components = data.results[0].address_components;
Logger.log(JSON.stringify(components));
var town = "";
var state = "";
for(var i=0; i<components.length; i++){
var types = components[i].types;
Logger.log(types);
if(state!="" && town!=""){
break;
}
for(var j=0; j<types.length; j++){
if (types[j] == "locality"){
Logger.log(types[j]);
town = components[i].long_name;
break;
}
else if (types[j]=="administrative_area_level_1"){
state = components[i].short_name;
break;
}
}
}
return town + ", " + state;
}
function getAddress(index) {
return queryGoogle(addresses[index][0]);
}
function main(){
//Logger.log(searchJson(getAddress(1)));
// apps script has an execution limit so I abbreivated it somewhat
var lower = 35;
var upper = 39;
var result = "";
for(var i=lower; i<upper;i++){
Logger.log(i);
result+=searchJson(getAddress(i));
result+='\n';
}
// I just needed a series of strings, adjust for your own needs
Logger.log(result);
}
function queryGoogle(address,callback){
var options =
{
"method" : "GET",
"followRedirects" : true,
"muteHttpExceptions": true
};
// wouldn't it be great if we had a real getRequest method?
var queryString = "?address="+address+"&key="+"GET YOUR OWN KEY";
var request = UrlFetchApp.fetch("https://maps.googleapis.com/maps/api/geocode/json"+queryString, options)
return request.getContentText();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment