How to create a sidebar list of all sheets in a Google Spreadsheet workbook?

Question

Because I have a Google spreadsheet workbook with so many sheets that they are not all visible in the pulldown on the bottom left side of the spreadsheet workbook, I am attempting to modify the code at "How to get the list of all sheet in a dropdown list in a sidebar" from a standalone script to a bound script in order to be able to see a longer list. Also, I do not want my list in a dropdown. I want it simply as a bulleted list.

I am getting a malformed HTML error that I assume is being generated from within the scriplet. (See code below.) Any ideas what is going wrong with this code.

HTML

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select name="Sheets List" onchange="listSheets()")>
    <? var sheets=spreadsheet.getSheets(); ?>
    <? for(var i=0;i<sheets.length;i++) { ?>
      <option value=<?=sheets[i].getName()?>> <?= sheets[i].getName()?></option>
    <? } ?>
    </select>
  <script>
   function listSheets(){
     var name=document.getElementsByName("Sheets List")[0].value;
     google.script.run.goToSheet(name);
    };
  </script>
  </body>
  </html>

GS

function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Custom')
      .addItem('Sheets List', 'sheetsList')   
      .addToUi();

function sheetsList(){
  var html = HtmlService.createHtmlOutputFromFile('sheets').evaluate
      .setTitle('Sheets in this Workbook')
      .setWidth(300);
  SpreadsheetApp.getUi()
  .showSidebar(html);
}
}

Error Message

Message details Malformed HTML content: > function listSheets(){ var name=document.getElementsByName("Sheets List")[0].value; google.script.run.goToSheet(name); }; .

Why is there a `)` here: `listSheets()"` **)** `>` – TheMaster Dec 15 '19 at 12:32 — TheMaster, Dec 15 '19 at 12:32

score 3 · Answer 1 · answered Dec 15 '19 at 13:28

You have a few issues that are preventing the sidebar from showing:

You should be using HtmlService.createTemplateFromFile(), not createHtmlOutputFromFile().
To generate the HTML, you need to be sure to include the parentheses when calling .evaluate(). (Related to the first point, note that evaluate() is only available for HtmlTemplate objects.)
sheetsList() should not be nested within your onOpen() function.
You need to define spreadsheet in your HTML.

GS

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom')
  .addItem('Sheets List', 'sheetsList')   
  .addToUi();
}

function sheetsList(){
  var html = HtmlService.createTemplateFromFile('sheets').evaluate()
    .setTitle('Sheets in this Workbook')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

HTML In the HTML below, I removed the frontend JS code for listSheets() because it wasn't part of the question's scope and was calling a backend function that was also not included in the question.

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select name="Sheets List">
      <? var spreadsheet = SpreadsheetApp.getActive(); ?>
      <? var sheets=spreadsheet.getSheets(); ?>
      <? for(var i=0;i<sheets.length;i++) { ?>
      <option value=<?=sheets[i].getName()?>> <?= sheets[i].getName()?></option>
      <? } ?>
    </select>
  </body>
</html>

Cooper · Accepted Answer · 2019-12-15T20:07:13.120

Sheet Link List in Sidebar

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();
}

You can get a lot more buttons on the side bar this way:

function allsheetslist() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  var html='<style>input[type="button"]{margin:2px 5px 2px 0;border:none;background:white;}</style>';
  shts.forEach(function(sh,i){
    html+=Utilities.formatString('<input type="button" value="%s" onClick="gotoSheet(%s);" />',sh.getName(),sh.getIndex());
  });
  html+='<script>function gotoSheet(index){google.script.run.gotoSheetIndex(index);}</script>';
  var userInterface=HtmlService.createHtmlOutput(html).setTitle("Sheet Link List")
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

Both of these answers work (@Diego and @Cooper). I chose Cooper's because it creates clickable links (which I want). The 2nd Cooper version very handy too as it allows a much larger population of sheets to be displayed. There was one error in Cooper's code which was a missed capitalization in "allsheetsList". The "l" needed to be "L". Sincere thanks for both of you taking the time to help me with this. — Bee Tee, Dec 16 '19 at 12:54

score 0 · Answer 3 · answered Sep 08 '20 at 12:47

Tried the @Cooper way. Its easier and more friendly for me. But the problem still pending is that the "OnClick" function is not called and nothing happens when the sheet name button is clicked on the side bar. I have even added a specific field to be displayed along with sheet name in the sidebar. That works fantastically. Now the only help needed is to activate the click. I have tried many ways still no success. The code used is as below:

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="google.script.run.gotoSheet(\'%s\');" /></td>', sh.getName(), sh.getName());
    html += Utilities.formatString('<td><input type="button" value="%s" /></td></tr>', sh.getRange("C2").getValue());
  });
  html += '</table>';
  var userInterface = HtmlService.createHtmlOutput(html).setTitle("Sheet Link List");
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

function gotoSheet(name) {
  var ss = SpreadsheetApp.getActive();
  ss.getSheetByName(name).activate();
}

How to create a sidebar list of all sheets in a Google Spreadsheet workbook?

HTML

GS

Error Message

3 Answers3