Google sheets doesn't display the function on my script

2
votes

I am very new to using Google Scripts. I am having this very basic problem, but I can't seem to find an explanation. I programmed a function on a Google Sheets (opening the script editor from the same sheet in which I want to try the code). It turns out that when I want to use my function, Google Sheets doesn't find it. That means, when I type "=" plus the name of the function, I can't find it. My code is the following: 

function distance(origin,destination) {

    var mapObj = Maps.newDirectionFinder()

    mapObj.setMode(Maps.DirectionFinder.Mode.DRIVING)

    //Set the Orgin
    mapObj.setOrigin(origin)

    //Set the Destination
    mapObj.setDestination(destination)

    //Retrieve the Distance
    var directions = mapObj.getDirections();
    var meters = directions["routes"][0]["legs"][0]["distance"]["value"];
}

I would appreciate any help!!

3
google-apps-scriptgoogle-sheetsjsdoccustom-function
Make sure your custom function returns some value to display, see developers.google.com/apps-script/guides/sheets/… – Kos

3 Answers

1
votes

I copied your function and it seems to be missing alright! I can't find a reference but I think "distance" is reserved in some way. If you change the name of your function to getDistance() it works fine.

/**
 * Calculate the distance between origin and destination
 *
 * @param {Sheet3!B32}  reference  Cell or range to monitor.
 * @returns                        The distance.
 * @customfunction
 */
function getDistance(origin,destination) {
...
}

If you add a jsDoc comment before the function, as I have, the intellisense will work - i.e. when you type "=" in the formula bar, the contents of your comment will show:

enter image description here

1
votes

Try to use:

* @customfunction

in comments before the function

-1
votes

Not sure exactly what you mean by "sheets doesn't find it". Here are a couple of tips that might help:

  1. You can easily add a custom menu to any doc with the following code:

    function onOpen() { var ui = SpreadsheetApp.getUi(); var menu = ui.createMenu("My Script Menu"); menu.addItem("Menu Item 1", "function1").addToUi(); menu.addItem("Menu Item 2", "function2").addToUi(); ...etc. }

When you open the document, the custom menu will appear (wait a few seconds) and you can chose 'function1' from 'My Script Menu'.

  1. When your Google Apps Script Project opens, there are no functions selected in the Debug menu. Make sure you are selecting the function that you want to run if you are using the Debugger:

Google Apps Script Debugger

Click the down arrow and then chose the function you want to run.