Skip to content

Instantly share code, notes, and snippets.

@milesgrimshaw
Created September 23, 2014 18:52
Show Gist options
  • Save milesgrimshaw/5b1cee4e2b6acb1c215f to your computer and use it in GitHub Desktop.
Save milesgrimshaw/5b1cee4e2b6acb1c215f to your computer and use it in GitHub Desktop.
Geocode Restaurant Data in Google Sheet Appscript
function geocode_all() {
// 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 = 0; 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
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