Igor Dobryn
about IT

Google Sheets to find the closest location

A search of the closest location becomes a challenging task if you need to build something more complex than just as a crow flies path. Ideally traveling in a city we should choose between walking, cycling, traveling by car or by public transport and know exactly how much time will it take. All of this is possible in Google Maps, but what if you need to choose between different locations and different traveling modes? The answer would be to build all possible routes including different traveling modes and choose the one which has the best timing. Using API we definitely can avoid lots of manual actions. Let’s walk through how we can do that using Google Sheets and a little of code to call Google Maps API.

In my case, we were choosing the kindergarten within a preferred list. We had some restrictions on time traveling to the destination and considered several traveling modes. It was not as easy to figure out which one is better manually quickly, so I grabbed a list of locations from the offsite and started a new GoogleSheet:

Name Location
Home 65000, г. Одесса, ул. Фонтанская дорога, 41
Kindergarten 1 65049, г. Одесса, ул. Фонтанская дорога, 47
Kindergarten 2 65049, г. Одесса, ул. Сегедская, 2
Kindergarten 3 65058, г. Одесса, ул. М. Говорова, 5
Kindergarten 4 65058, г. Одесса, Французский бульвар, 46

The next step would be to validate locations. This is unnecessary step if you’re sure that your data is correct. I was not. Create a simple script by visiting Tools -> Script Editor menu item and define a function to geocode given address:

function geocodeAddress(address) {
  if (address == '') {
    Logger.log("Must provide an address");

    return;
  }

  var geocoder = Maps.newGeocoder();
  var location = geocoder.geocode(address);

  if (location.status == 'OK') {
    var location = location["results"][0]["geometry"]["location"]

    return [[location.lat, location.lng]];
  }

  return '';
}

 
Now we can use this function to calc coordinates in the spreadsheet. We just need to specify a proper formula for the column and it will populate a lat and adjacent right column with an lng:

=geocodeAddress(B2)
Name Location Lat Lng
Home 65000, г. Одесса, ул. Фонтанская дорога, 41 46.434631 30.7490668
Kindergarten 1 65049, г. Одесса, ул. Фонтанская дорога, 41 46.4328576 30.7491904
Kindergarten 2 65049, г. Одесса, ул. Сегедская, 2 46.4529604 30.7435382
Kindergarten 3 65058, г. Одесса, ул. М. Говорова, 5 46.4518486 30.7470723
Kindergarten 4 65058, г. Одесса, Французский бульвар, 46 46.4469703 30.7629324

Let’s quickly review coordinates. They should be close to each other as they’re from the same city. If not then it's probably geocoded wrong.

We have a home location and set of destination locations - it’s enough to calc route durations and distances depending on traveling mode:

function getRouteBetween(originLat, originLon, destLat, destLon, mode) {
  var directions = Maps.newDirectionFinder()
    .setOrigin(originLat, originLon)
    .setDestination(destLat, destLon)
    .setMode(mode)
    .getDirections(); // Direction Object

  if(directions.routes[0]){
    var route = directions.routes[0].legs.reduce(function(acc, currentRow){
      acc.distance += currentRow.distance.value/1000;
      acc.duration += currentRow.duration.value/60;

      return acc;
    },{ distance :0, duration: 0 });

    return [[route.duration, route.distance]];
  } else {

    return '';
  }
}

 
Let’s extract few more functions for usability:

function getDrivingRouteBetween(originLat, originLon, destLat, destLon) {
  return getRouteBetween(originLat, originLon, destLat, destLon, Maps.DirectionFinder.Mode.DRIVING)
}

function getWalkingRouteBetween(originLat, originLon, destLat, destLon) {
  return getRouteBetween(originLat, originLon, destLat, destLon, Maps.DirectionFinder.Mode.WALKING)
}

function getTransitRouteBetween(originLat, originLon, destLat, destLon) {
  return getRouteBetween(originLat, originLon, destLat, destLon, Maps.DirectionFinder.Mode.TRANSIT)
}

 
At this point getRouteBetween return array of distance + travelling time which is not very human readable. A little of formatting:

function formatDuration(duration) {
  return Math.round(duration) + ' min';
}

function formatDistance(distance) {
  return Math.round(distance * 10) / 10.0 + ' km';
}

 
And update the result of getRouteBetween to return string value:

return formatDuration(route.duration) + ', ' + formatDistance(route.distance);

 
Add 3 more columns to our table:

=getTransitRouteBetween($C$2,$D$2,C2,D2)
=getWalkingRouteBetween($C$2,$D$2,C2,D2)
=getDrivingRouteBetween($C$2,$D$2,C2,D2)

 
And spread this formula to all rows

