Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active July 26, 2023 16:08
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tanaikech/1232a8fd1ffbd84c96ebbb97051c5b59 to your computer and use it in GitHub Desktop.
Save tanaikech/1232a8fd1ffbd84c96ebbb97051c5b59 to your computer and use it in GitHub Desktop.
Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script

Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script

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);
    subMenu.addItem(dynamicMenu,dynamicMenu);
  }
  menu.addSubMenu(subMenu).addToUi();
}

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

installFunctions();

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

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

function onChange() {} // 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

Testing

  • October 16, 2020: I could confirm that this sample script worked. In the current stage, it seems that the specification at Google side is not changed.
@histteacher
Copy link

You are brilliant! This is a great feature!

@tanaikech
Copy link
Author

@histteacher Thank you so much.

@jayakrishnankg
Copy link

jayakrishnankg commented Oct 15, 2020

@tanaikech, I am getting "Script function not found: function-name" exception. I found that "this[dynamicMenu]" is not adding function dynamically to the global app script. After printing this, I found that dynamic functions are not created. Can you please help me out?

Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment