Last active
April 4, 2017 21:38
Star
You must be signed in to star a gist
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 file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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