Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.