How to disable SpreadsheetApp.addMenu item

Question

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.

Leatri

Answer 1

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.

Autotoxin

Answer 2

Thanks for the question, in my case, what a try to do is a dynamic menu in a Spreadsheet, which is going to change visually (user interface), showing some menu items, in function to the user need to do, over the same active spreadsheet.

How i made this?

• I use this 2 resources :

Custom Menus in Google Workspace

A document, spreadsheet, presentation, or form can only contain one menu with a given name. If the same script or another script adds a menu with the same name, the new menu replaces the old. Menus cannot be removed while the file is open, although you can write your onOpen() function to skip the menu in the future if a certain property is set.

Properties Service

The Properties service lets you store simple data in key-value pairs scoped to one script, one user of a script, or one document in which an add-on is used. It is typically used to store developer configuration or user preferences

The sample code

/*1.- Create the menu and define its name*/
  
const uiMenu = SpreadsheetApp.getUi().createMenu('MyMenu');
  
/*2.- Define a variable and store in propertyService*/
 
  const properties = PropertiesService.getDocumentProperties();
  const isLoad = properties.getProperty('load');

  console.log(typeof Boolean(isLoad));
  console.log('global', isLoad);

/* 3.- At the moment the spreadsheet is open this function is going to execute */

function onOpen(){
  console.log('onOpen', isLoad);
  if(Boolean(isLoad)){
       uiMenu
      .addItem('Send', 'send')
      .addItem('End', 'end')
  } else {
      uiMenu
      .addItem('Start', 'start')
      .addItem('Load', 'load')
  }
  uiMenu.addToUi();   
}

function start(){
  SpreadsheetApp.getUi().alert('You clicked the first menu item!');
  SpreadsheetApp.getActiveSpreadsheet().toast('Started');
}

function load(){
  SpreadsheetApp.getUi().alert('You clicked the Second menu item!');

  //set the variable 'load'
  properties.setProperty('load', true);

  //set the menu with the new items
  uiMenu
    .addItem('Send', 'send')
    .addItem('End', 'end')

  uiMenu.addToUi();

  SpreadsheetApp.getActiveSpreadsheet().toast(`Loading data`);
}


function send() {
  SpreadsheetApp.getUi().alert('You clicked the Third menu item!');

  if(Boolean(isLoad)){
     SpreadsheetApp.getActiveSpreadsheet().toast(`Sending data`)
  }
}

function end(){
  SpreadsheetApp.getActiveSpreadsheet().toast('End the process');
  
  //You can delete or set the variable to start with the process again.
 
  properties.deleteProperty('load');
  
//set the menu with the new items
  uiMenu
      .addItem('Start', 'start')
      .addItem('Load', 'load')
      .addToUi()
}

Shush