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:


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;
}
 
 


Comments