Coordinate to address with Google Drive spreadsheets
Creating a list of addresses if you only have the coordinates is actually possible with Google Spreadsheets. Basically the steps are:
- Create a script to add your own function to the spreadsheet
- In the function body call the Google API to do the reverse geocoding
- Make sure it contains a sleep and cache to keep from calling the API to often
- Add your function to the spreadsheet
- Let it run
- Save the result state by copying the output data and pasting only the values back.
Creating a script
Open a Google Drive spreadsheet and open the Tools menu to select Script editor.... Add a script like the following:
var geocoder = Maps.newGeocoder(),
cache = CacheService.getScriptCache();
function reverseGeocodeLatLngTest() {
Logger.log(reverseGeocodeLatLng(51.70055389, 5.318333149));
}
function reverseGeocodeLatLng(latitude, longitude) {
// var latitude = 51.69048,
// longitude = 5.29362,
var cacheKey = latitude + "+" + longitude,
cached = cache.get(cacheKey);
if(cached !== null) {
return JSON.parse(cached);
}
Utilities.sleep(2000);
var result = geocoder.reverseGeocode(latitude, longitude),
//First response is the most specific
response = result.results[0];
if(response === undefined) {
return "??";
}
function g(fieldName) {
for (var i = 0; i < response.address_components.length; i++) {
if(response.address_components[i].types.indexOf(fieldName) != -1) {
return response.address_components[i];
}
}
return {"long_name": "??"};
}
rValue = [[g("route").long_name, g("street_number").long_name, g("postal_code").long_name, g("locality").long_name, g('country').long_name]];
cache.put(cacheKey, JSON.stringify(rValue));
return rValue;
}
To test the script's working, find the Select function button and select the reverseGeocodeLatLngTest
function. If you hit the play button two buttons left of the Select function field, the function will evaluate.
If you see no errors, open the Logs from the View menu to see the result of your test. The output you see should be a two dimensional array with information on the address.
Save the script using Save from the File menu and go back to your spreadsheet.
Use the function in your spreadsheet
After defining the function in the script editor, you can now use the function like any other spreadsheet function inside your spreadsheet. The function will then fill five columns: the street, housenumber, postal code, city and country.
To test, place the following code in your first column:
=reverseGeocodeLatLng(51.69048;5.29362)
If everything works out, it should say Lodaing... and after a few seconds fill the column and the columns on the right with the result (in this case Stationspasserelle
10
5211 AZ
's-Hertogenbosch
Netherlands
).
Make the result final (remove dynamic lookup)
Because there is a limit on the number of calls you are allowed to make to the Google Maps API, make sure you replace the function call with the result.
Select the result fields (the function column and the other four columns to the right), copy (CTRL
+v
) and putting your cursor on the first column choose Edit, Paste special..., Paste values only. This will override the columns with the result of the function call.