I totally agree. It’s going to allow me to do things in Sheets that I couldn’t have before.
ScottC
This worked for me without any changes. I just copied the code you provided and pasted into a new script.
After authorizing Permissions, it ran without errors. The Sidebar with radio buttons for each tab showed up as you described.
Do you have any modifications in mind?
Not to complicate things but … possibly add something about hidden sheets if there are any. e.g., if some sheets are hidden, change the title of the sidebar to something like, “Showing Unhidden Sheets”
Or ask ChatGPT to add another radio button to toggle Hidden Sheets On or Off.
Once again, this is very useful. I’ve added it to several Google Sheets that I use frequently. It would also be a good addition to the Default Template but I don’t think my Google Workspace account allows me to modify my Default template. Something for me to research.
jemmoa7
With ChatGPT’s help, I modified your code to add a checkbox that toggles the inclusion of hidden tabs in the Sidebar. It also updates the sidebar when tabs are added or deleted.
I had help from ChatGPT on this. As an aside, I also asked ChatGPT to modify the code to change the background color of the area containing “Table of Contents” to a shade of blue instead of gray. I went round and round with ChatGPT on this but I gave up after about 15 tries. Either my “prompts” were wrong or I found a weakness that I’m sure will go away with time. I also noticed that ChatGPT suggested code that worked in Excel and not in Google Sheets even though I was explicit about needing help modifying a script for Google Sheets. When I told ChatGPT about the error, it acknowledged that the code wouldn’t work in Sheets and came back with the correct code. It made me wonder …if it already knew the code wouldn’t work in Sheets, why suggest it in the first place?
Here’s the code that does work. It doesn’t change the color but it does add a checkbox that toggles the inclusion of hidden sheets in the Sidebar.
// 2023-02-11 modifies Jemmoa7’s code to add a checkbox to the sidebar to toggle the inclusion of hidden sheets in the Sidebar.
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(300);
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();
}
1 Like
I managed to get ChatGPT to tell me how to change the background color in the script (the latest one with the hidden sheet toggle). I provided the script, so it didn’t have to worry about that. Here is the result I got.
function showSheetsInSidebar() {
var html = "<style>" +
"table, th, td { border: 2px solid green; padding: 5px; background-color: #003366; color: #FFFFFF; }" +
"</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(300);
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();
}
I know this isn’t what you were asking, so I’ll try to change the background of Table of contents and use the same approach with ChatGPT
UPDATE: I tried, but I have a suspicion that the gray title bar is not possible to change color, as that’s not really a part of apps script. Possibly somebody out there would know. I’m finding that asking ChatGPT how to change things within already created code gives better results.
Thanks.
When I asked CPT to change the background color of the area holding the text, Table of Contents, it seemed to understand but the code it sent either changed the color of the area with the radio buttons or it replaced the text in the title area with the html code that was supposed to change the color. We went back and forth over and over again but no luck.
If you manage to get it to work, I’d be interested in seeing the prompts you used to get the code that changes the background of the title area.
ScottC
That’s the same result I got. It can easily add a coloured title, but doesn’t seem to be able to change the gray background title at the top. I have a suspicion that this is not possible.
I have been submitting some of my scripts to ChatGPT and asking it to “make them more efficient”. It’s amazing what it does! It makes the script smaller and does the same thing. It even tells me what changes it made and why.
I don’t know html which makes me wonder if the area I’m referring to in the sidebar isn’t called the “title area.” I’ve been describing it as the area containing the words, “Table Of Contents” which seems pretty clear but that’s my best guess at this point.
ScottC
I’m trying to keep up here but got lost. I created the script but what do I need to do on my TOC sheet? Thanks for your creativity!!
I can see why you’re confused. Over the course of the last 2 weeks, this thread has evolved into 2 different approaches to creating a Table of Contents . In hindsight, the two approaches should not have been in the same thread.
The first post described a way to create a custom function that works just like a normal function. Once you add the script and save it, you will have a new formula that you can enter into a cell. The first post in this thread describes how to use it.
As the conversation in thread evolved, the discussion switched to the creation of a Sidebar menu created with a script. Jemmoa7 and I have been trading examples of scripts that incrementally add features to the sidebar but I don’t think the sample code we’ve posted could be considered ready for widespread adoption. If you like to tinker with apps script, that’s different.
If you’re interested, the next version I’m working on will add a custom menu to a workbook that contains at least one choice, “Show Sidebar.” Once this code is added to a spreadsheet, it will load / display the sidebar when a workbook is opened. It will refresh itself when a tab is added or deleted and it will have a checkbox that toggles the visibility of sheets that are hidden. If the sidebar is closed for any reason, the Show Sidebar in the new custom menu will reopen it. That’s the goal.
To prevent more confusion, I’m going add this script to a new thread in the Discussion Area.
Honestly, I think our posts should have made it clear that the code we were showing wasn’t ready for widespread adoption. At least that’s my feeling about it. Jemmoa7 may feel differently.
So, in conclusion, if you feel like “tinkering” you can try the code I posted at the start that creates a custom formula that can be called with =Sheetnames(parameters described in the 1st post). I think the instructions are pretty clear. That’s definitely the least complicated.
Or you can try the most recent examples of code posted by me or jemmoa7. This is more complicated but a better way to have a Table of Contents readily accessible.
But … either way, I’d suggest testing this on a copy of your Tiller sheet or, better yet, with another spreadsheet. Just to be safe.
Hope this helps.
1 Like
OK, this is quite amazing. I got ChatGPT to create a script that would sort my sheet tabs by color. (making all colors together). It also made a particular sheet be the first one. Amazing.
I also played with it alphabetizing my sheet tabs. That worked too.
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);
});
}
I told it to only work with sheets not hidden. Nice!
100% agree. It really is an amazing product.
Thanks. Looking forward to this becoming a “how to”.
That is
1) absolutely amazing. 
2) exactly what we were envisioning
3) simple, elegant code.
Do you know if the sidebar width is fixed, or can it be adjusted?
(Of course I would like it to be floating, pop-up on demand, in 1224 colors, with music backup in three part harmony.)
Thanks !!!
nb. I wrote this after seeing only your first showSheetsInSidebar version.
The following versions are even better.
jemmoa7
Is there a way that I can send a direct message to you without posting in this thread?
I’d like to send updated versions of the code to you and compare notes about using ChatGPT for creating Apps Scripts but I don’t want to create confusion by posting intermediate versions in this forum.
Ultimately, I’d like to share the code and “how to install” instructions with the rest of the Tiller Community.
1 Like
Yes, just click on my profile picture and you have the option to send a message.
Jim
I agree it’s getting confusing, but this post should be looked at as more of a “getting to know” ChatGPT and what it is and is not capable of as far as Apps Scripting goes. The TOC is exciting (I use it all the time!) but lets consider this more a "playing with ChatGPT post than a “sidebar” TOC post. I have learned so much about advanced script building simply by working with the AI and trying to figure out what it’s doing. I believe these new AI’s are as important an advancement in tech as Windows 3.0 was many years ago. (yes, I was there and even beta tested Win95 when I used to live in Seattle.) Anyway, lets have fun with this!
Clicking on your picture I get, “This user’s public profile is hidden.” I suppose you get the same when you click on mine. I tried to change my settings to show my email but didn’t see a way to do that. I asked Customer Support how to change it but haven’t heard back yet.
Totally agree with you about ChatGPT.
I’ve been thinking about this as an experimental project to test and learn ChatGPT and Apps Script at the same time but the title of the post doesn’t make that clear.
My experience with ChatGPT is similar to yours. I’ve learned to outline a description of everything I want but I feed it to ChatGPT in small chunks – adding a new feature once the first chunk is tested. Through trial and error, I’ve also learned to tell it not to change what its already done because sometimes it would add a feature but remove another in the process. It’s like Homer Simpson in that way. Homer said he didn’t have time to learn new things because it made him forget things he already knew. The last time he tried to learn something new he said he forgot how to drive a car! 
I’ve also found that it sometimes suggests code that isn’t compatible with Sheets. When I share the error codes returned by Apps Script, ChatGPT immediately recognizes its mistake and offers what it thinks is correct. Makes me wonder why it would suggest code that it already knew was incompatible.
I’ve got a version of the script + html that (1) adds a custom menu with one choice, “Show Sidebar” and (2) adds the sidebar Table of Contents. It’s based on the one you posted last week. It updates automatically when a sheet is added, deleted, renamed or rearranged in the tabs list at the bottom.
But, despite a lot of back and forth with ChatGPT, I haven’t been able to:
- get it show the sidebar when the workbook is opened. I have to choose it from the custom menu.
- add the feature you added to toggle visibility of hidden sheets.
- add a feature to sort the sheets alphabetically.
- change the color of the topmost title bar that contain the text “Table of Contents” Like you said, I’m not sure it can be changed.
I’ve been able to get versions that do some of these but not all – even if I tell ChatGPT to add one at a time. Adding one or more of these features seems to break or remove others.
Happy to share the code I have with you.
Hi,
I was able to click on your profile and send a message. I check mine out and I think it was set to not show my profile. So I unchecked it so it should be seen publicly and so I think now you can send a message.
Also, as an aside, are you finding the $20 per month for a ChatGPT Plus subscription is providing more value than the free version?
After a few tries, ChatGPT added the ability to sort the sheets in the sidebar as well as display hidden sheets or not. It was interesting that some times it would not finish the script, but when I asked it to complete it and gave it the last line it did, it actually completed it. I started asking it to please finish the script in it’s entirety. That seemed to help! Man, I thought I was in the Star Trek universe conversing with “Computer”. Here is the code:
function showSheetsInSidebar() {
var html = "<style>" +
"table, th, td { border: 2px solid green; padding: 5px; background-color: #003366; color: #FFFFFF; }" +
"h2 { background-color: green; color: white; padding: 5px; }" +
"</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;
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>";
if (sortAlphabetically == "true") {
sheets.sort(function(a, b) {
return a.getName().localeCompare(b.getName());
});
}
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();
}
It seems to write concise, elegant code. Remember how our math skills became rusty with the advent of electronic calculators? Well, now it seems coding skills may go the same way! How nice to concentrate on what you want, and not how to get there. ChatGPT has a ways’ go, but it’s on track so far!