3

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.

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

  • Jacob, that is exactly what I was looking for - thank you so much! The buttons were a nice touch as well. I agree that it makes it a lot easier to understand they they are clickable links. – Doug Sep 7 '13 at 18:15
  • @Doug please mark the answer as best answered. – Jacob Jan Tuinstra Sep 7 '13 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.