Automate Google Sheet to get Google Map Travel Time and Distance

Using the DirectionFinder class of Google Apps script, you get directions between locations from Google Maps. This class return several details, including travel time and distance between the given two locations. In this tutorial, I will show you how to get Google Map Travel Tim and Distance to Google Sheets automatically using Google Apps Script.

You can also develop this type of application with Microsoft Excel or some other programs to Get Google Map travel time and distance. You can use Google Maps APIs for that.

The benefits of using Google Sheets for this is, once you set Triggers for the Google Apps Script, the application runs on Google Servers. So you don’t need to keep your computer on or to use any other servers with this method.

When you set time interval and set Triggers to run this code, it will append the records to the Google Sheets.

How to get Google Map Travel Time and Distance to Google Sheet

For this purpose, we can use Class DirectionFinder to retrieve direction data between two locations. 

The class methods, setOrigin, setDestination, setDepartaddWaypointgetDirections, and setMode, are used in the following code. The getDirections method returns an object which includes the data we seek. 

Setting up input data

The “origin” and “destination” parameters are compulsory to retrieve directions data. In addition to that here, I am using departure time, waypoints and mode to get more specific results. The input data is obtained from a Google sheet. 

For the origin, destination and waypoint you can provide either location name or coordinates (both latitude and longitude together and separated by a comma).

The sample Google sheet is available here

This sample Google Sheet includes Input Sheet, Output Sheet and, the Script. You can view the script at Tools> Script Editor. Make a copy of this workbook to your drive to use it itself (File > Make a copy…).

The Script 

function getGoogleMapDirections() {
  
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var inputSheet = ss.getSheetByName("Inputs");
   var inputs = inputSheet.getRange("B2:I").getValues();
   var outputSheet = ss.getSheetByName("Outputs");
   var recordcount = outputSheet.getLastRow();
   var timeZone = "GMT+5:30"; //Change to your time-zone
   var now = new Date();
   var rDate = Utilities.formatDate(now, timeZone, "MM/dd/yyyy");
   var rTime = Utilities.formatDate(now, timeZone, "HH:mm:ss");
   var numberOfRoutes = inputSheet.getLastRow()-1;
  
  
  for(i=0;i<numberOfRoutes;i++){
   var setDirections = Maps.newDirectionFinder()
     .setOrigin(inputs[i][1])
     .setDestination(inputs[i][2])
     .setDepart(now)
     .setMode(Maps.DirectionFinder.Mode["DRIVING"]);//Optional
    
    //Setting waypoints
    var wayCount = inputs[i][7];
   
    for(j=0;j<wayCount;j++){
      setDirections.addWaypoint("via:"+inputs[i][3+j]);
    }
    
    var directions = setDirections.getDirections(); //Direction Response
   
    var traveltime = directions.routes[0].legs[0].duration_in_traffic.value;
    var distance = directions.routes[0].legs[0].distance.value;
    var route = inputs[i][0];
    var groute = directions.routes[0].summary;
    
    
    outputSheet.getRange(i+1+recordcount,1).setValue(route);
    outputSheet.getRange(i+1+recordcount,2).setValue(rDate);  
    outputSheet.getRange(i+1+recordcount,3).setValue(rTime); 
    outputSheet.getRange(i+1+recordcount,4).setValue(secToMin(traveltime));
    outputSheet.getRange(i+1+recordcount,5).setValue(distance/1000);
    outputSheet.getRange(i+1+recordcount,6).setValue(groute);
  }
}

//Function to convert Seconds to Minutes
function secToMin(duration){
  var minutes = parseInt((duration/60));
  var seconds = parseInt(duration%60);
  
  return "00:"+minutes+":"+seconds;
} 

Changing the time zone

In the script I have set the time zone to “GMT+5:30”. Change it as appropriately.

Changing the departure time

The script is written to give the real-time data. If you want to get future travel time you have to provide a future date and time instead of “now” variable in the code. The departure time cannot be earlier than the current time.  If you provide earlier time Google will return an error.

