# 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.
```/**
* 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
*/
function onOpen() {
{name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}
];
}

/**
* Creates a new sheet containing step-by-step directions between the two
* addresses on the "Settings" sheet that the user selected.
*/
function generateStepByStep_() {
settingsSheet.activate();

// Prompt the user for a row number.
var selectedRow = Browser.inputBox('Generate step-by-step',
' (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;
if (directionsSheet) {
directionsSheet.clear();
directionsSheet.activate();
} else {
directionsSheet =
}
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);
}

/**
* 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

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.
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.