Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script

GasTips
Google Apps Script / Spreadsheet / custom menu

Gists

This is a sample script for dynamically updating the custom menu of Google Spreadsheet using Google Apps Script.

Demo

In this demonstration, when the Spreadsheet is opened, 5 functions added to the custom menu. You can see that when a column is added and deleted, the custom menu is updated.

Issue and workaround for this goal

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, the goal cannot be directly achieved. So it is required to use the workaround.

When I thought this 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 as the workaround.

Sample script

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 like above demo video. And when you run the script, please open the Spreadsheet. By this, the custom menu is created. When new column is added by copying, the additional column is also added to the custom menu by the OnEdit event trigger as the simple trigger. When a column is deleted, the custom menu is also updated by the OnChange event trigger as the installable trigger. And when the function at the custom menu is run, the values corresponding to the column are activated. This can be seen at the demo video.

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

Note

  • In order to dynamically update 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

 Share!