Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
You should try asking this question on StackOverflow. –  slybloty Sep 6 '13 at 23:43
1  
I thought that code looked familiar. webapps.stackexchange.com/a/24969/1344 –  William Jackson Sep 7 '13 at 12:46
    
@WilliamJackson did you like the answer I gave? –  Jacob Jan Tuinstra Sep 7 '13 at 18:50
    
@JacobJanTuinstra I do like it. I added a link to your answer from my original answer. –  William Jackson Sep 8 '13 at 1:51

1 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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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