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 returns 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 benefit 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 turned on or to use any other servers with this method.

When you set time intervals 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

I will explain this in two parts as below.

First, I will explain to you how to use the Google Sheet given below to collect Google Travel Time Data. For this part, you do not need any knowledge of coding.

Then in the second part, I will explain the code attached to this Google Sheet briefly. You can modify this code and get custom results.

How to use this Google Sheet to get Travel Time and Distance.

First make a copy of the following Google sheet to your Google Drive.

This Google Sheet contains three sheets, namely “inputs“, “Outputs” and “SetTriggers“. Make a copy of this Google Sheet to your Drive.

Google Sheets1
  1. Copy the above Google Sheet to your Google Drive.
  2. Go to Inputs sheet.
  3. Add route description to column “A
  4. Add route number in column “B“. This will use to identify each route you defined here in the Outputs sheet.
  5. Add coordinates of Origin and Destination.
    • To get the coordinates, zoom in the Google Map completely and right-click in the specific location on the road, and copy the coordinates.
    • You need to provide latitude and longitude as comma-separated values (Ex: 6.899389906552246,79.92649007087084)
  6. Add waypoints, if necessary (when you provide only the origin and destination, Google will provide the route with the least travel time, this may choose alternate routes depending on the traffic condition. If you want the travel time across a specific route, you can define it using waypoints). Waypoints also should be added as latitude and longitude as comma-separated values.
  7. To check whether everything is okay, go to the “Google Travel Timecustom menu and click “Run“.
  8. Google will ask you to grant access to run the code in your Google Account. Allow Access. Again click “Run” in the “Google Travel Time” custom menu.
  9. Now, go to the “Outputs” sheet. If you have followed the above steps correctly, you will see one data is inserted in the Outputs sheet.
  10. To automate the data collection,
    • Go to the sheet SetTriggers, and enter the starting and end hours in 24-hour format in cells B1 and B2, respectively.
    • Enter the running intervals in minutes in cell B6. The running intervals must be one of 1, 5, 10, 15, or 30.
    • Then go to the custom menu “Google Travel Time” and select “Set Triggers“.
  11. Check the “Output” sheet and you can see the travel time data is appended to the list in the interval you defined in the “SetTriggers” sheet.

Explaining the Code

When you click the items in the Custom Menu of the above Google Sheet, it will run some JavaScript functions attached to it. These codes are in a separate application called Google Apps Script.

To access the Apps Script file, go to “Tools” and select “Script editor” in the above Google Sheet.

The Code

When you open the script edit as explained above, you will see the following code in the Code.gs file.

//CREATING CUSTOM MENU
function onOpen() { 
  var ui = SpreadsheetApp.getUi();
  
  ui.createMenu("Google Travel Time")
    .addItem("Run","getDistance")
    .addItem("Set Triggers","createEveryMinutesTrigger")
    .addItem("Delete Triggers","deleteTrigger")
    .addToUi();
}

// GET TRAVEL TIME AND DISTANCE FOR EACH ORIGIN AND DESTINATION
function getDistance() {
  
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var inputSheet = ss.getSheetByName("Inputs");
   var range = inputSheet.getRange("B2:I");
   var inputs = range.getValues();
   var outputSheet = ss.getSheetByName("Outputs");
   var recordcount = outputSheet.getLastRow();
   var timeZone = "GMT+5:30";
   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() //https://www.thetopsites.net/article/51638880.shtml
     .setOrigin(inputs[i][1])
     .setDestination(inputs[i][2])
     .setDepart(now)
     .setMode(Maps.DirectionFinder.Mode["DRIVING"]); //https://developers.google.com/apps-script/reference/maps/direction-finder
    
    var wayCount = inputs[i][7];
    
    for(j=0;j<wayCount;j++){
      setDirections.addWaypoint("via:"+inputs[i][3+j]);
    }
    
    var directions = setDirections.getDirections();
   
    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];
    
    
    outputSheet.getRange(i+1+recordcount,1).setValue(route);
    outputSheet.getRange(i+1+recordcount,2).setValue(now);
    outputSheet.getRange(i+1+recordcount,3).setValue(secToMin(traveltime));
    outputSheet.getRange(i+1+recordcount,4).setValue(distance/1000);
    outputSheet.getRange(i+1+recordcount,5).setValue((distance/traveltime)*(3600/1000));
    outputSheet.getRange(i+1+recordcount,6).setValue(traveltime);
    outputSheet.getRange(i+1+recordcount,7).setValue(rDate);
    outputSheet.getRange(i+1+recordcount,8).setValue(rTime);
  }
}


////RUN FUNCTION EVERY n MINUTES BETWEEN GIVEN TIME DURATION
function runGetDistance() {
  var date = new Date();  
  var day = date.getDay();
  var hrs = date.getHours();
  var min = date.getMinutes();
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputSheet = ss.getSheetByName("SetTriggers");
  var startHour = inputSheet.getRange("B1").getValue();
  var endHour = inputSheet.getRange("B2").getValue();
  
  if ((hrs >= startHour) && (hrs <= endHour) && (min >= 0) && (min <= 59 )) { //https://stackoverflow.com/a/39903751
    getDistance();
  }
}


//CREATE TRIGGER  
function createEveryMinutesTrigger(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var inputSheet = ss.getSheetByName("SetTriggers");
  var runningInterval = inputSheet.getRange("B6").getValue();
  
  
  ScriptApp.newTrigger("runGetDistance")
    .timeBased()
    .everyMinutes(runningInterval)
    .create();
}


