Creating drop down lists with javascript in a Google Spreadsheet

This function builds on my previous post about passing a range of data from a google spreadsheet into an array. Taking it one step further, I now want to display (some of) that data in a drop down list box that allows the user select a record so we can then perform actions on it e.g. displaying the data.

The starting point for this exercise is the Google Developer site, specifically this list box widget code.

The code creates a "panel" that sits above the spreadsheet - this is a graphical interface that will display text, buttons to etc. (a message box or input box is a simple example of a panel). Then I create a list box, to which I add the list items (these will come from the array). I add that list box to the panel, then add a button to the panel as well. The button has a click handler associated with it - this tells it what to do on the event of a click. In this case it'll call on another function that I'll define which simply displays the entry that the user has selected in the list box.

The data for this example is simple enough:

The script from my previous blog post adds this to an array so here I want to add code to the end of that script - the snippets below are not standalone, they will only work when added to the code from my previous example. Before the final closing "}", I add this code to declare the variables needed for the graphical panel and the list box:

  var app = UiApp.createApplication().setTitle('Search');
  var panel = app.createVerticalPanel();
  var lb = app.createListBox(true).setId('myId').setName('myLb');

Now I need to add some items to the list box. I want to do this using the variables I have stored in my array. Recall, the array is sized to hold all of the line items from my data range, which contains "N" rows in this case (where N is defined to equal the last row count of the data range). So I use a for loop to cycle through the values in the array and add each one to the list box (the array holds N rows, which are each in turn an array holding values "index", "name1", "name2" and "web" (see header row of image above)). In this case I've chosen to add name1 and name2 to my list box. The code is as follows:

  //create the list box items
  lb.setVisibleItemCount(4); // how many items show at once
  for (var k = 1; k < N; k++) {
    // add items to ListBox
    var listBoxString = mainObject[k].name1 + " " + mainObject[k].name2;
    lb.addItem(listBoxString);
  }

Finally, I want to add a button and event handler so that I can do something once the user has selected an entry in the list box. Several things happen in this snippet of code: I declare a button labeled "Select"; I declare a variable called "handler" that calls on the function called "select" and passes the data from the panel (i.e. the list box); and finally, I associate this handler to the button. I don't pretend to fully understand this yet but there are more details here on the Google Developer website.

  //add button and click handler
  var button = app.createButton('Select');
  var handler = app.createServerClickHandler('select').addCallbackElement(panel);
  button.addClickHandler(handler);

The final piece of the code is required to add the list box and the button to the panel, then add the panel and then display the panel.

  panel.add(lb);  // add list box to panel
  panel.add(button); // add button to panel
  app.add(panel);  // add panel to the app
  spreadsheet.show(app); // display the app i.e. the panel with list box

Adding this code to the array code from my earlier blog post should give the following output:

Cool huh! This took me quite a while to work out, progressing by adding static items to the list box, then data in static cells in my worksheet, then data from a static array I created, and finally the data from my dynamic array. I was stoked to get it working. The final piece of code is the "select" function that the button handler calls on - it's a simple display box to show the entry the user has selected, as follows:

function select(eventInfo) {
  var value = eventInfo.parameter.myLbName;
  Browser.msgBox(value);
  return app; 
}

The output should show:

Voila! Next up, doing something more useful than just displaying the entry from the list box.