Friday, June 6, 2014

Code to replace all FORM items

///////////////////////////////////////////////
    // 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