Jump between sheets in a google spreadsheets by clicking a button

Question

I have a single Google spreadsheet having multiple sheets like "sheet1" and "sheet2".

I want to add a button to jump from sheet1 to sheet2 or sheetx for which I need to use app script.

Can anyone suggest what code/function should I use to link the sheets using button.

I know how to hyperlink cells but I need button(UI) for same.

Have you tried writing a script to do this? If you post it others can help fix it. — ADW
Take a look at Google sheets macro for switching to another sheet based of drop-down selection - it's not "precisely" what you want but there are two quite different answers that offer food for thought and some programming. — Tedinoz
Another solution, this time using a sidebar - Code is on infospired How to add a hyperlinked index sheet to your large Google Sheet workbooks — Tedinoz

Tanya Gupta Tanya Gupta · Accepted Answer · 2019-08-30T12:21:01

Here is a basic proof of concept that should allow you to develop a more complete solution. This uses the sidebar. I am assuming you have basic familiarity with GAS including where to put the script, how to authorize and run the code.

code.gs

function onOpen(e) {

  SpreadsheetApp.getUi().createAddonMenu().addItem('Start', 'start').addToUi()

}



function onInstall(e) {

  onOpen(e);

}

function start() {
   try {
      SpreadsheetApp.getUi().showSidebar(HtmlService.createTemplateFromFile("UI").evaluate().setTitle("Menu").setWidth(300).setSandboxMode(HtmlService.SandboxMode.IFRAME));
   }
   catch(e)
   {
     var msg="Error. Please re-try."
     SpreadsheetApp.getUi().alert(msg + e);

   }
  return;

}


function makeActiveSheet(num){

  name = "Sheet"+num
  Logger.log(name)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name).activate();


}

UI.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Menu</h1>
    <button onclick='google.script.run.makeActiveSheet(1);'>Sheet 1</button>
    <button onclick='google.script.run.makeActiveSheet(2);'>Sheet 2</button>
  </body>
</html>

Jump between sheets in a google spreadsheets by clicking a button

1 Answers