Add Table of Contents with hyperlinks to all sheets in workbook. Option to exclude Hidden sheets

Conversation
65 
/*  3/5/2023  */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
 
  ui.createMenu('Sidebar')
      .addItem('Sheet Index', 'showSheetsInSidebar')
      .addToUi();
}



function showSheetsInSidebar() {
  var html = "<style>" +
             "table, th, td { border: 1px solid black; padding: 5px; background-color: AntiqueWhite }" +
             "</style>" +
             "<table><tr><th>Select</th><th>Sheet Name</th></tr>";
  
  var sheets = SpreadsheetApp.getActive().getSheets();
  var showHiddenSheets = PropertiesService.getScriptProperties().getProperty("showHiddenSheets") || false;
  var sortAlphabetically = PropertiesService.getScriptProperties().getProperty("sortAlphabetically") || false;
  
  if (sortAlphabetically == "true") {
    sheets.sort(function(a, b) {
      return a.getName().localeCompare(b.getName());
    });
  }
  
  // Move "Dashboard" sheet to beginning of sheets array
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() == "Transactions") {
      sheets.unshift(sheets.splice(i, 1)[0]);
      break;
    }
  }
  
  html += "<tr><td colspan='2'><input type='checkbox' id='showHiddenSheets' " + (showHiddenSheets == "true" ? "checked" : "") + " onclick='google.script.run.toggleShowHiddenSheets(this.checked)'> Show Hidden Sheets</td></tr>";
  html += "<tr><td colspan='2'><input type='checkbox' id='sortAlphabetically' " + (sortAlphabetically == "true" ? "checked" : "") + " onclick='google.script.run.toggleSort(this.checked)'> Sort Alphabetically</td></tr>";
   html += "<tr><td colspan='2'><input type='button'  + value='Hide Active Sheet' / " + " onclick='google.script.run.hideSheet()'>  </td><</tr> ";

  
  for (var i = 0; i < sheets.length; i++) {
    if (!sheets[i].isSheetHidden() || showHiddenSheets == "true") {
      html += "<tr><td><input type='radio' name='sheet' value='" + sheets[i].getName() + "' onclick='google.script.run.goToSheet(this.value)'></td><td>" + sheets[i].getName() + "</td></tr>";
    }
  }
  
  html += "</table>";
  
  var ui = HtmlService.createHtmlOutput(html)
      .setTitle("Sheet Index")
      .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(ui);
}

function toggleSort(sortType) {
  PropertiesService.getScriptProperties().setProperty("sortAlphabetically", sortType);
  showSheetsInSidebar();
}

function toggleShowHiddenSheets(show) {
  PropertiesService.getScriptProperties().setProperty("showHiddenSheets", show);
  showSheetsInSidebar();
}

function goToSheet(sheetName) {
  var sheets = SpreadsheetApp.getActive().getSheets();
  
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() == sheetName) {
      SpreadsheetApp.setActiveSheet(sheets[i]);
    }
  }
}

function hideSheet() {
  var hideActiveSheet  = PropertiesService.getScriptProperties().getProperty("hideActiveSheet") || false;
  var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getActiveSheet().hideSheet();
 /* showSheetsInSidebar();*/
}

function sortVisibleSheetsByColor() {
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  sheets = sheets.filter(function(sheet) {
    return !sheet.isSheetHidden();
  });
  sheets.sort(function(a, b){
    if (a.getName() === "Transactions") return -1;
    if (b.getName() === "Transactions") return 1;
    return a.getTabColor() > b.getTabColor() ? 1 : -1;
  });
  sheets.forEach(function(sheet, index) {
    ss.setActiveSheet(sheet);
    ss.moveActiveSheet(index + 1);
  });
  
}

function getContrastYIQ(hexcolor){
  var r = parseInt(hexcolor.substr(1,2),16);
  var g = parseInt(hexcolor.substr(3,2),16);
  var b = parseInt(hexcolor.substr(5,2),16);
  var yiq = ((r*299)+(g*587)+(b*114))/1000;
  return (yiq >= 128) ? 'black' : 'white';
}
66

it’s great! I just changed random color to match the blue in the Tiller Transaction sheet and centered “Table of Contents”. Learning more and more through little edits. Thanks!

67

That’s the fun part, making modifications to suit you and learn how to write scripts in the process. I always have learned better by examples. I made another change to the script and added a button next to the “hide sheet” button that runs a script that calculates how much space is left on each spreadsheet. It displays the list in a message box, not in the side bar. That was fun to get working!

68

Ok. Late to the party but reaping the benefits. When I woke up this morning, I had no clue about scripts in Sheets. But I really really wanted a table of contents for all my tabs in 2024. I read through this thread and dug in. I had a million questions and I read and re-read and found the answers to them all.

I now have a fancy dancy totally working sidebar Index and I even managed to change the color and the font and the background to satisfy my way-too-picky eyes.

Thank you thank you tall in this thread. I’m shocked and delighted that I was about to add this to my Tiller sheet for the new year.

1 Like
69

I recently have been getting an error message: Exception: Malformed HTML content: (with a long detailed error script). Has anyone else gotten any error message? Nothing else has changed since it last worked.

NOTE: ChatGPT was used and fixed the error. Thanks.