Google Apps Script - Update Custom Menu name on Sheet Change

Question

I've been trying to update this code to change the custom menu's name to the current Sheet name.

function onOpen() {
  const prop = PropertiesService.getScriptProperties();
  const sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); 
  SpreadsheetApp.getUi().createMenu(`${sheetName}`).addItem('Run Script', 'main').addToUi();
  prop.setProperty("previousSheet", sheetName); 
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const randomValue = Math.floor(Math.random() * 100);
  const sheetName = range.getSheet().getSheetName();
  if (sheetName != previousSheet) {
    range.setValue(`Changed tab from ${previousSheet} to ${sheetName}. ${randomValue}`);
    SpreadsheetApp.getActiveSpreadsheet().removeMenu(`${previousSheet}`);
    onOpen();
  } else return;
  
  prop.setProperty("previousSheet", sheetName);

}

I could never get .removeMenu to work when I use it in onSelectionChange, neither onOpen to add the updated menu again. Not sure if this is even possible to do, maybe someone can help. Thanks.

CMB · Accepted Answer · 2021-08-04 19:31:31Z

1

Explanation:

Menus created from Class Ui are different from menus created from Class Spreadsheet, so your removeMenu() is failing since it tries to remove a non-existent menu. Thus you need to create your menu in Class Spreadsheet.

Sample Code:

function onOpen() {
  const prop = PropertiesService.getScriptProperties();
  const sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); 
  var menuEntries = [];
  menuEntries.push({name: sheetName, functionName: "main"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Sheet Menu",menuEntries);
  prop.setProperty("previousSheet", sheetName); 
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const randomValue = Math.floor(Math.random() * 100);
  const sheetName = range.getSheet().getSheetName();
  if (sheetName != previousSheet) {
    range.setValue(`Changed tab from ${previousSheet} to ${sheetName}. ${randomValue}`);
    SpreadsheetApp.getActiveSpreadsheet().removeMenu("Sheet Menu");
    onOpen();
  } else return;
  
  prop.setProperty("previousSheet", sheetName);

}

Sample Output:

References:

Class Spreadsheet

answered Aug 4, 2021 at 19:31

CMB

5,1811 gold badge8 silver badges17 bronze badges

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

4 Comments

Alex Over a year ago

Very useful! Thanks a lot. This still doesn't update on Sheet switching even though I've used your own code. Any ideas why it might not work? PrintScreen: prnt.sc/1j4x62v

CMB Over a year ago

It helps to check your script executions in script.google.com. Maybe somewhere it failed. It always works on my setup.

Alex Over a year ago

Using your code again but creating a new Spreadsheet from Incognito solved it. I think it was a bug somewhere. Works now, would've been cool if there was a way to change the menu name as well. Thank you a lot!

Dan Dascalescu Over a year ago

The problem with menus created this way is that you can't add submenus to them. The "subMenu" parameter to updateMenu is actually the flat list of menu items; nothing hierarchical.

Collectives™ on Stack Overflow

Google Apps Script - Update Custom Menu name on Sheet Change

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related