Thanks.
I’ll try it out tomorrow. I’m curious to compare the code it gave me to yours.
Scott
I also tried my darndest to get the TOC to open when the sheet was opened. No Dice. ChatGPT says it should work, but it doesn’t. I did manage to get it to add code to change the fonts used. Now that the code is there, I can just edit it to any font that’s supported I want.
> This works on opening the sheet. You can close the sidebar anytime and reopen it from the new 'Open Sheets' menu. [Thanks to everyone here and on Ben Collins google sheets coding blog who did all the coding. I just rearranged it a little. ]
> Here is a link to the sheet
> Https://docs.google.com/spreadsheets/d/1m_th6QgsBH8XkAV2bQY5yPgw2wVnYYKJfz4k1D86Jp4/edit?usp=sharing
>
> function onOpen() {
> var ui = SpreadsheetApp.getUi();
>
> ui.createMenu('Find Sheets')
> .addItem('Find Sheets', 'showSheetsInSidebar')
> .addToUi();
> }
>
>
>
> function showSheetsInSidebar() {
> var html = "<style>" +
> "table, th, td { border: 1px solid black; padding: 5px; }" +
> "</style>" +
> "<table><tr><th>Select</th><th>Sheet Name</th></tr>";
>
> var sheets = SpreadsheetApp.getActive().getSheets();
> var showHiddenSheets = PropertiesService.getScriptProperties().getProperty("showHiddenSheets") || false;
>
> 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>";
>
> 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("TABLE OF CONTENTS")
> .setWidth(200);
> SpreadsheetApp.getUi().showSidebar(ui);
> }
>
> 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 toggleShowHiddenSheets(show) {
> PropertiesService.getScriptProperties().setProperty("showHiddenSheets", show);
> showSheetsInSidebar();
> }
>
>
There’s an odd break in the middle of the code you posted.
Can you paste the code as a “blockquote” or as “preformatted text” using one of the formatting options to the right of Bold, Italics, etc.?
Thank you…
ScottC
I recently ran across this option and so far I like the way it works:
Well, nothing like confessing your lack of sophistication. I’m a bit overwhelmed by this thread. 
I have not used scripts before, but I want a dang TOC! It appears maybe many of you have refined a solution / creation.
@Chas.bicking, @ScottC, or others:
-
Is install of this reasonably “doable” with a step-by-step set of instructions? Or, is another approach perhaps even in this thread recommended for a less sophisticated Sheets user?
-
If reasonably “doable”, is someone willing to do a short step-by-step post of how to install this.
Grateful for a summary and insight and/or a step by step! 
Larry
Appreciate your review of my question/post of today. Thanks, Larry
I had fun with this one. Here is code that will show all sheets, including hidden. And have the option to sort alphabetically leaving the sheet called “Dashboard” always at the top of the list and choose a different background colour every time it’s run. You can hard code any colours you want, and change the first sheet to whatever you want (hardcode). I also made a script that will sort all the sheets by colour. Anyway here it is. ChatGPT is a marvel at this.
function showSheetsInSidebar() {
var randomColors = ['#440154', '#3e2688', '#31688e', '#21908c', '#35b779', '#fde725', '#b2df8a', '#6baed6', '#fdae61', '#d7191c', '#9e0142', '#d55e00', '#f0e442', '#0072b2', '#b0a5c1', '#7570b3', '#4d4f88', '#4e79a7', '#f28e2b', '#e15759', '#76b7b2', '#59a14f', '#edc948', '#af7aa1'];
var randomColor = randomColors[Math.floor(Math.random() * randomColors.length)];
var fontColor = getContrastYIQ(randomColor);
var html = "<style>" +
"table, th, td { border: 2px solid black; padding: 5px; background-color: " + randomColor + "; color: " + fontColor + "; }" +
"h2 { background-color: "+ randomColor +"; color: " + fontColor + "; padding: 5px; font-family: 'Comic Sans MS', Arial, sans-serif; }" +
"td { font-family: 'Comic Sans MS', Arial, sans-serif; }" +
"th { font-family: 'Comic Sans MS', Arial, sans-serif; }" +
"</style>" +
"<h2>Table Of Contents</h2>" +
"<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() == "Dashboard") {
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>";
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("Jim's Budget")
.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 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() === "Dashboard") return -1;
if (b.getName() === "Dashboard") 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';
}
The standalone sort by colour script is included. I actually am learning how to Apps Script with the help of ChatGPT. Adding the random colours was fun, but not really necessary!
Wow!!! This is truly an amazing and generous gift to the community.
Thank you so very much @chas.bicking for all this time and effort in a super clean and clear explanation of the steps. Will be tackling soon and will be fun to learn some new things about scripts in the process.
@jemmoa7, Would it be possible to send me a screen shot of how the colors and sidebar dashboard all looks with your new script? Thanks so very much!
Or maybe post up this so others can see the cool result?
Thank you!
Larry
You are the best! See my thread post of thanks.
Here’s some screen shots of 3 openings. ChatGPT even was able to use a white font when the color was dark and black when it was light.
I just asked ChatGPT to create the randomcolor variable using the “colours of the rainbow, with 5 shades of each colour.” Much easier than figuring out the colours myself and coding them in!
Wow, thanks for the idea of a trigger! My TOC now opens with the sheet.
I added a "hide active sheet" button, which saves about 4 clicks.
I used the trick of running a macro to hide a sheet,
then copied the macro code to my code.gs and
modified the html button code from the existing
checkbox code.
html += "<input type=‘button’ + value=‘Hide Active Sheet’ / " + " onclick=‘google.script.run.hideSheet()’> < ";
function hideSheet() {
var hideActiveSheet = PropertiesService.getScriptProperties().getProperty(“hideActiveSheet”) || false;
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getActiveSheet().hideSheet();
}
What a great idea! Thanks! Nice to be able to unhide and look at a sheet then hide it again. I had ChatGPT make some font and color modifications to your code. I learn by example and the AI is really good at showing me the code. Sometimes gets it gets it wrong, and the challenge is to word your request properly.
Can you share your final edited code? I’ve had trouble adding the modification to add the Hide Current Sheet box.
Here’s my latest “build”. I’ve made it a little easier to change the font and colours by adding variable for those called later in the script. The “Dashboard” reference is so you can pick the sheet you want to open first be at the top of the list no matter where it actually lies.
Here is the entire set of scripts for the TOC.
function showSheetsInSidebar() {
var randomColors = ['#440154', '#3e2688', '#31688e', '#21908c', '#35b779', '#fde725', '#b2df8a', '#6baed6', '#fdae61', '#d7191c', '#9e0142', '#d55e00', '#f0e442', '#0072b2', '#b0a5c1', '#7570b3', '#4d4f88', '#4e79a7', '#f28e2b', '#e15759', '#76b7b2', '#59a14f', '#edc948', '#af7aa1'];
var randomColor = randomColors[Math.floor(Math.random() * randomColors.length)];
var dashboardName = "Dashboard"; // this is the sheet that will be listed first and in italics
var fontColor = getContrastYIQ(randomColor);
var fontName = "Nunito Sans"; // Set the font name here
// var fontName = "Overpass"; // Set the font name here
var html = "<style>" +
"table, th, td { border: 2px solid black; padding: 5px; background-color: " + randomColor + "; color: " + fontColor + "; }" +
"h2 { background-color: "+ randomColor +"; color: " + fontColor + "; padding: 5px; font-family: " + fontName + ", sans-serif; }" +
"td { font-family: " + fontName + ", sans-serif; }" +
"th { font-family: " + fontName + ", sans-serif;, sans-serif; }" +
"</style>" +
"<h2>Table Of Contents</h2>" +
"<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() === dashboardName) {
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' style='color: white; background-color: " + randomColor + ";'><input type='button' value='Hide Sheet' style='background-color: " + randomColor + "; color: " + fontColor + "; font-family: " + fontName + ", sans-serif' onclick='google.script.run.hideSheet()'></td></tr>";
for (var i = 0; i < sheets.length; i++) {
var sheetName = sheets[i].getName();
var sheetStyle = (sheetName === dashboardName) ? "font-style: italic;" : "";
if (!sheets[i].isSheetHidden() || showHiddenSheets == "true") {
html += "<tr><td><input type='radio' name='sheet' value='" + sheetName + "' onclick='google.script.run.goToSheet(this.value)'></td><td style='" + sheetStyle + "'>" + sheetName + "</td></tr>";
}
}
html += "</table>";
var ui = HtmlService.createHtmlOutput(html)
.setTitle("Aspire Budget - Enhanced by Tiller")
.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 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';
}
function hideSheet() {
var hideActiveSheet = PropertiesService.getScriptProperties().getProperty("hideActiveSheet") || false;
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
if (sheet.isSheetHidden()) {
Logger.log("Sheet is already hidden.");
return;
}
sheet.hideSheet();
PropertiesService.getScriptProperties().setProperty("hideActiveSheet", true);
}