Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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