Geocoding Apps Script

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