Created
October 24, 2016 08:12
-
-
Save jhammann/3f4929503884593b60ef3ce7cb84cd56 to your computer and use it in GitHub Desktop.
Google Spreadsheet script for finding lat&lng and/or provinces (NL)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Function to find the latitude and longitude of a location. | |
function geocodeSelectedCells() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var cells = sheet.getActiveRange(); | |
// Must have selected 4 columns (Street, City, Lat and Lng). | |
// Lat and Lng may ofcourse be empty (but you have to select them anyway). | |
// Must have selected at least 1 row. | |
if (cells.getNumColumns() != 4) { | |
Logger.log("Must select the Street, City, Lat and Lng columns."); | |
return; | |
} | |
var streetColumn = 1; | |
var cityColumn = 2; | |
var addressRow; | |
var latColumn = cityColumn + 1; | |
var lngColumn = cityColumn + 2; | |
var geocoder = Maps.newGeocoder().setRegion('nl').setLanguage('nl'); | |
var location; | |
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) { | |
street = cells.getCell(addressRow, streetColumn).getValue(); | |
city = cells.getCell(addressRow, cityColumn).getValue(); | |
address = street + ' ' + city; | |
// Geocode the address and plug the lat, lng pair into the 4th and 5th elements of the current range row. | |
if (street.length) { | |
location = geocoder.geocode(address); | |
} | |
// Only change cells if geocoder seems to have gotten a | |
// valid response. | |
if (street.length) { | |
if (location.status == 'OK') { | |
lat = location["results"][0]["geometry"]["location"]["lat"]; | |
lng = location["results"][0]["geometry"]["location"]["lng"]; | |
// Sleep 1 second to prevent reaching Geocode limit. | |
Utilities.sleep(1100); | |
cells.getCell(addressRow, latColumn).setValue(lat); | |
cells.getCell(addressRow, lngColumn).setValue(lng); | |
} | |
} | |
} | |
}; | |
// Function to find the province of a location. | |
function provinceGeocodeSelectedCells() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var cells = sheet.getActiveRange(); | |
// Must have selected 2 columns (City and Province). | |
// Province may also be empty. | |
// Must have selected at least 1 row. | |
if (cells.getNumColumns() != 2) { | |
Logger.log("Must select the City and Province columns."); | |
return; | |
} | |
var cityColumn = 1; | |
var provinceColumn = cityColumn + 1; | |
var geocoder = Maps.newGeocoder().setRegion('nl').setLanguage('nl'); | |
var location; | |
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) { | |
city = cells.getCell(addressRow, cityColumn).getValue(); | |
// Geocode the city and plug the Province into the 2nd element of the current range row. | |
if (city.length) { | |
location = geocoder.geocode(city); | |
} | |
// Only change cells if geocoder seems to have gotten a | |
// valid response. | |
if (location.status == 'OK') { | |
var province; | |
var components = location.results[0].address_components; | |
for (var componentsIndex = 0; componentsIndex < components.length; componentsIndex++) { | |
if (components[componentsIndex].types[0] === 'administrative_area_level_1') { | |
var component = components[componentsIndex].long_name; | |
province = component; | |
} | |
} | |
if (city.length) { | |
Utilities.sleep(1100); | |
cells.getCell(addressRow, provinceColumn).setValue(province); | |
} | |
} | |
} | |
}; | |
/** | |
* Adds a custom menu to the active spreadsheet, containing a menu item of each function. | |
* | |
* 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() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [ | |
{ | |
name: "Find Province", | |
functionName: "provinceGeocodeSelectedCells" | |
}, | |
{ | |
name: "Find Lat/Lng", | |
functionName: "geocodeSelectedCells" | |
} | |
]; | |
sheet.addMenu("Geocode", entries); | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment