Simple Google Script to GeoCode from address in cell
function getGeocodingRegion() { | |
return PropertiesService.getDocumentProperties().getProperty('GEOCODING_REGION') || 'au'; | |
} | |
function addressToPosition() { | |
// Select a cell with an address and two blank spaces after it | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var cells = sheet.getActiveRange(); | |
var addressColumn = 1; | |
var addressRow; | |
var latColumn = addressColumn + 1; | |
var lngColumn = addressColumn + 2; | |
var API_KEY = "INSERT YOUR API KEY"; | |
var options = { | |
muteHttpExceptions: true, | |
contentType: "application/json", | |
}; | |
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) { | |
var address = cells.getCell(addressRow, addressColumn).getValue(); | |
var serviceUrl = "https://maps.googleapis.com/maps/api/geocode/json?address=" + address + "&key=" + API_KEY; | |
// Logger.log(address); | |
// Logger.log(serviceUrl); | |
var response = UrlFetchApp.fetch(serviceUrl, options); | |
if (response.getResponseCode() == 200) { | |
var location = JSON.parse(response.getContentText()); | |
// Logger.log(response.getContentText()); | |
if (location["status"] == "OK") { | |
//return coordinates; | |
var lat = location["results"][0]["geometry"]["location"]["lat"]; | |
var lng = location["results"][0]["geometry"]["location"]["lng"]; | |
cells.getCell(addressRow, latColumn).setValue(lat); | |
cells.getCell(addressRow, lngColumn).setValue(lng); | |
} | |
} | |
} | |
}; | |
function positionToAddress() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var cells = sheet.getActiveRange(); | |
// Must have selected 3 columns (Address, Lat, Lng). | |
// Must have selected at least 1 row. | |
if (cells.getNumColumns() != 3) { | |
Logger.log("Must select at least 3 columns: Address, Lat, Lng columns."); | |
return; | |
} | |
var addressColumn = 1; | |
var addressRow; | |
var latColumn = addressColumn + 1; | |
var lngColumn = addressColumn + 2; | |
//Maps.setAuthentication("acqa-test1", "AIzaSyBzNCaW2AQCCfpfJzkYZiQR8NHbHnRGDRg"); | |
var geocoder = Maps.newGeocoder().setRegion(getGeocodingRegion()); | |
var location; | |
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) { | |
var lat = cells.getCell(addressRow, latColumn).getValue(); | |
var lng = cells.getCell(addressRow, lngColumn).getValue(); | |
// Geocode the lat, lng pair to an address. | |
location = geocoder.reverseGeocode(lat, lng); | |
// Only change cells if geocoder seems to have gotten a | |
// valid response. | |
Logger.log(location.status); | |
if (location.status == 'OK') { | |
var address = location["results"][0]["formatted_address"]; | |
cells.getCell(addressRow, addressColumn).setValue(address); | |
} | |
} | |
}; | |
function generateMenu() { | |
var entries = [{ | |
name: "Geocode Selected Cells (Address to Lat, Long)", | |
functionName: "addressToPosition" | |
}, { | |
name: "Geocode Selected Cells (Address from Lat, Long)", | |
functionName: "positionToAddress" | |
}]; | |
return entries; | |
} | |
function updateMenu() { | |
SpreadsheetApp.getActiveSpreadsheet().updateMenu('Geocode', generateMenu()) | |
}; | |
/** | |
* Adds a custom menu to the active spreadsheet, containing a single menu item | |
* for invoking the readRows() function specified above. | |
* The onOpen() function, when defined, is automatically invoked whenever the | |
* spreadsheet is opened. | |
* | |
* For more information on using the Spreadsheet API, see | |
* https://developers.google.com/apps-script/service_spreadsheet | |
*/ | |
function onOpen() { | |
SpreadsheetApp.getActiveSpreadsheet().addMenu('Geocode', generateMenu()); | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment