How to run google sheet custom functions from standalone script editor add-on?

[Alberto Oteíza]

I am trying to create a editor add-on for spreadsheets that would be public in the future.

I created a apps script by following the documentation and managed to make the add-on appear on the menu and run a startWorkflow function that asks for permission when necessary:

function onOpen(e) {

var menu = SpreadsheetApp.getUi().createAddonMenu(); // Or DocumentApp.

if (e && e.authMode == ScriptApp.AuthMode.NONE) {

// Add a normal menu item (works in all authorization modes).

menu.addItem('Start workflow', 'startWorkflow');

} else {

// Add a menu item based on properties (doesn't work in AuthMode.NONE).

var properties = PropertiesService.getDocumentProperties();

var workflowStarted = properties.getProperty('workflowStarted');

if (workflowStarted) {

menu.addItem('Check workflow status', 'checkWorkflow');

} else {

menu.addItem('Start workflow', 'startWorkflow');

}

}

menu.addToUi();

}

function startWorkflow() {

console.log('START WORKFLOW!')

}

/**

* Multiplies an input value by 2.

* @param {number} input The number to double.

* @return The input multiplied by 2.

* @customfunction

*/

function DOUBLE(num) {

return num * 2;

}

I run my tests by going to Deploy > Test Deployments and executing my deployment with my latest code, but when I try to use the DOUBLE  function in the spreadsheet that opens, I just get an "Unknown function" error:

 

How can I make the sheet access this custom function?

Thanks in advance.

[Alberto Oteíza]

Turns out google has an active issue about this since 9 years ago... https://issuetracker.google.com/issues/36763437?pli=1

What a shame

[Andrew Apell]

Your code looks fine. I have an editor add-on that make extensive use of custom functions and this error shows up from time to time, but it has been weeks since I last saw it.

I have also faced issues when calling the Properties Service in the onOpen() function. There is a limitation with it and similar "services" that I cannot quite remember at the moment, that will cause your add-on menu to display "Help" and nothing else. I would use it cautiously if I were you.