Coding level: Beginner
Duration: 10 minutes
Project type: Custom function and
automation with a custom menu
Objectives
- Understand what the solution does.
- Understand what the Apps Script services do within the solution.
- Set up the script.
- Run the script.
About this solution
Using custom functions, you can calculate the driving distance between two locations and convert the distance from meters to miles. An additional automation provides a custom menu that lets you add step-by-step directions from the start address to the end address in a new sheet.
How it works
The script uses 2 custom functions and an automation.
- The
drivingDistance(origin, destination)
function uses the Maps Service to calculate the driving directions between two locations and return the distance between the two addresses in meters. - The
metersToMiles(meters)
function calculates the equivalent number of miles for a given number of meters. - The automation prompts the user to enter which row of start and end addresses to calculate the driving directions for and adds the step-by-step driving directions to a new sheet.
Apps Script services
This solution uses the following services:
- Spreadsheet service–Adds the custom menu, adds demo data to test this solution, and formats the new sheets when the script adds driving directions.
- Base service–Uses the
Browser
class to prompt the user to enter a row number for directions and alerts the user if an error occurs. - Utilities service–Updates templated strings with user-specified information.
- Maps service–Gets step-by-step Google Maps directions from the start address to the end address.
Prerequisites
To use this sample, you need the following prerequisites:
- A Google Account (Google Workspace accounts might require administrator approval).
- A web browser with access to the internet.
Set up the script
- Make a copy of the Calculate driving distance and convert meters to miles
spreadsheet. The Apps Script project for
this solution is attached to the spreadsheet.
Make a copy - To add headers and demo data to your sheet, click Directions > Prepare sheet. You might need to refresh the page for this custom menu to appear.
When prompted, authorize the script. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} (unsafe).
Click Directions > Prepare sheet again.
Run the script
- In cell
C2
, enter the formula=DRIVINGDISTANCE(A2,B2)
and press Enter. If you're in a location that uses decimal commas, you might need to enter=DRIVINGDISTANCE(A2;B2)
instead. - In cell
D2
, enter the formula=METERSTOMILES(C2)
and press Enter. - (Optional) Add additional rows of start and end addresses and copy the
formulas in columns
C
andD
to calculate the driving distances between various places. - Click Directions > Generate step-by-step.
- In the dialog, enter the row number of the addresses for which you want to generate directions and click OK.
- Review the driving directions in the new sheet the script creates.
Review the code
To review the Apps Script code for this solution, click View source code below:
View source code
Code.gs
Contributors
This sample is maintained by Google with the help of Google Developer Experts.