ALL Custom Functions return "unknown function" in one spreadsheet, but work in others

Question

I have a few Simple Custom Functions that I use all the time. I have recently run into a spreadsheet that I desperately need to use them in, but when I try to use even the most basic custom functions, I get "#NAME?" with a hover text of "unknown function".

Even deleting all other code, and trying just this simple function, doesn't seem to work:

function double(d) {
  return 2*d;
}

When I duplicate the spreadsheet, the code that comes with it DOES work.

A few Notes:

1. This is a Google Apps for Enterprise account.

2. I was originally not the owner on this spreadsheet, but have since become the owner, thinking that this might be the cause.

3. The spreadsheet originally had protected regions, which have since been removed, thinking that this might be the cause.

This spreadsheet is shared with hundreds of people, so I really want to find the root cause and fix it.

Any thoughts?

Answer 1

When you have custom functions on a sheet - they areBOUND to that sheet. So it does work when you duplicate the sheet itself , but it does not automatically become a global or domain wide custom function.

The only way use it else where without actually publishing it as an add-on is to go to the tools menu, click on script editor and actually copy the code from the script bound to that sheet that it works with, then paste it in the script editor of the sheet you want to use it in.

Answer 2

Way too late for the OP, but in case this helps someone else: I had a similar issue where my spreadsheet suddenly stopped recognising custom functions that have been working for months. No code changes have been made recently and the spreadsheet hadn't even been edited since it last worked. When I went to Tools > Script editor it still showed the code with the custom functions.

Reloading the page and closing and reopening didn't fix it. I added a simple parameterless wrapper function to call the custom function with some appropriate arguments.

I then ran this wrapper function in the debugger with a breakpoint in the original custom function. When it hit this, I switched back to the spreadsheet and it could magically see the custom functions again.

Answer 3

Again, late in the thread, but it was the only thread I found on this -- My function had been running well, too and suddenly returned Unknown Function. I found that the signature had changed - initially, I had a few parameters I was sending in and the cell returning the error still had parameters in the function call. The function hadn't used parameters for a long time. I removed the parameters and Voila! Apparently, something changed where it started enforcing call patterns...

Answer 4

If you already published as Add-on, then you need to do reactivate the addon for copied sheet with the following steps:

• Click the menu Add-ons / YOUR-ADD-ON-NAME / Help / View in store,
• click Manage and in the dropdown menu uncheck then check the 'Use in this document' again for the addon to be loaded to the current sheet.

Answer 5

I can not explain the exact cause, but here is a SOLUTION:

1. copy the expression written in the cell
2. delete the content of the cell
3. re-enter the expression in the cell!

Answer 6

I faced exactly the same problem ! My code worked well and was properly linked to the correct spreadsheet. Unfortunately deleting the content of the cell didn't work for me.

Another solution I found is to simply rename the function.

For example: "myFunction" renamed as "my_Function" and it worked for me ! I guess maybe it's due to an important amount of calls (over 35 000 in my case). Maybe it makes it run into an error or something. Cheers !

Answer 7

Another late entry. This error can also come as a signature violation. This can occur if the type of variable being passed into the custom function is different from what is expected. I was "cleaning up" my spreadsheet and re-formatted some of the entries. This caused a not found. Detecting this error is made easy if you enabled the function for prompting. In my case I started typing the function name and it came up (so Google knew it was there), but when it came to execution I got the error.

Answer 8

I created a copy of my sheet as a backup and closed up for the night. I came back in the morning with the functions unable to be called. So I did the usual, cut the function from the cell and pasted it right back. This usually fixed things. Not this time. Tried reopening browser, tried re-logging in. Nothing worked. Than I simply renamed the function in the Project Editor (appended "call" in front of the function name) and edited the sheet to match. It just worked.

Answer 9

I faced the same problems. I here document, what worked for me.

To create a new custom function, I did the following:

1. In spreadsheet: Menu -> Tools -> Script Editor
2. Wrote the function in Script Editor
3. In spreadsheet: Menu -> Tools -> Import -> Clicked "Add Function" next to the custom function name.
4. In spreadsheet: In a cell, I did =CustomFunctionName()

This worked.

Now, when I wanted to reuse the same in another spreadsheet, there are 2 ways to do that.

1. Make Copy of the sheet in which the custom function is written.
2. Make library of custom functions and reuse them in other sheets, by writing custom functions specific for a particular sheet.

#1 Make Copy of the sheet in which the custom function is written.

