Find out which menu item was selected from a custom menu in Google Sheets

In a previous tutorial, I showed you how to build a navigation menu in Google Sheets using custom menus. The Apps Script code that we used in that tutorial defined a separate function for every menu item. And every one of these functions used the same business logic tosheet corresponding to the selected menu item.

// ...

// Activate the sheet named sheetName in the spreadsheet. 
function setActiveSpreadsheet(sheetName) {
 SpreadsheetApp.getActive().getSheetByName(sheetName).activate();
}

// One function per menu item.
// One of these functions will be called when users select the
// corresponding menu item from the navigation menu.

function sales() {
 setActiveSpreadsheet("Sales");
}

function marketing() {
 setActiveSpreadsheet("Marketing");
}

function finance() {
 setActiveSpreadsheet("Finance");
}

// ... etc.

There is a lot of repetitive code here. We're defining a number of functions that simply call a common function.

The question is is there a different way to do this? The answer is … yes! There is a way to find out which menu item was selected in a custom menu with a lot less code. However, before explaining how to do that, it is important to say this is really only an issue if you have several menu items in your custom menu. If you have fewer than 5 menu items, it may be better to use the above simpler approach of creating separate functions for each menu item.

What are we trying to accomplish?

We want to use a single function definition for multiple menu items in a custom menu. That is to say, we want a single handler function that we can attach to every menu item such that this function can figure out which menu item was selected.

Understanding the caller of a function

The key concept that we will be using in this tutorial is the caller of a function. Let us first understand what the "caller" of a function means.

Consider two functions A and B that both call (i.e., invoke) a third function C. When function C is executed because A called it, the caller of C is A. When C is executed because B called it, the caller of C is B.

Within function C, you can access the function that called it by using the caller property: C.caller. Also, C.caller.name is the name of the caller function.

The code below demonstrates the concept of a function's caller.

function A() {
  //Call function C
  C();
}

function B() {
  //Call function C
  C();
}

function C() {
  //Log the caller's name
  Logger.log(C.caller.name);

  //Log the caller function's code
  Logger.log(C.caller.toString());
}

When you run function A, it will call function C, which will then log the name and source code of its caller function A.

Similarly, when you run function B, it will call function C which will log the name and source code of itss caller function B.

Now, let's see what happens when we declare a variable D and assign the function C to it.

var D = C;

When you run the function D, function C will be run since the value of function D is C. Interestingly, the caller function here is actually an anonymous function that returns the function D.

We're able to figure out that function D led to calling function C from the source code of the caller function (i.e, from C.caller.toString()). Here, we did not define a separate function D. We simply created a variable D and assigned the function C to it.

Now, inside function C, we can use the string methods indexOf() and slice() to extract the name D from C.caller.toString().

function C() {
  var caller = C.caller.toString();
  var startIndex = caller.indexOf("return") + 7;
  var endIndex = caller.indexOf("(...arguments);");
  var callerFunctionName = PROPER_CASE(caller.slice(startIndex, endIndex));
  Logger.log(callerFunctionName);
}

var D = C;

// This is not needed for the code to work but the Apps Script
// editor will only let you run D() if you declare it like a function
function D() {}

Using the caller of a function to figure out which menu item was selected in a custom menu

We can now use the above approach to figure out which menu item was selected without defining separate functions for each menu item.

In the code below, we define a single function common() that handles menu selections and it figures out which menu item was selected based on its caller. The benefit of this approach is that all of the business logic for handling menu selections is centralized in a single function. This makes updating the logic easy to do since you only have to make updates in a single place.

//@OnlyCurrentDoc

// Create a menu using an onOpen simple trigger 
function onOpen() {
 SpreadsheetApp.getUi().createMenu("Test")
   .addItem("A", "A")
   .addItem("B", "B")
   .addItem("C", "C")
   .addToUi();
}

// All of the business logic for handling menu selections
// goes into this function. In this example, this function
// displays a toast notification with the 
function common() {
  var caller = common.caller.toString();
  var startIndex = caller.indexOf("return") + 7;
  var endIndex = caller.indexOf("(...arguments);");
  var callerFunctionName = caller.slice(startIndex, endIndex);
  SpreadsheetApp.getActive().toast(callerFunctionName);
}

// We declare a variable for each menu item and we assign
// the function common to them. We pick the variable's name such that
// we can deduce which menu item was selected from this name.
var A = common;
var B = common;
var C = common;

Here is a video that demonstrates this feature:

Conclusion

In this tutorial, I showed you how to figure out which menu item was selected in a custom menu without defining separate functions for each menu item. I also explained what the caller of a function is and how to apply this concept to the problem at hand.

We've effectively created a single "handler" function for all menu selections such that the handler can determine which menu item was selected. I've been trying to figure out how to do this for some time now and I finally came up with the above approach that seems to work well. If you know of a better way to do this, please do let me know using the form below.

Thanks for reading!