google-sheetsgoogle-apps-scriptsidebar

Displaying hyperlinks to other sheet tabs in a custom sidebar

I have a custom sidebar working great, showing links to other sheet tabs. When you click a link, it opens another version of Google Sheets and displays the required Sheet. However, I want the script to change to the correct Sheet tab without opening a new instance of Google.

My Code.gs is:

function onOpen() {
 SpreadsheetApp
   .getUi()
   .createMenu("Sidebar")
   .addItem("Show Sidebar", "Display.sidebar")
   .addToUi();
}

const Display ={
  html(){
    return HtmlService.createHtmlOutputFromFile('Home')
  },
  sidebar(){
    return SpreadsheetApp.getUi().showSidebar(this.html());    
  }
};

My Home.html is:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body>
    <div style="padding:5px;">
    <ul>
      <li><a href="https://website.com" target="_blank">Website</a></li>
      <li><a href="https://docs.google.com/spreadsheets/d/*****/edit?gid=941347032#gid=941347032" target="_blank">Haulier List</a></li>
      <li><a href="https://docs.google.com/spreadsheets/d/*****/edit?gid=1977595707#gid=1977595707" target="_blank">Purchase Invoices</a></li>
      <li><a href="https://docs.google.com/spreadsheets/d/*****/edit?gid=969162599#gid=969162599" target="_blank">Sales Invoices</a></li>
      <li><a href="https://docs.google.com/spreadsheets/d/*****/edit?gid=344378613#gid=344378613" target="_blank">Create Sales Invoice</a></li>
      <li><a href="https://docs.google.com/spreadsheets/d/*****/edit?gid=838733436#gid=838733436" target="_blank">Customer Warehouse Stock</a></li>
    </ul>
    </div>
  </body>
</html>

The following article has a solution provided by @Cooper and does what I want almost... How to create a sidebar list of all sheets in a Google Spreadsheet workbook?

Instead of displaying ALL sheets in the Sidebar, I want specific sheets only, as per my original effort.

Solution

  • The answer to the above question is here...

    https://support.google.com/docs/thread/356832818?hl=en&sjid=10003399125056233914-EU

    The solution uses @Cooper code as follows:

    function allsheetslist() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  var html='<table>';
  shts.forEach(function(sh,i){
    html+=Utilities.formatString('<tr><td><input type="button" value="%s" onClick="gotoSheet(%s);" /></td></tr>',sh.getName(),sh.getIndex());
  });
  html+='</table>';
  html+='<script>function gotoSheet(index){google.script.run.gotoSheetIndex(index);}</script>';
  var userInterface=HtmlService.createHtmlOutput(html)
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

function gotoSheetIndex(index) {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  shts[index-1].activate();
}

    @--Hyde then provides his solution to replace a section of the code with the following:

    shts.forEach((sheet, i) => {
    const sheetName = sheet.getName();
    if (sheetName.match(/^(Sheet1|Sheet2|Another sheet|Fourth)$/i))
      html += Utilities.formatString('<tr><td><input type="button" value="%s" onClick="gotoSheet(%s);" /></td></tr>', sheetName, sheet.getIndex());
  });

    This does exactly what I hoped it would. It enables me to specify the exact sheets I want in my sidebar.

    I hope that this proves useful to others.