Search code examples
google-apps-scriptgoogle-sheetsmenuitem

How to disable SpreadsheetApp.addMenu item


The SpreadsheetApp.AddMenu(name, array of entries) produces a new menu in the top bar of the spreadsheet. Alternatively, there is the getUI.createMenu that results in an array of items. Both work fine, not sure which is better.

Is it possible to disable and enable some of the menu entries depending on what the active sheet is?

Is there a way to tell when a particular sheet becomes active? If not, I can work with the onChange or onEdit events since the menu item I want disabled needs to be enabled only when there is a user change to a particular sheet.


Solution

  • A script can find which sheet is presently active by calling getActiveSheet. However, there is no trigger that fires when a user switches from one sheet to another: this is not considered an Edit or a Change. Go with the plan B stated in the question: detect the active sheet at the time of an edit/change.

    As for two methods: .addMenu syntax was the only one available in an old version of Google Sheets. When getUI().createMenu() was added, the old method was kept for compatibility. The old one has simpler syntax, but the new method has more options, such as adding separators and sub-menus. See What is the difference between creating a menu with .getUI().createMenu() and .addMenu()?

    There is no way to make an item of a custom menu appear disabled. Instead, when a user tries to invoke an option that's not available in the present context, you may want to show a not-too-annoying message such as a toast to inform them of that.