Dynamically creating spreadsheet menu items

by
  • 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:

enter image description here

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.

Leave a Comment

tech