5

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;
}
c.dimock
  • 59
  • 3
  • That exact code works for me. Only things I did was cut/paste/save and then =myFunction(2) = 4. – Tom Woodward Jun 23 '16 at 17:34
  • Did it work when you ran it as a Test Add-On? Because the function works fine when it is just in the script file for the sheet, but I can't figure out how to make it work when I test it as an Add-On. – c.dimock Jun 27 '16 at 14:02
  • @c.dimock did you find out the answer? – Ankur Feb 21 '17 at 11:02

2 Answers2

4

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"
  ],
Neil
  • 7,227
  • 5
  • 42
  • 43
franfran
  • 3,085
  • 2
  • 18
  • 11
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.

Sample Function BUG in GAS Add-Ons

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

bgerd
  • 223
  • 2
  • 11