Create a dynamic Menu from content in a Google Sheet

The following Google Apps Script is designed to dynamically create a Menu in a Google Sheet, based specifically on the data within it. So rather than the typical process of creating a static Menu where you prescribe exactly which items go into it and the Functions to run, this script will generate the items each time the Sheet is opened and perform one universal Function: translating 'Hello' into a couple of different languages in this example.

This means you can add/remove items from your Sheet and the Menu will adjust accordingly (the next time you re-open it), without any need to go into the Apps Script Editor.

Screenshot of Sheet popup showing translated text from data within the Sheet.

The Code

One of the strange parts about this code is needing to call our main Function 'dynamicMenu' at the very top of our script so that it runs as the Google Sheet is opened, because we want our Menu to be ready immediately. We also run the standard 'onOpen' Function in order to help trigger the creation of our Menu.

We perform our typical getting of the Sheet data in our 'getSheetData' Function. We will additionally need to loop through each row and combine the column and row values as a JavaScript Object (name:value pair), so we can refer to them when a user selects our Menu. Along with a customised message we then push this Object into our array:

// extract each column value

var language = row[0];

var formal = row[1];

var informal = row[2];

// create popup message

var msg = `"Hello" in ${language} is "${formal}" or "${informal}"`;

// push values and popup message into array

rowData.push({

    'name': language,

    'args': msg

});

Back in our Parent Function we then need to map this information to our Menu options. We extract our data from the JavaScript Object by looping through it and then assign each Menu item with a universal Function that will display a popup with the translated language in this example:

// get Language 'name'

var functionName = "_" + message.name;

// get Message details

var args = message.args;

The JavaScript method 'this' is used to link a keyword (Language in this example) with our 'sayHello' Function that we want to run when somebody selects from our Menu. We pass into it our customised message before finally adding the item to our Menu:

// use 'this' keyword method to link Menu item name with corresponding Function

this[functionName] = sayHello(args);

// add sub-Menu item to main Menu

menu.addItem(message.name, functionName);

Finally, we have our 'sayHello' Function that displays a simple popup dialogue box with our customised message:

function sayHello(args) {

  return function () {

    Browser.msgBox(args);

  };

};

Caveats

It is worth being aware of a couple of potential pitfalls with this method:

1. The code needs to load when the Google Sheet is opened, that means it will run every single time. Therefore depending on just how much data you are using to try and create a dynamic Menu, it could start to become a bit sluggish.
   
   
2. It may not pair very well with other Triggers such as onEdit(e) which are also typically running code when changes are made to a Google Sheet. Again the overhead could cause sluggish behaviour.
   
   
3. If you are debugging your code you need to remember that a chunk of it will be running as soon as the Google Sheet is opened, in addition to whatever other functionality you may have developed.

Download

Create a dynamic Menu from content in a Google Sheet download (please use 'File' > 'Make a copy' for your own version).