"Direction" menu does not appear in the Google Sheet

[Alan C]

Hi,

I just follow the instructions in https://developers.google.com/apps-script/quickstart/custom-functions to create a sheet and the create and save a script. However, when I use Ctrl + Shift + R to reload the worksheet, I cannot see the "Directions" menu in the sheet. Why?

I have re-read the tutorials and confirmed that I have done every step properly.

[Darren D'Mello]

Share the code here 😂

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/274f13f3-d698-4cf6-93d4-4e6f0b61c58eo%40googlegroups.com.

[Alan C]

The code is copied from the above URL, as below:

/**
* @OnlyCurrentDoc Limits the script to only accessing the current sheet.
*/

/**
* 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: 'Prepare sheet...', functionName: 'prepareSheet_'},
{name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}
];
spreadsheet.addMenu('Directions', menuItems);
}

/**
* 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 function that adds headers and some initial data to the spreadsheet.
*/
function prepareSheet_() {
var sheet = SpreadsheetApp.getActiveSheet().setName('Settings');
var headers = [
'Start Address',
'End Address',
'Driving Distance (meters)',
'Driving Distance (miles)'];
var initialData = [
'350 5th Ave, New York, NY 10118',
'405 Lexington Ave, New York, NY 10174'];
sheet.getRange('A1:D1').setValues([headers]).setFontWeight('bold');
sheet.getRange('A2:B2').setValues([initialData]);
sheet.setFrozenRows(1);
sheet.autoResizeColumns(1, 4);
}

/**
* 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.status !== 'OK') {
throw directions.error_message;
}
return directions;

}


On Friday, October 2, 2020 at 5:02:09 PM UTC+8, miscellaneousmailer wrote:
>
> Share the code here 😂
>

> On Fri, Oct 2, 2020, 1:32 PM Alan C <datanu...@gmail.com <javascript:>>

> wrote:
>
>> Hi,
>>
>> I just follow the instructions in
>> https://developers.google.com/apps-script/quickstart/custom-functions to
>> create a sheet and the create and save a script. However, when I use Ctrl +
>> Shift + R to reload the worksheet, I cannot see the "Directions" menu in
>> the sheet. Why?
>>
>> I have re-read the tutorials and confirmed that I have done every step
>> properly.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Google Apps Script Community" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to google-apps-script-c...@googlegroups.com

>> <javascript:>.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/google-apps-script-community/274f13f3-d698-4cf6-93d4-4e6f0b61c58eo%40googlegroups.com

>> <https://groups.google.com/d/msgid/google-apps-script-community/274f13f3-d698-4cf6-93d4-4e6f0b61c58eo%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>

[Alan Wells]

You'll need to get some debug information.

It may be helpful to know whether you are using V8 or DEPRECATED_ES5

because the logging is a little different for each.

From the code editor,

click the "View" menu.

Click "Show Manifest file"

or make sure that it is checked.

Open the appsscript.json file.

Look for:

"runtimeVersion":

That will tell you whether you're using V8 or DEPRECATED_ES5

From the "View" menu choose:

Executions

Look for failures.

You can expand the row, and click the link:

VIEW IN STACKDRIVER

If you see any error information, let us know what it is.

[Alan C]

Sorry but today when I load the sheet, I can see the "Directions" menu. I think what the guide said about reload the sheet, is close and then reopen it in Google Drive, not Ctrl + SHift +R