Automate Google Sheet to get Google Map Travel Time and Distance

With Google sheet app script, (similar to MS Excel VBA) you can extend the functionalities of the Google Sheet. In addition to that, you can automate some tasks using Triggers. In this post, I’m using Time-driven-triggers to automatically run an app script to get Google Map direction data to Google Sheet.

How to get Google Map direction data 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 

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.

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

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.

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

References

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

Leave a Reply

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