Name Location Transit Walking Driving
Home 65000, г. Одесса, ул. Фонтанская дорога, 41 0 min, 0 km 0 min, 0 km 0 min, 0 km
Kindergarten 1 65049, г. Одесса, ул. Фонтанская дорога, 41 5 min, 0.4 km 5 min, 0.4 km 4 min, 0.6 km
Kindergarten 2 65049, г. Одесса, ул. Сегедская, 2 20 min, 2.7 km 28 min, 2.3 km 8 min, 2.5 km
Kindergarten 3 65058, г. Одесса, ул. М. Говорова, 5 26 min, 3.2 km 28 min, 2.2 km 9 min, 2.7 km
Kindergarten 4 65058, г. Одесса, Французский бульвар, 46 25 min, 3 km 29 min, 2.3 km 10 min, 2.5 km

Basically, this would be enough to find the best route, but let’s keep improving and add link Google Map. Therefore define a function to build URL:

function googleMapRouteUrl(originLat, originLon, destLat, destLon, mode) {
  var dataMap = { };

  dataMap[Maps.DirectionFinder.Mode.DRIVING] = '!4m2!4m1!3e0';
  dataMap[Maps.DirectionFinder.Mode.WALKING] = '!4m2!4m1!3e2';
  dataMap[Maps.DirectionFinder.Mode.TRANSIT] = '!4m2!4m1!3e3';

  return "https://www.google.com/maps/dir/" + originLat + "," + originLon + "/" + destLat + "," + destLon + "/data=" + dataMap[mode];
}

 
and 3 wrapper functions for each travelling mode:

function googleMapDrivingRouteUrl(originLat, originLon, destLat, destLon) {
  return googleMapRouteUrl(originLat, originLon, destLat, destLon, Maps.DirectionFinder.Mode.DRIVING)
}

function googleMapWalkingRouteUrl(originLat, originLon, destLat, destLon) {
  return googleMapRouteUrl(originLat, originLon, destLat, destLon, Maps.DirectionFinder.Mode.WALKING)
}

function googleMapTransitRouteUrl(originLat, originLon, destLat, destLon) {
  return googleMapRouteUrl(originLat, originLon, destLat, destLon, Maps.DirectionFinder.Mode.TRANSIT)
}

 
Update formulas:

=HYPERLINK(googleMapTransitRouteUrl($C$2,$D$2,C2,D2), getTransitDistanceBetween($C$2,$D$2,C2,D2))
=HYPERLINK(googleMapWalkingRouteUrl($C$2,$D$2,C2,D2), getWalkingDistanceBetween($C$2,$D$2,C2,D2))
=HYPERLINK(googleMapDrivingRouteUrl($C$2,$D$2,C2,D2), getDrivingDistanceBetween($C$2,$D$2,C2,D2))

 
After refresh, we have a better-formatted table

Name Location Transit Walking Driving
Home 65000, г. Одесса, ул. Фонтанская дорога, 41 0 min, 0 km 0 min, 0 km 0 min, 0 km
Kindergarten 1 65049, г. Одесса, ул. Фонтанская дорога, 41 5 min, 0.4 km 5 min, 0.4 km 4 min, 0.6 km
Kindergarten 2 65049, г. Одесса, ул. Сегедская, 2 20 min, 2.7 km 28 min, 2.3 km 8 min, 2.5 km
Kindergarten 3 65058, г. Одесса, ул. М. Говорова, 5 26 min, 3.2 km 28 min, 2.2 km 9 min, 2.7 km
Kindergarten 4 65058, г. Одесса, Французский бульвар, 46 25 min, 3 km 29 min, 2.3 km 10 min, 2.5 km

Perfect, all routes, durations and distances are there. Sometimes you may encounter Error in a cell. This is due to too many requests to Google API. Just refresh the cell. This is why we need to copy all the data and insert as a plain values to a separate sheet.

The last thing to do is to find the best destination. Choose the traveling mode and use built-in Google sheets function to sort rows in menu Data -> Sort Range

 

Conclusions

We needed to find the best destination within a given list. That required reviewing a bunch of routes with different traveling modes. This is a time-consuming process when there are more than just a few locations. Actually, before the script, I started with a manual process, but after some time equivalent to writing the whole script, I realized that my home location is wrong. I had to start from scratch.

Google sheets are more than just tables. It provides a good opportunity to write custom functions with all the power of calling APIs. It was a great fit for this kind of task even though this was a way not experienced before. Eventually, it took me less time writing the script than building every route manually. As a result, I have an approach that is scalable to any number of locations.

P.S. The home location specified here is not my real home location. It's a nice cafe to work in.

Google Sheets Google Maps API Javascript Location Search