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
- Make a copy of the sample spreadsheet Apps Script Quickstart: Macros, menus, and custom functions.
- From within your new spreadsheet, select the menu item Tools > Script editor. If you are presented with a welcome screen, click Blank Project.
- 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; }
- Select the menu item File > Save. Name your new script and click OK.
Try it out
- Switch back to your spreadsheet and reload the page.
- Enter the formula
=DRIVINGDISTANCE(A2,B2)
into cellC2
and press enter. After a moment, the driving distance between the two addresses will be shown in the cell. - Enter the formula
=METERSTOMILES(C2)
into cellD2
and press enter. After a moment, the equivalent distance in miles will be shown in the cell. - Add additional rows of addresses and copy the formulas in columns
C
andD
to compute the driving distances between various places. - 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.)
- 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.
- 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: