CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Riccardo
Riccardo

Reputation: 2216

Google Sheets custom function bug "Unknown function" after successful run

In a Google Sheet cell, a custom function is being called through =sheetName() to run the following script (that will return current sheet name):

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

On first edit (code insertion in cell) the function works properly, but since the sheet is renamed the function won't work as the cell (calling the custom function) content is not updated. Editing again the cell will show the error "Unknown function":

enter image description here

At this point, some editing in the "offending" cell, will correctly run again the custom function...

Any idea why the function will only work when cell content is edited and wont run when sheet "tab" is renamed?

Upvotes: 3

Views: 5374

Answers (2)

Doug Leary
Doug Leary

Reputation: 318

I am getting the exact same error with functions that DO have parameters. As per the docs I opened Extensions/Apps Script and wrote a function called myFunction that takes one param and displays the param. I added test code that calls myFunction(55) and console.logs the result. This runs correctly without error. So then I saved the project, and according to the docs I should now be able to use myFunction. So I type the following into a cell, to call myFunction with the value of cell D2 as parameter:

=myFunction(D2)

The cell says #NAME and the context help says "myFunction" is an unknown function.

Upvotes: 0

Aidan
Aidan

Reputation: 1750

Google sheets is optimised so that recalculations are done as efficiently as possible. One of the main ways that this is done is through looking at the parameters of functions and only recalculating cells that have dependencies that have changed.

This mostly doesn't cause problems, but for your function which has no parameters you will find that the function is run once and never gets run again. Surprisingly the original result remains even if you close and open the sheet again.

A hacky solution is to put in a dummy parameter with a reference to another cell. 

=sheetName(A1)

If you want to recalculate just change the contents of A1 and it will force your function to be recalculated.

Upvotes: 4

Related Questions