Google Apps Script

Quickstart: Macros, Menus, and Custom Functions

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 newRows = [
        [sheetTitle, '', ''],
        ['Step', 'Distance (Meters)', 'Distance (Miles)']
      ];
      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,
          '=METERSTOMILES(R[0]C[-1])'
        ]);
      }
      directionsSheet.getRange(1, 1, newRows.length, 3).setValues(newRows);
    
      // 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:

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.