how to get travel directions in google sheets
IN SUMMARY
Google Sheets allows you to fetch travel directions, distances, and travel times directly from Google Maps using the Travel Formulas add-on. This add-on provides four formulas: TRAVEL_TIME, TRAVEL_DISTANCE, TRAVEL_ROUTE_URL, and TRAVEL_DIRECTIONS, which can be used to retrieve various travel-related information between two locations.
Setting Up the Travel Formulas Add-on
Go to Extensions > Add-ons > Get add-ons and search for 'Travel Formulas'. Install the add-on and enable it for your spreadsheet by going to Extensions > Travel Formulas > Enable in workbook.
In your spreadsheet, enter the origin and destination locations as addresses or latitude/longitude coordinates. You can either type them directly into the formula or reference cells containing the locations.
Depending on the formula you use, you can provide additional parameters such as mode of travel (driving, walking, biking, or transit), departure time, and units for distance measurement.
Using the Travel Formulas
This formula returns the estimated travel time between the origin and destination. For example: =TRAVEL_TIME(A2, B2, "driving", "3pm") will give you the driving time between the locations in cells A2 and B2, assuming a departure time of 3 PM.
This formula calculates the distance between the origin and destination. For example: =TRAVEL_DISTANCE(A2, B2, "biking", "km") will give you the biking distance between the locations in cells A2 and B2, measured in kilometers.
This formula generates a URL that opens Google Maps with directions between the origin and destination. For example: =TRAVEL_ROUTE_URL(A2, B2, "walking") will create a link that opens Google Maps with walking directions between the locations in cells A2 and B2.
This formula returns an array of step-by-step directions between the origin and destination. For example: =TRAVEL_DIRECTIONS(A2, B2) will provide a list of directions for driving between the locations in cells A2 and B2.
Tips and Considerations
Google Sheets has limits on the number of calculations it can perform. If you're working with a large dataset, consider using the Trip Tally add-on instead, which performs the calculations once and stores the results as static values.
The Travel Formulas add-on provides live formulas that update based on the latest data from Google Maps. If you don't need live updates, you can convert the formulas to static values to improve performance.
You can automate the process of fetching travel data by using Google Apps Script and setting up time-based triggers. This allows you to retrieve and update travel information at regular intervals without manual intervention.