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'.
Spreadsheet Preview:List1 is having the item list for First List Box. List2 is having the item list for Second List Box. example List Box 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; }
|
Advanced Examples >