Complete the steps described on this page, and in about five minutes you'll have created a spreadsheet with custom functions, menu items, and automated procedures (similar to macros).

Set it up

1. Make a copy of the sample spreadsheet Apps Script Quickstart: Macros, menus, and custom functions.
2. From within your new spreadsheet, select the menu item Tools > Script editor. If you are presented with a welcome screen, click Blank Project.
3. Delete any code in the script editor and paste in the code below. Open code in new window

   /**
    * A custom function that converts meters to miles.
    *
    * @param {Number} meters The distance in meters.
    * @return {Number} The distance in miles.
    */
   function metersToMiles(meters) {
     if (typeof meters != 'number') {
       return null;
     }
     return meters / 1000 * 0.621371;
   }
   
   /**
    * A custom function that gets the driving distance between two addresses.
    *
    * @param {String} origin The starting address.
    * @param {String} destination The ending address.
    * @return {Number} The distance in meters.
    */
   function drivingDistance(origin, destination) {
     var directions = getDirections_(origin, destination);
     return directions.routes[0].legs[0].distance.value;
   }
   
   /**
    * A special function that runs when the spreadsheet is open, used to add a
    * custom menu to the spreadsheet.
    */
   function onOpen() {
     var spreadsheet = SpreadsheetApp.getActive();
     var menuItems = [
       {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}
     ];
     spreadsheet.addMenu('Directions', menuItems);
   }
   
   /**
    * Creates a new sheet containing step-by-step directions between the two
    * addresses on the "Settings" sheet that the user selected.
    */
   function generateStepByStep_() {
     var spreadsheet = SpreadsheetApp.getActive();
     var settingsSheet = spreadsheet.getSheetByName('Settings');
     settingsSheet.activate();
   
     // Prompt the user for a row number.
     var selectedRow = Browser.inputBox('Generate step-by-step',
         'Please enter the row number of the addresses to use' +
         ' (for example, "2"):',
         Browser.Buttons.OK_CANCEL);
     if (selectedRow == 'cancel') {
       return;
     }
     var rowNumber = Number(selectedRow);
     if (isNaN(rowNumber) || rowNumber < 2 ||
         rowNumber > settingsSheet.getLastRow()) {
       Browser.msgBox('Error',
           Utilities.formatString('Row "%s" is not valid.', selectedRow),
           Browser.Buttons.OK);
       return;
     }
   
     // Retrieve the addresses in that row.
     var row = settingsSheet.getRange(rowNumber, 1, 1, 2);
     var rowValues = row.getValues();
     var origin = rowValues[0][0];
     var destination = rowValues[0][1];
     if (!origin || !destination) {
       Browser.msgBox('Error', 'Row does not contain two addresses.',
           Browser.Buttons.OK);
       return;
     }
   
     // Get the raw directions information.
     var directions = getDirections_(origin, destination);
   
     // Create a new sheet and append the steps in the directions.
     var sheetName = 'Driving Directions for Row ' + rowNumber;
     var directionsSheet = spreadsheet.getSheetByName(sheetName);
     if (directionsSheet) {
       directionsSheet.clear();
       directionsSheet.activate();
     } else {
       directionsSheet =
           spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());
     }
     var sheetTitle = Utilities.formatString('Driving Directions from %s to %s',
         origin, destination);
     var headers = [
       [sheetTitle, '', ''],
       ['Step', 'Distance (Meters)', 'Distance (Miles)']
     ];
     var newRows = [];
     for (var i = 0; i < directions.routes[0].legs[0].steps.length; i++) {
       var step = directions.routes[0].legs[0].steps[i];
       // Remove HTML tags from the instructions.
       var instructions = step.html_instructions.replace(/<br>|<div.*?>/g, '\n')
           .replace(/<.*?>/g, '');
       newRows.push([
         instructions,
         step.distance.value
       ]);
     }
     directionsSheet.getRange(1, 1, headers.length, 3).setValues(headers);
     directionsSheet.getRange(headers.length + 1, 1, newRows.length, 2)
         .setValues(newRows);
     directionsSheet.getRange(headers.length + 1, 3, newRows.length, 1)
         .setFormulaR1C1('=METERSTOMILES(R[0]C[-1])');
   
     // Format the new sheet.
     directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');
     directionsSheet.getRange('A1:2').setFontWeight('bold');
     directionsSheet.setColumnWidth(1, 500);
     directionsSheet.getRange('B2:C').setVerticalAlignment('top');
     directionsSheet.getRange('C2:C').setNumberFormat('0.00');
     var stepsRange = directionsSheet.getDataRange()
         .offset(2, 0, directionsSheet.getLastRow() - 2);
     setAlternatingRowBackgroundColors_(stepsRange, '#ffffff', '#eeeeee');
     directionsSheet.setFrozenRows(2);
     SpreadsheetApp.flush();
   }
   
   /**
    * Sets the background colors for alternating rows within the range.
    * @param {Range} range The range to change the background colors of.
    * @param {string} oddColor The color to apply to odd rows (relative to the
    *     start of the range).
    * @param {string} evenColor The color to apply to even rows (relative to the
    *     start of the range).
    */
   function setAlternatingRowBackgroundColors_(range, oddColor, evenColor) {
     var backgrounds = [];
     for (var row = 1; row <= range.getNumRows(); row++) {
       var rowBackgrounds = [];
       for (var column = 1; column <= range.getNumColumns(); column++) {
         if (row % 2 == 0) {
           rowBackgrounds.push(evenColor);
         } else {
           rowBackgrounds.push(oddColor);
         }
       }
       backgrounds.push(rowBackgrounds);
     }
     range.setBackgrounds(backgrounds);
   }
   
   /**
    * A shared helper function used to obtain the full set of directions
    * information between two addresses. Uses the Apps Script Maps Service.
    *
    * @param {String} origin The starting address.
    * @param {String} destination The ending address.
    * @return {Object} The directions response object.
    */
   function getDirections_(origin, destination) {
     var directionFinder = Maps.newDirectionFinder();
     directionFinder.setOrigin(origin);
     directionFinder.setDestination(destination);
     var directions = directionFinder.getDirections();
     if (directions.routes.length == 0) {
       throw 'Unable to calculate directions between these addresses.';
     }
     return directions;
   }
   

4. Select the menu item File > Save. Name your new script and click OK.

Try it out

1. Switch back to your spreadsheet and reload the page.
2. Enter the formula =DRIVINGDISTANCE(A2,B2) into cell C2 and press enter. After a moment, the driving distance between the two addresses will be shown in the cell.
3. Enter the formula =METERSTOMILES(C2) into cell D2 and press enter. After a moment, the equivalent distance in miles will be shown in the cell.
4. Add additional rows of addresses and copy the formulas in columns C and D to compute the driving distances between various places.
5. Select the menu item Directions > Generate step-by-step. (The Directions menu is a custom menu that the script added to the spreadsheet. It should have appeared a few seconds after you reloaded the spreadsheet.)
6. A dialog box will appear and tell you that the script requires authorization. Click Continue. A second dialog box will then request authorization for specific Google services. Read the notice carefully, then click Accept.
7. Now that the script is authorized, select Directions > Generate step-by-step again. In the dialog that appears, enter the row number of the addresses for which you want to generate directions, then click OK. After a moment, a new sheet will be created with nicely formatted directions.

That's it! With only a small amount of code, you've created custom functions, added a menu item, and automatically generated a new sheet of information.

Learn more

To continue learning about how to extend Google Sheets with Apps Script, take a look at the following resources:

• Spreadsheet Custom Functions
• Custom Menus in Google Apps
• Extending Google Sheets

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.