Google Apps Script: Dynamically creating spreadshe

Question

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();
    }     
}

Answer 1

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();
}

Answer 2

• 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.

Answer 3

Assuming you wanted to store the relevant captions and function names in Sheet1 columns A & B, respectively, you could simply call .getValues() and iterate through that data.

/** 
 * SHEET1 VALUES
 * --------------------------------------
 *          Column A        Column B
 * Row 1    Class One       functionOne
 * Row 2    Class Two       functionTwo
 * Row 3    Class Three     functionThree
 */

function onOpen(e) {
  // Initialize the menu & submenu
  var ui = SpreadsheetApp.getUi(); 
  var menu = ui.createMenu('Menu Name').addItem('First label', 'menuItem1').addSeparator();
  var subMenu = ui.createMenu('Submenu Name');

  // Get the submenu item data from Sheet1
  // Values in column A represent the captions that will appear in the submenu
  // Values in column B represent the name of the function to be called
  var values = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('A:B').getValues();
  for (var rowIndex = 0; rowIndex < values.length; ++rowIndex) {
    var rowData = values[rowIndex];
    var caption = rowData[0]; // Column A value
    var functionName = rowData[1]; // Column B value
    if (caption != '' && functionName != '') { // Exclude empty strings
      subMenu.addItem(caption, functionName);
    }
  }     

  // Add the submenu to the menu, and finally to the UI
  menu.addSubMenu(subMenu).addToUi();
}

function menuItem1() { Browser.msgBox('menuItem1'); }
function functionOne() { Browser.msgBox('functionOne'); }
function functionTwo() { Browser.msgBox('functionTwo'); }
function functionThree() { Browser.msgBox('functionThree'); }

---

In the original post, the error was happening because .addSubMenu() was being called on the value [9p1] instead of a Menu object. Further, from my understanding of the question, I think a menu item is intended to be created in the loop, not an individual submenu for each class. So those are two problems that needed to be resolved.

I also assume that you don't want to run runBatch1 for every single menu item as that would defeat the purpose of creating different menu items. According to the documentation, the method for adding a menu item expects two strings:

1. caption – The label for the menu item.
2. functionName – The name of the function to invoke when the user selects the item.

So it follows that you can substitute any string when making your submenu.addItem() call.

var caption = "Caption";
var functionName = "functionName";
var subMenu = ui.createMenu('Submenu Name');
subMenu.addItem(caption, functionName);     

---

Other Scenarios

Array of Item Objects

Alternatively, you could define an array (or object map) of the various submenu items you want to include, rather than storing them in the spreadsheet. Here, I've used an array of objects to clearly define the caption & functionName values.

function onOpen(e) {
  // Initialize the menu & submenu
  var ui = SpreadsheetApp.getUi(); 
  var menu = ui.createMenu('Menu Name').addItem('First label', 'menuItem1').addSeparator();
  var subMenu = ui.createMenu('Submenu Name');

  // Array of items to include in the submenu
  var items = [
    { caption: 'Class One', functionName: 'functionOne' },
    { caption: 'Class Two', functionName: 'functionTwo' },
    { caption: 'Class Three', functionName: 'functionThree' }
  ];
  for (var i = 0; i < items.length; ++i) {
    var item = items[i];
    subMenu.addItem(item.caption, item.functionName);

  }     

  // Add the submenu to the menu, and finally to the UI
  menu.addSubMenu(subMenu).addToUi();
}

function menuItem1() { Browser.msgBox('menuItem1'); }
function functionOne() { Browser.msgBox('functionOne'); }
function functionTwo() { Browser.msgBox('functionTwo'); }
function functionThree() { Browser.msgBox('functionThree'); }

Caption String Manipulation

Since the method accepts any string, you can perform any string manipulation that will result in a valid function name. In this example, I'm only storing the captions in an array and I dynamically generate the function names based on the caption values. (You could also pull the caption names from column A and apply the same manipulation.)

function onOpen(e) {
  // Initialize the menu & submenu
  var ui = SpreadsheetApp.getUi(); 
  var menu = ui.createMenu('Menu Name').addItem('First label', 'menuItem1').addSeparator();
  var subMenu = ui.createMenu('Submenu Name');

  // Array of captions to include in the submenu.
  // Will generate function names from this.
  var captions = ['Class One', 'Class Two', 'Class Three'];
  for (var i = 0; i < captions.length; ++i) {
    var caption = captions[i];
    subMenu.addItem(caption, 'function' +  caption.split(' ')[1]);
  }     

  // Add the submenu to the menu, and finally to the UI
  menu.addSubMenu(subMenu).addToUi();
}

function menuItem1() { Browser.msgBox('menuItem1'); }
function functionOne() { Browser.msgBox('functionOne'); }
function functionTwo() { Browser.msgBox('functionTwo'); }
function functionThree() { Browser.msgBox('functionThree'); }