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.

Using Google Sheets has a great benefit: once you set triggers for the Google Apps Script, the application runs on Google Servers. Therefore, there’s no need to keep your computer turned on or use any other servers with this method.

When you set time intervals and triggers to run the code, the records will be appended to 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 the Inputs sheet.
  3. Add route description to column “A
  4. Add the route number in column “B”. This will 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 Maps completely and right-click on the specific location on the road, and click on the coordinates at the top right-click menu to copy it.
    • You need to provide latitude and longitude as comma-separated values (Ex: 34.06526,-118.24371)
  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.

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 hour, 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 19-22 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 27 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. 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

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, you can see the code related to creating the custom menu in the Menu.gs file. 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 Sheets 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 computer 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, 2 of the above Triggers.gs file 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 Sheets 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 Triggers>Add Trigger

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 Triggers.gs file 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

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

  1. Appreciate a lot your work, code also was easy for understand (I`m not familiar with that kind of code). I have only one question: “Timestamp” vs. “Time”. Which one is the time when the departure takes place? I have my configuration on “GMT +2” (Spain) but both hours are not the same. The difference between the 2 are 3h30min. Is it because Timestamp is configurated (not in the code) to give you GMT+5:30 so I should not worry about that? Thank you for your work!

    Reply
    • I had the same question, and the answer is to go into the spreadsheet settings and change the timezone there for “Timestamp”. This also controls how the script is run from Triggers, so important to change to the timezone of interest.

      Great work to OP and thanks for sharing!

      Reply
    • I stand corrected… by myself – you will need to go into:

      Extensions -> Apps Script

      and change the timezone there as well, which is what is used for the Triggers. The timezone on the Spreadsheet file does change the timezone for “Timestamp” as described above.

      Reply
  2. 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?

    Reply
  3. 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()
    }

    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
  5. 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
  6. 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
    • Change the l.48
      var traveltime = directions.routes[0].legs[0].duration_in_traffic.value;
      into
      var traveltime = directions.routes[0].legs[0].duration.value;

      The property duration_in_traffic doesn’t exist for TRANSIT mode

      Reply
  7. 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

Leave a Comment

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

Share via
Copy link