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.

• How to use this Google Sheet to get Travel Time and Distance.
• Explaining the Code

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.

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 Time” custom 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, setDepart, addWaypoint, getDirections, 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