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

user-defined functions sql
user-defined function example
user defined functions python
user defined function excel
user defined functions c++
excel custom function javascript
types of user-defined functions in sql server
sql user defined functions w3schools

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?

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.

Although Excel includes a multitude of built-in worksheet functions, chances are it doesn't have a function for every type of calculation you perform. Custom  Let's look at a case where you need to ensure that a zip code parameter is in the correct format for the custom function to work. The following custom function uses a regular expression to check the zip code. If it is correct, then it will look up the city (in another function) and return the value.

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.

Return values. Every custom function must return a value to display, such that: If a custom function returns a value, the value displays in the cell the function was  The function isn't getting called ("Setting a break point in the custom function shows that the function isn’t actually called."). It's not a problem with the function code. Everything works fine for a couple of weeks, and then suddenly all these cells have "#VALUE!"

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...

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user  Excel allows you to create custom functions using VBA, called "User Defined Functions" (UDFs) that can be used the same way you would use SUM() or other built-in Excel functions. They can be especially useful for advanced mathematics or special text manipulation or date calculations prior to 1900 .

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  The trick is that Power Query custom function can return any single object. and that object can be simple structure object such as Date, Text, Number. Or it can be multiple value objects such as Record, List, and Table.

return(object) }. Objects in the function are local to the function. The object returned can be any data type. Here is an example. # function example - get measures  Using return values in your own functions. To return a value from a custom function, you need to use the return keyword. We saw this in action recently in our random-canvas-circles.html example. Our draw() function draws 100 random circles somewhere on an HTML <canvas>:

The last statement in a function must be a RETURN statement. User-defined functions can't contain any function that returns different data every  To create a custom DISCOUNT function in this workbook, follow these steps: Press Alt+F11 to open the Visual Basic Editor (on the Mac, press FN+ALT+F11 ), and then click Insert > Module. A new module window appears on the right-hand side of the Visual Basic Editor. Copy and paste the following code to the new module.

Inside the function body, the return statement determines the value to be returned​. In other languages, void-type functions are not even called a function at all  Most PowerShell newbies believe that PowerShell functions can return a value only through the Return statement. The return statement usually terminates the function and returns control to the calling function. But in Windows PowerShell, this is not entirely true…

Comments
  • Check for the sillies first. Is the code in an .html file instead of a .gs file? Are you editing a script that's in a different spreadsheet? Are the custom functions you are interested in contained inside another function (thus hiding them)? Did you save the script? Can you run double() from within the debugger?
  • All the sillies checked - it is a .gs, it is in the correct spreadsheet, it is a root level function, it is saved, it can be run in the debugger, permissions have been granted to the doc, etc. Last Note - I can simply duplicate the spreadsheet, and in the duplicate, all custom functions duplicate work as expected.
  • Instead of a programming problem this looks to be a problem with the spreadsheet file and you should ask for help to Google Apps for Work support, but this could be very likely that the solution will be to use another file.
  • I too had my spreadsheet stop recognising functions that had been working and unchanged for a long time. After seeing this answer I tried changing the code in a way that shouldn't make a difference but did. I had functions f(x, y) and for me x was constant across all calls so I created g(y) which calls f(x,y) with the fixed x and revised all the calls to f(x,y) to g(y) and that ended up working.
  • Hello @George. I understand you're facing the same kind of problem as in the OP question, and you are looking for a solution. In this case you should upvote the original question, or open a new question. Also, there seems to be one anwser offering solutions : stackoverflow.com/a/40063526/5142427. Does it solve your problem ?
  • My post was a resolution as well.