Last active
August 29, 2015 13:57
-
-
Save milesgrimshaw/9414087 to your computer and use it in GitHub Desktop.
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
function addMahZipCodez() { | |
// grab basic sheet data | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var rows = sheet.getDataRange(); | |
var numRows = rows.getNumRows(); | |
var values = rows.getValues(); | |
// init some locals for looping over each row | |
var row; | |
// init some other temp vars | |
var resp, // geocode response | |
address, // address to geocode | |
lat, // lattitude | |
lon, // longitude | |
latlon; // address components from response | |
// setup our geocoder | |
var geo = Maps.newGeocoder(); | |
// loop over all rows in spreadsheet (note: watch out for header row!) | |
for (var i = 9000; i <= numRows - 1; i++) { | |
row = values[i]; | |
address = row[0]; | |
lat = row[1]; | |
// skip if we've already done this row (for repeat runs etc) | |
if (typeof lat === "number"){ continue; } | |
// try and geocode the address | |
resp = geo.geocode(address); | |
// catch failures | |
//TODO: compare status code to actual status enum | |
if (resp.status != 'OK'){ | |
sheet.getRange(i+1, 4, 1, 1).setValue('fail'); | |
Logger.log('failure in row: ' + (i+1) + '.' ); | |
continue; | |
} | |
// response was OK, continue by extracting address components | |
latlon = resp.results[0].geometry.location; | |
lat = latlon['lat']; | |
lon = latlon['lng']; | |
// write back to spreadsheet | |
// 2nd for lat | |
// 3rd for lon | |
// 4th for raw data | |
sheet.getRange(i+1, 2, 1, 1).setValue(lat); | |
sheet.getRange(i+1, 3, 1, 1).setValue(lon); | |
sheet.getRange(i+1, 4, 1, 1).setValue(latlon); | |
Logger.log('finished row: ' + (i+1) + '.' ); | |
} | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment