Geocode Restaurant Data in Google Sheet Appscript

geocode_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) + '.' );   
    
  }
};