Creating a custom menu in Google Sheets using Google Apps Script can greatly enhance your experience by providing a tailored user interface. This can be particularly beneficial for automating tasks, navigating, and organizing your sheets. Follow the steps below to create your own custom menu in Google Sheets. Adding a custom menu to Google Sheets through Google Apps Script is a convenient way to tailor the interface to suit your needs and streamline tasks
Step 1: Open Google sheets script editor
- Open your Google Sheet document.
- Navigate to “Extensions” > “Apps Script” to open the Script Editor.
Step 2: Write a custom menu script
Once you have opened the Script Editor, you will need to write a script that creates your custom menu.
Here is an example script that creates a custom menu titled ‘Custom Menu’ with a submenu item ‘Log Sheet Name’.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var menuItems = [
{name: 'Log Sheet Name', functionName: 'logSheetName'}
];
spreadsheet.addMenu('Custom Menu', menuItems);
}
function logSheetName() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sheetName = sheet.getName();
Logger.log(sheetName);
}
1. onOpen(): This function runs automatically when the Google Sheet is opened. It is responsible for creating the custom menu.
2. SpreadsheetApp.getActiveSpreadsheet(): Gets the currently active spreadsheet.
3. addMenu(‘Custom Menu’, menuItems): Adds a custom menu to the active spreadsheet.
4. logSheetName(): This is a custom function that logs the name of the active sheet to the Logger.
Step 3: Save the script
- After typing the script, click on the floppy disk icon or press Ctrl + S (Windows) or Command + S (Mac) to save the script.
- You may name your project by clicking on “Untitled project” in the upper-left corner of the script editor.
Step 4: Relaunch Google sheets
After saving the script, close your Google Sheets and reopen it. You should now see your ‘Custom Menu’ in the menu bar between ‘Extensions’ and ‘Help’.
Step 5: Test the Custom menu
To test your newly created custom menu:
1. Click on ‘Custom Menu’ in the menu bar.
2. Select ‘Log Sheet Name’.
3. Go back to the Script Editor, and in the left-hand column, click ‘View’ > ‘Logs’.
4. Check if the name of the active sheet is logged correctly.
Adding more menu items
To add more menu items to your custom menu, you simply need to add more objects to the menuItems array in the onOpen() function, each having a name and a functionName.
For instance, if you want to add another menu item that logs the number of rows in the active sheet, your onOpen() function would look like this:
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var menuItems = [
{name: 'Log Sheet Name', functionName: 'logSheetName'},
{name: 'Log Number of Rows', functionName: 'logNumRows'}
];
spreadsheet.addMenu('Custom Menu', menuItems);
}
function logNumRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var numRows = sheet.getLastRow();
Logger.log(numRows);
}
