Last active
August 9, 2018 09:25
-
-
Save jhammann/d377c003e2506a3c30d07f1ed7a9464d to your computer and use it in GitHub Desktop.
🗺 Geocode Google Spreadsheet Cells which uses OSM Nominatim.
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 geocodeSelectedCells() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var cells = sheet.getActiveRange(); | |
if (cells.getNumColumns() != 4) { | |
Logger.log("Must select the Street, City, Lat and Lng columns."); | |
return; | |
} | |
var streetColumn = 1; | |
var cityColumn = 2; | |
var latColumn = cityColumn + 1; | |
var lngColumn = cityColumn + 2; | |
for (var addressRow = 1; addressRow <= cells.getNumRows(); addressRow += 1) { | |
street = cells.getCell(addressRow, streetColumn).getValue(); | |
city = cells.getCell(addressRow, cityColumn).getValue(); | |
address = street.replace(/ /g, '+') + ',' + city.replace(/ /g, '+'); | |
var response = UrlFetchApp.fetch('https://nominatim.openstreetmap.org/search/?q=' + address + '&format=json&addressdetails=1&limit=1'); | |
var json = response.getContentText(); | |
var data = JSON.parse(json); | |
var lat = data[0].lat.replace('.', ','); | |
var lng = data[0].lon.replace('.', ','); | |
cells.getCell(addressRow, latColumn).setValue(lat); | |
cells.getCell(addressRow, lngColumn).setValue(lng); | |
} | |
} | |
function onOpen() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [ | |
{ | |
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