Coordinate to address with Google Drive spreadsheets

posted on 2015-11-10

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.