How to make a Google Sheet custom function as an Add-On

Question

I'm tying to create a custom function for google sheets that will be accessible with an Add-On to Google Sheets.

The problem is when I run the Add-On as a test, when I type in my custom formula it gives me this error: #NAME? Error Unknown function: 'myfunction'.

For simplicity sake, I have just been using the following simple function so I know the issue isn't with my functions code

function myFunction(input) {
  var output = input * 2;
  return output;
}

Answer 1

I also tried to write custom function and publish as Add-on, but get "Error Unknown function" in the Spreadsheet.

Wasted several days on it, and finally got it working.

1. You must have a add-on menu.

   Ref: Custom Functions with add-ons?

2. You must define the scopes in your manifest. And the scopes must be the same as your project.

Ref: https://issuetracker.google.com/issues/36763437#comment22

e.g. add this to your appsscript.json file:

  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ],

Answer 2

After scouring the web and questions/answers on Stack Overflow, I believe that as of May 2017 this is an Open Bug with GAS Add-ons. I'm curious if you have come to the same conclusion.

I tried Google's own date_add_and_substract Add-on example and couldn't get it to work myself. It's supposed to illustrate the difference between the Installed for Current User and Enabled in Current Document Add-on states. When the Enabled, the Add-on is supposed to load the following Customer Functions for working with Dates:

• DATEADD()
• DATESUBTRACT()
• DATETEST()

When I run the standalone script as Test as Add-on, enabled it, then enter any of those Custom Function into the cell I get that same #NAME? Unknown function: 'XXXXXXX' Error.

In fact this seems to be one of the Open Bug Tickets.