///////////////////////////////////////////////
// INITIALIZE MULTI-DIMENSIONAL ARRAY
// [NAMED RANGE, NAME ON FORM, TYPE OF ELEMENT]
///////////////////////////////////////////////
var array = [["Members","Who are you?","asListItem"],
["Members","One2One's with members of our group","asListItem"]];
var formID = '1gqkjPbC902ahDZq5Hdud2BlYepuBos_fe4CvKdsfasdq5M' //this is the ID of the form you are using
var ss = SpreadsheetApp.getActive();
//var sheet = ss.getSheetByName("Members");
//var range = sheet.getRange(2, 1, 98, 1);
//range.sort(1);
// open the form and then get all items from inside the form.
var form = FormApp.openById(formID);
var AllFormItems = form.getItems();
form.setTitle(ss.getRangeByName("FormName").getValue()); //RENAME THE FORM HERE
var FormItemId = 0;
// Open the current spread sheet
var ss = SpreadsheetApp.getActive();
// LOOP THROUGH ALL ITEMS ONE AT A TIME
for (var n = 0; n < array.length; n++)
{
// Getting the items in your named range from the spreadsheet
var SheetItemRange = ss.getRangeByName(array[n][0]);
var SheetItems = SheetItemRange.getValues();
// loop through all of the items in the form
for (i=0; i < AllFormItems.length ; ++i)
{
var FormTitle = AllFormItems[i].getTitle();
// Compare the title to that of each item in your form
if (FormTitle == array[n][1])
{
FormItemId = AllFormItems[i].getId();
}
}
// If the item was not found then exit
if (FormItemId == 0) {
return;
}
switch (array[n][2]) {
case "asCheckboxItem":
// Casting item to checkboxItem because the form is using a checkbox
FormItemList = form.getItemById(FormItemId).asCheckboxItem();
break;
case "asListItem":
// Casting item to listItem because the form is using a drop down.
FormItemList = form.getItemById(FormItemId).asListItem();
break;
}
// Create the array of choices and loop through each of them
var SheetItemChoices = []
for (i=0; i < SheetItems.length; ++i) {
SingleSheetItem = SheetItems[i][0].toString().trim();
if (!(SingleSheetItem=='')) {
SheetItemChoices.push(FormItemList.createChoice(SingleSheetItem));
}
}
// Setting the choice array to drop down item.
FormItemList.setChoices(SheetItemChoices);
}
No comments:
Post a Comment