Hi everyone
I was getting annoyed at scrolling through the tabs to find the one I wanted so I found some code to create a side menu with clickable links to jump to whichever tab I want to.
Click on Extensions - App Script
in the Code.gs file, paste the following - delete any placeholder code :
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.createMenu('Sidebar Menu')
.addItem('Show sidebar', 'showSidebar')
.addToUi();
}
function showSidebar() {
var ui = HtmlService.createTemplateFromFile('Sidebar.html')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setTitle('Index Sidebar');
SpreadsheetApp.getUi().showSidebar(ui);
}
function getSheetNames() {
// Get all the different sheet IDs
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets().filter(s => !s.isSheetHidden());
return sheetNamesIds(sheets).sort();
}
// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {
var indexOfSheets = [];
// create array of sheet names and sheet gids
sheets.forEach(function(sheet){
indexOfSheets.push([sheet.getSheetName(),sheet.getSheetId()]);
});
//Logger.log(indexOfSheets);
return indexOfSheets;
}
// function to return a button with onclick attribute for each sheet that matches
function returnListItems(text) {
var sheetNames = getSheetNames()
// Checking if there is a search term
if (text) {
sheetNames = sheetNames.filter(n => n[0].includes(text))
}
var htmlString = sheetNames.map(function(d) {
var string = `
<li>
<input
type="button"
value="${d[0]}"
onclick=\"google.script.run.setActiveByName('${d[0]}')\"/>
</li>
`
return string }).join(' ')
return htmlString
}
// Utility function to set Active sheet by name.
function setActiveByName(name) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name)
SpreadsheetApp.setActiveSheet(ss)
}
Click Save
Click the + next to Files to create a new HTML file. Name it Sidebar.html (case sensitive). Paste the following (between the dashes) - delete any placeholder code :
<!DOCTYPE html>
<h1>Index:</h1>
<script>
function removeElement(elementId) {
var element = document.getElementById(elementId);
element.parentNode.removeChild(element);
}
function buildList(text) {
google.script.run.withSuccessHandler(onSuccess).returnListItems(text)
}
function onSuccess(result) {
var element = document.createElement("ol")
element.innerHTML = result
var sidebar = document.getElementById("sidebar")
sidebar.appendChild(element)
}
function getTextAndSearch() {
var text = document.getElementById("text-search").value
removeElement("ol")
buildList(text)
}
</script>
<sidebar id="sidebar">
<input type="button" value="Close" onclick="google.script.host.close()" />
<br>
<input type="text" id="text-search" />
<input type="button" value="Search" onclick="getTextAndSearch()" />
<ol id="ol">
<?!=
returnListItems()
?>
</ol>
</sidebar>
Click Save
Open or reload your Tiller workbook and you should see a “Sidebar Menu” menu option. Click that and choose “Show Sidebar”. You will likely have to give permissions for the code to run but then you should get a sidebar of all of the sheets in the workbook. Note, there is a slight delay after clicking on the buttons or searching.
Hope this is useful for people!