Load List box from sheet

In this example, I'll show you how to load a list-box items from a spreadsheet.

This is useful when you have a large number of items in a List-box, or you need to change the list Box items frequently.

I have used below spreadsheet to load the List Box items.

There are two sheets in this spreadsheet namely 'List1' & 'List2'.

List1 is having the item list for First List Box.

List2 is having the item list for Second List Box.

Spreadsheet Preview:

example List Box demo:

List Box item demo

Example Code:

//spreadsheet key is neede to access the spreadsheet.

//Make sure you own the spreadsheet which you are using.

//You can use public spredsheet also

var itemSpreadsheetKey = 'tVBP74_aSlhBrBSTi7kCpZA';

//Open the spreadsheet and get the sheet objects

var openedSS = SpreadsheetApp.openById(itemSpreadsheetKey);

var sheetList1 = openedSS.getSheetByName("List1");//Spreadsheet must match with sheet name

var sheetList2 = openedSS.getSheetByName("List2");//Spreadsheet must match with sheet name

//Function to create Userinterface on site page

function doGet() {

  var app = UiApp.createApplication();

  var panel = app.createVerticalPanel();

  var label1 = app.createLabel('This is first List Box').setStyleAttribute('color', '#006400');

  var listBox1 = app.createListBox().setWidth('150px');

  numItemList1 = sheetList1.getLastRow()-1;//-1 is to exclude header row

  //get the item array

  list1ItemArray = sheetList1.getRange(2,1,numItemList1,1).getValues();

  //Add the items in ListBox

  for(var i=0; i<list1ItemArray.length; i++){

    listBox1.addItem(list1ItemArray[i][0])

  }

 

  //Similarly for Second List Box

  var label2 = app.createLabel('This is Second List Box').setStyleAttribute('color', '#8B0000');

  var listBox2 = app.createListBox(true).setWidth('150px');

  numItemList2 = sheetList2.getLastRow()-1;//-1 is to exclude header row

  list2ItemArray = sheetList2.getRange(2,1,numItemList2,1).getValues();

  for(var i=0; i<list2ItemArray.length; i++){

    listBox2.addItem(list2ItemArray[i][0])

  }

  panel.add(label1)

    .add(listBox1)

    .add(label2)

    .add(listBox2);

  app.add(panel);

  return app;

}