Create Menu in Google sheet and link to another sheet

Create a Custom Menu to link another sheet

In google sheet

 

Create a custom menu and link to another sheet by using the app script editor.

For Custom menu items  use simple trigger function, which is  onOpen() .

To update onOpen()  and add new menu items in the menu bar do the following.

 

·         In the menu bar click on tools - > script editor add following script.  

·         You can add your desired menu item name as per your requirement.

·         Change the Menu name as per your requirement (i.e.; ‘My Data’ ‘Progress-Report’, ‘Sales’. ‘Max’, ‘Rocky’)

 

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('MyData')

      .addItem('Progress-report', 'menu1')

      .addSeparator()

      .addSubMenu(ui.createMenu('Sales')

          .addItem('Max', 'menu2')

          .addItem('Rocky', 'menu3'))

      .addToUi();

  }

Once Menu created in the menu bar, assign Links for each menu adding the below script. 

Note: Paste your target sheet url in the place of 'PASTE YOUR URL HERE' text

Change the user interface name in the script as per your requirement. 

function menu1() {

  var selection = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();

    var html = "<script>window.open('PASTE YOUR URL HERE');google.script.host.close();</script>";

    var userInterface = HtmlService.createHtmlOutput(html);

    SpreadsheetApp.getUi().showModalDialog(userInterface, 'Progress Report');

}

function menu2() {

  var selection = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();

    var html = "<script>window.open('PASTE YOUR URL HERE');google.script.host.close();</script>";

    var userInterface = HtmlService.createHtmlOutput(html);

    SpreadsheetApp.getUi().showModalDialog(userInterface, 'Max');

}

function menu2() {

  var selection = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();

    var html = "<script>window.open('PASTE YOUR URL HERE');google.script.host.close();</script>";

    var userInterface = HtmlService.createHtmlOutput(html);

    SpreadsheetApp.getUi().showModalDialog(userInterface, 'Rocky');

}

Watch the video for more details :