I am encountering a weird problem. Whenever I open a new file, the custom function of my Google Sheet add-on is not recognized (it shows #NAME?; Unknown function)
In my add-on I have some code which creates add-on dropdown menu on open. From the menu, the users can choose to show the sidebar, refresh all sheets, view documentation, etc.... When they click any option in the menu bar, the custom function is instantly recognized and runs normally without error.
I think Google Sheets only recognize my custom functions when the users start my add-on (by clicking anything in my add-on's dropdown menu). Is there any way to make Google Sheets recognize my custom function on open, without having to click anything from the menu bar.
Appreciate any help
CodePudding user response：
Add-ons should be activated on each spreadsheet before they can used on them. This can't be done automatically, so you should tell your users that in order to be able to use the add-ons custom functions they should activate the add-on.
In order to avoid having the #NAME? error, IFERROR might be used, i.e.
=IFERROR(myFunction(),"Please activate the add-on")
Resource
CodePudding user response：
Adding an onOpen(e) trigger to your app script should allow you to pre-load custom menu options before user authentication.
var menu = SpreadsheetApp.getUi().createAddonMenu();
if (e && e.authMode == ScriptApp.AuthMode.NONE){
// Add a normal menu item (works in all authorization modes).
menu.addItem('Start workflow', 'startWorkflow');
If the add-on is installed but not enabled your onOpen function will run with AuthMode.NONE which is probably why your app is getting blocked by user security settings. Refer to the Editor Add-On Authorization documentation for Apps Script to confirm your auth state.