Google Apps Script: Dynamically creating spreadsheet menu items

I am trying to create a dynamically populated menu in Google Sheets using Google Apps Script.

1. I have a sheet, 'Classes', where I list the classes I teach.
2. On running my script I get my script to read and load these classes into an array.
3. In order to only hard-code values in the original 'Classes' sheet I want to then to create a sub-menu item for each of these classes.

The sheet is called 'Classes'. The values in the classes sheet are 8H, 9p1, 9p2 etc. They are in cells A1:A12. In the debugger the array, menuItemArray, loads correctly with all expected classes from the 'Classes' sheet.

The error I get is:

TypeError: Cannot find function addSubMenu in object 9p1. (line 13, file "Code")

This is when stepping into the line

menuItemArrayClass =  menuItemArray [menuCount]

I would be really grateful for any help as to what I am doing wrong or any better ways to do it.

Here is my code:

function onOpen(e) {
    var ui = SpreadsheetApp.getUi(); 
    var menuCount = 0; 
    ui.createMenu('Manage Timetable')
    .addItem('First item', 'menuItem1')
    .addSeparator()

    var menuItemArray =     SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Classes').getDataRange().getValues();  
    for (menuCount=1;menuCount < menuItemArray.length;++menuCount) {
        var menuItemArrayClass = [] 
        menuItemArrayClass =  menuItemArray [menuCount]
        .addSubMenu(ui.createMenu('Manage Classes')
            .addItem(menuItemArrayClass [menuCount] + 'Schedule Timetable', 'runBatch1'))
        .addToUi();
    }     
}

Solution 1:

• You want to create custom menu.
• You want to update the custom functions by editing the Spreadsheet.
• You want to use one function name like myFunction() for several functions in the custom menu.
• You want to give each value to the function as the argument when a function is run from the custom menu.
• The sample situation is as follows.
  • There are values in the column "A" to "I".
  • There are 9 functions in the custom menu. The custom menu is created when the Spreadsheet is opened. The function names are corresponding to each column name.
  • When the function of the column "A" is clicked, the values of the column "A" is activated.
  • When the column "I" is copied to the column "H", the new function is added to the custom menu.

I understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

Unfortunately, in the current stage, when a function is added to the custom menu with addItem method, the argument cannot been able to be used. And when one of functions in the custom menu is run, the information about the function name which was run cannot be retrieved. By this, your goal cannot be directly achieved. So it is required to use the workaround.

When I saw your question, for your goal, I thought that this thread is useful. At google.script.run, it is required to be able to directly run the function at the script editor and the function is included in this. But at the custom menu, when the function is included in this, the function can be run even when the function cannot be directly run at the script editor. When the function is run in only GAS side, the function can be run even when the function cannot be directly run with the script editor. I thought that this situation can be used for the workaround.

Modified script:

When your script is modified by including this workaround, it becomes as follows. Please copy and paste it to the container-bound script of Spreadsheet which has the headers ("Col1", "Col2",,,) at the 1st row and the values from 2nd row. And when you run the script, please open the Spreadsheet. By this, the custom menu is added. And when new column is added by copying, the additional column is also added to the custom menu. And when the function at the custom menu is run, the values corresponding to the column are activated.

From:

function onOpen(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Custom Menu')
  .addItem('First item', 'menuItem1')
  .addSeparator();
  var subMenu = ui.createMenu('Sub-menu');
  for (var i = 0; i < headers.length; i++) {
    var dynamicMenu = headers[i];
    subMenu.addItem(dynamicMenu,'dynamicItem');
  }
  menu.addSubMenu(subMenu).addToUi();
}

function onEdit(e) {
  onOpen(e);
}

function menuItem1() {
  SpreadsheetApp.getUi()
  .alert('You clicked the first menu item!');
}

function dynamicItem() {
  SpreadsheetApp.getUi()
  .alert('You clicked the dynamic menu item!');
}

To:

function installFunctions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Custom Menu')
  .addItem('First item', 'menuItem1')
  .addSeparator();
  var subMenu = ui.createMenu('Sub-menu');
  for (var i = 0; i < headers.length; i++) {
    var dynamicMenu = headers[i];
    this[dynamicMenu] = dynamicItem(i); // Added
    subMenu.addItem(dynamicMenu,dynamicMenu); // Modified
  }
  menu.addSubMenu(subMenu).addToUi();
}

function dynamicItem(i) { // Added
  return function() {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(2, i + 1, sheet.getLastRow() - 1, 1).activate();
  }
}

installFunctions(); // Added

function onOpen() {} // Modified: This can be used as the simple trigger.

function onEdit() {} // Modified: This can be used as the simple trigger.

function onChange() {} // Added: Please install OnChange event trigger to this function.

• Before you use this script, please install the OnChange event trigger to the function of onChange(). By this, when the column is deleted, the custom menu is updated.
• The functions of function onEdit() {} and function onChange() {} are used for running onOpen();.

Result:

Note:

• In order to dynamically create the custom menu, this script is required to be run at initial stage when the function is run. So it is required to be put it as the global like onOpen();.
• In this workaround, when the function is run, onOpen is run every time. So when the number of columns are large, the process cost will be high. So please be careful this.
• This is a simple sample script for explaining one methodology of the workaround. So please modify this for your situation.

References:

• Custom Menus
• Different output in 3 anonymous function in GAS
  • I think that this thread was useful for thinking OP's goal.

Solution 2:

Try this:

function onOpen(e) {
    var ui = SpreadsheetApp.getUi(); 
    var menuCount = 0; 
   var menu = ui.createMenu('Manage Timetable');
    menu.addItem('First item', 'menuItem1')
    menu.addSeparator()

    var menuItemArray =     SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Лист2').getDataRange().getValues();  
    for (menuCount=0;menuCount < menuItemArray.length;menuCount++) {
        var menuItemArrayClass = [] 
        menuItemArrayClass =  menuItemArray [menuCount];
        menu.addSubMenu(ui.createMenu('Manage Classes')
            .addItem(menuItemArrayClass + ' Schedule Timetable', 'runBatch1'))
        menu.addToUi();
    }     
}

If you start from 1 you loose first row, so I changed it to 0.

Also now you need to dynamically allocate the scripts, but I do no know how they are set up in your sheet, so I left that part unchanged.

Also not sure if you need to add 'Manage Classes' menu every time, but I kept it just in case.

Try this instead as it seems more like what you want:

function onOpen(e) {
  var ui = SpreadsheetApp.getUi(); 
  var menuCount = 0; 
  var menu = ui.createMenu('Manage Timetable')
  .addItem('First item', 'menuItem1')
  .addSeparator();
  var subMenu = ui.createMenu('Manage Classes');

  var menuItemArray =     SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Лист2').getDataRange().getValues();  
  for (menuCount=0;menuCount < menuItemArray.length;menuCount++) {
    var menuItemArrayClass = [] 
    menuItemArrayClass =  menuItemArray [menuCount];
    subMenu.addItem(menuItemArrayClass + ' Schedule Timetable', 'runBatch1');      
  }     

  menu.addSubMenu(subMenu).addToUi();
}