Google Apps Script is an application development platform that helps create applications that integrate with Google Workspace (Google Sheets, Google Docs, Google Slides …) quickly and easily.

If your spreadsheet consists of multiple sheets and it is a bit difficult to switch between sheets each time, why not try creating custom menus to make navigation easier.

Start

Suppose you have 3 sheets with the names “Peter”, “Tom” and “Lee” respectively. We will create a custom menu so that, every time we click on a sheet name, only that sheet will be displayed.

Every time you open the file, the onOpen () function will run and display the custom menu “Navigate”. When you click on the sheet name, the function with the corresponding name will be called and display the message ” You call function … ” (for simplicity, I just show the alert , the complete code is added in the following part). .

This way we have created a custom menu that meets the requirements. However, when the number of sheets as well as the sheet name change, we have to modify the code. Therefore, we will try to rewrite the code to automatically get the sheet names and create the corresponding custom menu .

Automatic

Since the custom menu does not pass parameters when calling the function, we will create these functions using code where the function name is the sheet name. We’ll use the this keyword to point to the global variable, and use the forEach command to traverse an array of sheet names. Note, the code that creates this dynamic function cannot be in the function onOpen () , if it is in onOpen () , this keyword points to the onOpen () function, not the global variable.

The code this[name] = function() {} will generate the functions (Peter (), Tom (), Lee ()) like the method by hand above, although at runtime we do not see the This function is displayed. When we click on the custom menu we will see an alert name of the function called.

Every time the sheet name changes, just run the onOpen () function again and the “Navigate” custom menu will automatically change. However, the function name does the sheet conversion exactly the same as sheet name, so sometimes errors will arise.

Debug

According to the function naming rules , if the function contains spaces or begins with a number / special character, an error will arise. Therefore, if the sheet name has spaces (and some other cases), when running the code, the error will be “Script function not found: Tom my”.

To avoid this error, we will replace the sheet name with sheet id to name the function (the id of each sheet is unique in each spreadsheet ). Also append the prefix because the sheet id starts with a number.

Since the sheet variable is already an array of sheets , each sheet is an object containing sheetName and id, so we don’t need to use the array of variable names (names). Besides, Apps Script function only supports getSheetId() without supporting function getSheetById() so we need to write this function.

Complete

For now, we have just shown the sheet one at a time. We’ll add the “Show all” command on the custom menu to help the user display the entire sheet .

Thus, we used Apps Script to create custom menus for Google Sheets. The code is written in the project bound with the spreadsheet, so if you want another spreadsheet to also display the custom menu “Navigate” then just copy / paste the Custom_Menu.gs code file into the new spreadsheet.

Demo Google Sheets file . Full code at Github.