//DELETE TRIGGER
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]);
  }
}

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

Class DirectionFinder

You can get direction data from Google Maps using Google Maps APIs. In Google Apps Script you can use the DirectionFinder class 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. 

Input data

The “origin” and “destination” parameters are compulsory to retrieve direction data. In addition to that here, I am using departure time, waypoints, and mode to get more specific results. And also I use start hour, end our, and running intervals to set the triggers to run the code automatically.

To get the direction details, you need to add these input data in the “Inputs” and “SetTriggers” sheets of the Google Sheet as explained above.

These inputs are assigned to variables in the code and you can see them from lines 15-25 of the above code.

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 real-time data. If you want to get future travel time you need to provide a future date and time instead of the “now” variable in line 22 of the code. The departure time cannot be earlier than the current time. If you provide an 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 four (4) waypoints. If no waypoint is added Code will return the route which has the best travel time. In the “Input” sheet of the above Google Sheet, you can see it calculates the waypoint count. Do not delete the function in this column since it is used in the program. When you add more rows, make sure to copy down the formula.

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

You can create custom menu items in Google Sheet. We use one custom menu with three sub-menus in this Google Sheet. When you create a custom menu item, it will appear at the end of the toolbar menu.

In the above code, line 2-10, the onOpen() function creates a custom menu in your Google Sheet. You can add your functions in your code as menu items. With them, you can run your functions and you do not need to go to the code every time.

The onOpen() function create a new menu item in your Google Sheet. I have named it as “Google Travel Time“. Under these menu items, it creates sub-menu items namely “Run“, “Set Triggers“, and “Delete Triggers” to run the relevant functions.

Automate the Script to retrieve data at predefined intervals

When you run the above getDistance() function once, it will return the travel time for that specific time you hit the run button. You can automate this task and let the function run at predefined time 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.

Creating Triggers

You can set triggers either programmatically or by going to “G Suite Developer Hub“. The function createEveryMinutesTrigger() in the line, 79-89 of the above code creates time-driven triggers to run the runGetDistance() function in defined intervals.

To make it easy, I have created another input sheet (SetTriggers) in the Google Sheet to define time intervals to run the triggers. So that, anyone can use this code to get Google Map travel time without touching the code.

The function runGetDistance() looks for the start hour and end hour values in the sheet SetTriggers. Inside this function we call the getDistance() function.

I have added this createEveryMinutesTrigger() function as a sub-menu item in the custom menu mentioned above.

Accordingly, to set the triggers,

! Before setting triggers, you must provide origin and destination details in the “Inputs” sheet as explained above.

  1. Go to the sheet SetTriggers, and enter the starting and end hours in 24-hour format in cells B1 and B2, respectively.
  2. Enter the running intervals in minutes in cell B6. The running intervals must be one of 1, 5, 10, 15, or 30.
  3. Then go to the custom menu “Google Travel Time” and select “Set Triggers“.

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

Deleting Triggers

It is important to remember that the time-driven triggers will be running until you delete them.

The deleteTrigger() function in the line 93-101 of the above code delete all the triggers set previously. I have added this function also as a sub-menu in the custom menu.

To delete all your triggers, go to the Google Travel Time custom menu and select “Delete Triggers

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 conditions 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
close

Oh hi there 👋
It’s nice to meet you.

Sign up to receive awesome content in your inbox, every month.

We don’t spam! Read our privacy policy for more info.

The Ultimate Managed Hosting Platform
0 0 votes
Article Rating
Subscribe
Notify of
guest

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

8 Comments
Inline Feedbacks
View all comments
Yas

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.

Admin

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 🙂

KI LEE

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?

Ike Knuivers

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?)

N

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?

Troy

What’s the easier way to input additional waypoints?

Bryan James

I’m using a version of this, however if a cell still has focus (most recently edited) that data isn’t moved to the datasheet. Is there a way to avoid this?

here is my script:

function submitData() {
 var ss    = SpreadsheetApp.getActiveSpreadsheet();
 var formSS  = ss.getSheetByName(“Form”); //Form Sheet
 var datasheet = ss.getSheetByName(“Data”); //Data Sheet
  
 //Input Values
 var values = [[formSS.getRange(“B2”).getValue(), //FuneralHome
         formSS.getRange(“B4”).getValue(), //B or C
         formSS.getRange(“B6”).getValue(), //Name
         formSS.getRange(“B8”).getValue(), //Visitation Date
         formSS.getRange(“B10”).getValue(), // Visitation Time/location
         formSS.getRange(“E8”).getValue(), //Service Date
         formSS.getRange(“E10”).getValue(), //service time/Location
         formSS.getRange(“E4”).getValue(), //Cemetary
         formSS.getRange(“E6”).getValue(), //Staff
         formSS.getRange(“A14”).getValue()]]; //Notes
  
 datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 10).setValues(values); 
  
 formSS.getRange(“B2”).clear()
 formSS.getRange(“B4”).clear()
 formSS.getRange(“B6”).clear()
 formSS.getRange(“B8”).clear()
 formSS.getRange(“B10”).clear()
 formSS.getRange(“E4”).clear()
 formSS.getRange(“E6”).clear()
 formSS.getRange(“E8”).clear()
 formSS.getRange(“E10”).clear()
 formSS.getRange(“A14”).clear()
}

CyberCowboy

If my selected cell is still on a cell where I input data when I click the ‘save’ image it doesn’t move whatever was in the cell that I have highlighted. Any help there?

shares