In this way, the whole custom function code gets duplicated. So, if any changes needs to be done in the custom function, the change must be done in all the copied files. Also, once the sheet is copied, the custom function does not simply works. I had to delete the cells & reenter the function names again, to get them working.

#2 Make library of custom functions and reuse them in other sheets, by writing custom functions specific for a particular sheet.

This is better, because, the core logic will stay in one place. Only wrapper functions will be written for each sheet, in which the custom functions are used.

To create a library:

1. Write the function in Script Editor
2. Click on "Deploy -> New Deployment"
3. "Select Type -> Library"
4. Provide a description, if you want to and click "Deploy" & "Done"
5. Click on the "Project Settings" on the left menu & copy the "Script Id"(to use the library)

To use a library:

1. Goto the spreadsheet where you want the library to be used.
2. Menu -> Tools -> Script Editor
3. In Script Editor: Click + button next to the Library section on the left.
4. Enter the "Script Id" & Click on "Look Up".
5. You will see the script details. Then click on the "Add" button.
6. Remember the "Identifier" field value. Using that, the functions of the library can be accessed.
7. Now, in the Script Editor, you can call the library functions with the identifier name. See examples below.

Library Code:

function SheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getName();
}

function SayHello(name) {
  return 'Hello ${name}';
}

Let's say, while including this library into a spreadsheet, I entered the identifier as utility. So, in the script editor, I would write like this:

function SheetName() {
  return utility.SheetName();
}

How to update a library and use the updated code in spreadsheets?

To update a library:

1. Make the changes in the library project.
2. Click on "Deploy -> Manage Deployments".
3. Select the correct deployment from the left side.
4. Click on the edit button(on top left).
5. Select "New Version" in the "Version" input.
6. Click "Deploy" & make note of the new version number displayed.

To use the updated library:

1. Goto the spreadsheet in which the library is already being used & open the Script Editor in there.
2. If the Script Editor of that sheet is already open, reload the page once.
3. Click on the library name in the Library section(on the left).
4. Select the latest version number on the Version input & click "Save".

Now, you can call the new functions in the same way you did previously & the existing functions will work in the new way(if there are any changes to the existing functions).

Answer 10

When writing the functions, the comment section was not done properly. Here was what I wrote initially:

/* 
 A custom function that converts US Dollars to SwissFrancs

 @param {number} dollars The total number of dollars
 @return {number} swissFrancs The total number of SwissFrancs

 @customfunction
*/

Upon inspection of the comments, I found out that I used the comment notation wrongly. I ought to have placed an asterisk just after the opening comment tag /* and on every newline in the comments place an asterisk sign and finally place another asterisk sign before the closing multiline comment tag (*/) like so:

/** 
* A custom function that converts US Dollars to SwissFrancs
*
* @param {number} dollars The total number of dollars
* @return {number} swissFrancs The total number of SwissFrancs
*
* @customfunction
**/

One last thing, don't forget to add the @customfunction in the comment section.

Answer 11

As per the documentation by Google, it is important to add the comments before the custom function. Otherwise the function won't appear in the autocomplete popup.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Answer 12

I've solved as follows

#1. Created 2 instances of the same funcion:

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE2(input) {
  return DOUBLE(input);
}

The second instance returns the result of the first one

#2. Created a function to fix the issue:

function updateFormulas_() {
  var f = SpreadsheetApp.getActive();
  var r = f.getActiveRange(); 
  var f1 = 'DOUBLE('; // !! Mind "("
  var f2 = 'DOUBLE1(';


  var finder1 = r.createTextFinder(f1).matchFormulaText(true);
  var found1 = finder1.findNext();
  if (found1) {
    finder1.replaceAllWith(f2);
  }
  var finder2 = r.createTextFinder(f2).matchFormulaText(true)
  var found2 = finder2.findNext();
  if (found2) {
    finder2.replaceAllWith(f1);
  } 
}

Note I've added opening parentheses to my functions in replacements in order not to break the original.

#3. Created onOpen Custom menu

function onOpen() {
  createMenu_();
}
function createMenu_() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Dispatch menu')
      .addItem('Update Formulas', 'updateFormulas_')
      .addToUi();
}

Now if error occurs user can select a range with error and fix it manually.

The other option #3 is to create a time trigger to do this.

Answer 13

If "copy expression -> delete cell contents -> exit cell -> re-enter expression" fixes your problem, then the easiest way to implement it en masse is to select all faulty cells, hit backspace to delete their contents, and then undo the deletion.

Doing so will force reevaluation of affected cells which bypasses the cache.