Not display multiple choice

Question

I have a script Im working on for Google sheets. I want it to only display a multiple choice if the ticket is open and not display if the ticket is closed.

What I am getting is it will not display the Value but have a blank option to select.

Any idea how to remove the option?

function updateForm() {
    var formId="formid";
    var form = FormApp.openById(formId);
    var ss = SpreadsheetApp.getActive();
    var tickedID = form.getItemById("779001018").asMultipleChoiceItem();
    var tickets = ss.getSheetByName("Form Responses 1");
    var theTickets = tickets.getRange(2,1, 
    tickets.getLastRow(),9).getValues();
    var TicketArray = [];
    var ticketLength = theTickets.length;
    for(var i = 0; i < ticketLength; i++){ 
        if(theTickets[i][2] ==="Open"){      
            var text = String(theTickets[i][0] + " - "+ theTickets[i][5]);
            TicketArray[i] = TicketArray.push(text);
            tickedID.setChoiceValues(TicketArray);
        }else{
            TicketArray[i] = theTickets[i][0] + 'XXX';
            TicketArray.pop();
        }
    }
}

This is as far as I can get it to go so far

Answer 1

I believe your goal is as follows.

• You want to retrieve the rows that column "C" which has a value of "Open", and you want to create the values using columns "A" and "F" from the retrieved rows. And, you want to put the values into the existing multiple-choice item in a Google Form.

The argument of the method setChoiceValues of Class MultipleChoiceItem is String[]. So, in your situation, I thought that an array including your expected values could be used. When this is reflected in your script, how about the following modification?

Modified script:

function updateForm() {
  var formId = "formid";
  var form = FormApp.openById(formId);
  var ss = SpreadsheetApp.getActive();
  var tickedID = form.getItemById("779001018").asMultipleChoiceItem();
  var tickets = ss.getSheetByName("Form Responses 1");
  var theTickets = tickets.getRange(2, 1, tickets.getLastRow(), 9).getValues();

  // I modified the below script.
  var filtered = theTickets
    .filter(r => r[2] == "Open") // The specific rows are retrieved.
    .map(r => `${r[0]}-${r[5]}`); // Create your expected values using columns "A" and "F".
  tickedID.setChoiceValues(filtered); // Put the values to the multiple-choice item.
}

Note:

• In this modification, it supposes that your item ID and form ID are valid values. Please be careful about this.

• From TicketArray[i] = theTickets[i][0] + 'XXX';, if you want to use this value when column "C" has no value of "Open", please modify the above script as follows.

  • From

      var filtered = theTickets
        .filter(r => r[2] == "Open") // The specific rows are retrieved.
        .map(r => `${r[0]}-${r[5]}`); // Create your expected values using columns "A" and "F".
      tickedID.setChoiceValues(filtered); // Put the values to the multiple-choice item.
    

  • To

      var filtered = theTickets.map(r => r[2] == "Open" ? `${r[0]}-${r[5]}` : `${r[0]}XXX`);
      tickedID.setChoiceValues(filtered);
    

References:

• setChoiceValues(values)- filter()
• map()