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;

}