HOW TO CREATE CUSTOM MENUS IN GOOGLE SHEET

In this article we will learn how we can create custom Menus in Google Sheet.

Google App script library provides functions , using which , we can create our own Menus in Google sheet.

In an example above , we can see in the image, a Menu named "My Macros" is created next to the Help menu in Google sheet.

Let see how to write code in App Script for custom Menu.

The major steps are following:

Step 1. 

We will write a function which will trigger on opening of the Google sheet. In the function we will write the code for creating the custom menu.

By doing so, when ever the google sheet file is opened, our custom menu will get created. 

Step 2 

We will create an instance of the spreadsheet app and then we will add custom menu with the help of "createMenu" method in Google sheet.

Step 3

Then we will add an item in the newly created custom menu, by using the method "addItem"

The add item function has two parameters, first to provide the name of the item and second to provide the name of the function which we want to link with the newly created Item.

Let's see the code:

function OnOpen(e) {

  SpreadsheetApp.getUi()

   .createMenu("My Macros")

    .addItem("Change Color", "Red")

    .addToUi()

 }

Next we will write a small function named "Red", which we have linked in the "addItem" method.

Here is the code to that:

function Red(){

  var oGs = SpreadsheetApp

  var oWb = oGs.getActiveSpreadsheet()

  var oSh = oWb.getActiveSheet()

  var Rng  =  oSh.getActiveRange()

  Rng.setBackgroundRGB(255,0,0)

}

Explanation of the code "Red"

Firstly, the above function creates instance to spreadsheetapp in the variable oGs. 

Next we have created instance to active spreadsheet in the variable oWb. Further we have created instance to active sheet in the variable oSh. 

Next we have created a variable "Rng" and we are storing the active range in this variable.

Later we are using "Rng" variable and by using set background RGB method, we are changing the background color of this range to color red.

The set background RGB method takes three parameters i.e  red, green and blue color values.

The code of red colour is 255, 0, 0. 

In this way we can change the background color of our range to red.

Next we will link this function i.e "Red" to "addItem" method so that when we will use the custom menu, this function can be executed from there.

Now we will see, how our main function onOpen(e) works.

In Google Apps script we have a function called onOpen().  This function works whenever the Google sheet is opened.

First, we have created the instance of Google sheet app with the help of function spreadsheetapp and then with the help of another function getUI(),   we have created the instance of Menu in Google sheet.

You can see this in the following code

SpreadsheetApp.getUi()

Next we will create menu by using the function create menu. The createMenu() function has a parameter "caption" which is of a string type. This parameter helps us to define the name of new menu.

.createMenu("My Macros")

In the above code we have provided the name of our custom menu as "My Macros".

Then we have added a new item in our newly created custom menu. We have given the name of our new item as "Change Color" and then we have provided the name of the function which we want to execute when this item is clicked from the menu. Below is the code for the same.

.addItem("Change Color", "Red")

In the last line of code we have used the method addToUi()  which helped us to add the newly created custom menu and items under that, to the menu bar of Google sheet.

.addToUi()

I hope you liked this article.

Thank you.

Ashwani