Adding waypoints

If you want to define your route more precisely you can add waypoints in between origin and destination. the sample Sheet is designed to provide up to 4 waypoints. If no waypoint is added Code will return the route which has the best travel time.

Retrieving Typical Travel Time

The above script returns the real time data considering the current traffic condition. If you want to get typical travel time for your route, replace the duration_in_traffic method with the duration method.

 Creating a custom menu to run the Script

Adding Custom Menu in Google Sheet to Run App Script

Instead of going to script editor every time, you can create a custom menu in the Google Sheet to run the script. The following code will add a custom menu called “getGoogleMapDirections” when you open the Spread Sheet.

//Creating custom menu
function onOpen() { 
  var ui = SpreadsheetApp.getUi();
  
  ui.createMenu("getGoogleMapDirections")
    .addItem("Run","getGoogleMapDirections")
    .addToUi();
}

Automate the Script to retrieve data at predefined intervals

This is one of the reasons you may use Google Sheet instead of Microsoft Excel to do this job. You can use “Time-Driven Triggers” to automate your script. When you set Triggers correctly, the code will run on the Google cloud and you don’t need to keep your pc power on.

Installable triggers are subject to Apps Script trigger quota limits.

Adding Trigger

You can set triggers either programmatically or by going to “G Suite Developer Hub“. The following script creates a time-driven trigger that will run getGoogleMapDirections() function every day around 7:15 AM (+/-15 minutes).

function createTrigger1() {
  ScriptApp.newTrigger('getGoogleMapDirections')
      .timeBased()
      .atHour(7)
      .nearMinute(15)
      .everyDays(1)
      .create();
}

You can also set triggers manually through the script editor by going to Edit>Current project’s triggers

Deleting Trigger

It is important to remember that, the time-driven triggers will be running until you delete them. Run the following script to delete all your triggers.

function deleteTrigger() {
  // Loop over all triggers and delete them
  var allTriggers = ScriptApp.getProjectTriggers();
  
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }
}

You can also delete triggers manually through the script editor by going to Edit>Current project’s triggers

Wrapping Up

In this post, I showed you how to get Google Map to travel time and distance using Google Apps Script DirectionFinder class. You can use this to observe traffic condition over a period of time in a specific road, junction or in a specific area. You can even create a free online dashboard with Google Data Studio to monitor them visually.

References

  1. Build time-based triggers in Apps Script projects to automate programs
  2. Google Map – Developer Guide
  3. Google App Script – Class DirectionFinder

6 thoughts on “Automate Google Sheet to get Google Map Travel Time and Distance”

  1. Hi. Thank you so much for these information. I am not too familiar with Google Scripts, I need to calculate the travel time for too many origins and destinations. My file has more than 10000 points. Can I still use this code? Thank you.

    Reply
    • Thanks, Yas for your comment.
      Actually I haven’t tried this code for such an amount of inputs. However, according to Quotas for Google Services free users (gmail) can run triggers (If you are going to automate) only 90min/day. This code needs 5-10s to run about 15 ODs. So, as a free user, you won’t be able to run such an amount of ODs. Why don’t you try and comment your results here 🙂

      Reply
  2. THANKS A LOT!
    It works like charm.
    Found a little challenge. I tried to change the mode to “TRANSIT” and it doesn’t work. Returns an:
    “typeError: Cannot read property “legs” from undefined.”
    Is there a way to solve this?

    Reply
  3. Great tool. Works great.
    Unfortunately the waypoints do not work at all. That is not a big problem since you can split a route in two and add the result. Would be nice if the waypoints work though (I use this script for routes between 4 an 15 km. That might be the reason?)

    Reply
  4. Overall this works great, but I have had trouble with timing and triggers–the script I set up to check morning commute times runs at the correct time, but the evening times are all over the map–I have set it to run at 16:00 but instead it runs at 22:00. Does anyone have any ideas?

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.