Using a loop within the menu UI script beginner

Question

I am trying to create a dynamic menu that creates the names of the sheets in it. I dont often code and need some help. currently the code ON_Open creates a menu, creates its first item in the menu, then add a seperator and then goes into a loop. it checks how many sheets there are and starts at the first one. stores the name and makes a menu item with that name, then advances to the next sheet. gets its name and makes the next menu item. i can get the loop to work with the menu UI syntax.im not worried about the names. i will try to figure that out next,just want it to create the menus first

function onOpen() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var numsheets = spreadsheet.getNumSheets();

  SpreadsheetApp.getUi
      .createMenu('SWMS CREATER')
      .addItem('Create New SWMS', 'showPrompt')
      .addSeparator()

 for ( var i = 0; i < numsheets.length;i++ ) {
    var ui = SpreadsheetApp.getUi();
    var subMenu = ui.createMenu('Test Menu');
    subMenu.addItem('Test script'i ,'showPrompt');
    } 

}

score 0 · Accepted Answer · answered Feb 10 '19 at 13:17

The OP is trying to create a dynamic menu that lists each of the sheets in the spreadsheet. The OP's code is very close to working - there are just a small, but significant, number of adjustments.

function onOpen() {

  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('OPSWMS CREATER')
    .addItem('Create New SWMS', 'showPrompt')
    .addSeparator();

  var sheetList = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var subMenu = ui.createMenu('OPTest Menu');

  for (var i = 0; i < sheetList.length; i++) {
    subMenu.addItem(sheetList[i].getName(), 'showPrompt');
  }
  menu.addSubMenu(subMenu).addToUi();
}

Summary of major differences:

1) variable ui moved out of the loop; and then re-used where possible.
2) variable menu established and also moved out of the loop. This is re-used to add the subMenu in the last line of code.
3) added a semi-colon after .addSeparator() (though optional)
4) used .getSheets() to get all the sheets. This is the first key element missing from the OP code.
5) dropped var numsheets line. You don't need this because you can get the same count on sheetList.
6) within the loop, two things to note

sheetList[i] the i in square brackets ([i]) returns the relevant item from "sheetList";
.getName() returns the name of the sheet. Combined, sheetList[i].getName() gives you the name of the sheet, and lets you add it as a menu item selection.

7) menu.addSubMenu(subMenu).addToUi(); This add the contents of the loop to the menu. This is the second key element missing from the OP code.

Credit:
Akshin Jalilov's answer to Google Apps Script: Dynamically creating spreadsheet menu items

Using a loop within the menu UI script beginner

1 Answers1