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

Question

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":

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?

Is the change to the sheet name done manually? if it can be done through code you'd be able to use renameActiveSheet("Hello world"); to the new value and then update it into the cell — AMolina
– AMolina, Commented Aug 13, 2019 at 7:07

Aidan · Accepted Answer · 2019-08-11 15:08:33Z

4

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.

answered Aug 11, 2019 at 15:08

Aidan

1,8101 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Riccardo Over a year ago

Thanks @Aidan much appreciated help! One question: is there a way to trigger the change once the sheet name is changed, without affecting too much performance?

Doug Leary · Accepted Answer · 2022-09-28 02:24:17Z

0

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.

answered Sep 28, 2022 at 2:24

Doug Leary

3181 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related