Panel with Vertical Scroll Bar that allows navigating to different worksheets

Question

I am attempting to build a Script that would open a window with each worksheet I have in my spreadsheet, allowing me to click on the worksheet name to navigate to that sheet. I found the following code and it is working well, except for that I would like a vertical scroll bar (I have more worksheets than will fit in the window and even setting the Height higher will not accommodate them all):

function onOpen(event) {   
    var ss = event.source;
    var menuEntries = [];   
    menuEntries.push({name: "Go to sheet...", functionName: "showGoToSheet"});  
    ss.addMenu("Tasks", menuEntries); 
}
function showGoToSheet() {
    var ss =SpreadsheetApp.getActiveSpreadsheet();   
    var allsheets = ss.getSheets();   
    var app = UiApp.createApplication();  
    app.setTitle("Go to sheet...").setWidth(800).setHeight(600); 
    var table = app.createFlexTable();  
    table.setCellPadding(5).setCellSpacing(0);  
    var goToSheetClick = app.createServerHandler('handleGoToSheetClick');   
    var widgets = [];  
    for (var i = 0; i < allsheets.length; i++) {
        var sheet_name = allsheets[i].getName();
        widgets[i] = 
       app.createHTML(sheet_name).setId(sheet_name).addClickHandler(goToSheetClick);
       table.setWidget(i, 1, widgets[i])   
}   
var panel = app.createSimplePanel();   
panel.add(table);   
app.add(panel);  
ss.show(app); }

function handleGoToSheetClick(e) {   
    var sheet_name = e.parameter.source;  
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getSheetByName(sheet_name);   sheet.activate();   
    var app = UiApp.getActiveApplication();   
    app.close();   
    return app; 
}

I have tried to incorporate other bits of code and tinker with this one but no such luck. Again, this does exactly what I want it to do, I just want the window to have a vertical scroll.

Answer 1

You need to add a scroll panel. See revised code I made for you:

Code

// global var
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen(event) {  
  var menuEntries = [({name: "Go to sheet...", 
    functionName: "showGoToSheet"})];   
  ss.addMenu("Tasks", menuEntries); 
}

function showGoToSheet() {
  var app = UiApp.createApplication().setTitle("Go to sheet...")
    .setHeight(400).setWidth(450);
  var sPanel = app.createScrollPanel().setAlwaysShowScrollBars(true)
    .setSize(440, 400);
  var vPanel = app.createVerticalPanel().setSize(400, 400);
  var fTable = app.createFlexTable().setCellPadding(5).setSize(400, 400)
    .setCellSpacing(0).setBorderWidth(1); 
  var allsheets = ss.getSheets();   
  var goToSheetClick = app.createServerHandler('handleGoToSheetClick');     
  for (var i=0, iLen=allsheets.length; i<iLen; i++) {
    var sheet_name = allsheets[i].getName();
    fTable.setWidget(i, 0, app.createButton(sheet_name).setId(sheet_name)
      .setWidth(200).addClickHandler(goToSheetClick)); 
  }
  app.add(sPanel.add(vPanel.add(fTable)));
  ss.show(app);
}

function handleGoToSheetClick(e) {
  var app = UiApp.getActiveApplication();  
  ss.getSheetByName(e.parameter.source).activate();
  app.close();   
  return app; 
}

Remark

If you add buttons, it will make it more intuitive.

Example

I've created an example file for you to see the result: Sheets in Scroll Panel