mercredi 22 janvier 2020

Converting "Create Multiple Choice Form Question from spreadsheet" script to "Create Checkbox etc." script - error?

Question: What is causing the error in this script when replacing "asMultipleChoiceItem()" with "asCheckboxItem"? And is there an obvious way to correct it?

Short version: I am trying to implement a checkbox version of the solution (found here), by replacing "item.asMultipleChoiceItem()" with "item.asCheckboxItem()"; however, I'm encountering an error on debugging "Invalid conversion for item type: MULTIPLE_CHOICE." (debug image here). I'm having trouble troubleshooting to identify/understand, and therefore figure out how to correct, the error.

My code:

function DeptUpdate() {
  // User settings
  var OPTIONS_SPREADSHEET_ID = "1q21HxRkwXxVtiw7D5fuO-w0JCQtZRd-A35gRtmJUwKk";
  var OPTIONS_SHEET_NAME = "Present";
  var OPTIONS_RANGE = "A:A"; // We have the options, listed in column A
  var itemNumber = 1;         // which question on form does this apply to
  //
    var options2DArr = SpreadsheetApp.openById(OPTIONS_SPREADSHEET_ID).getSheetByName(OPTIONS_SHEET_NAME).getRange(OPTIONS_RANGE).getValues();
  var options = options2DArr.reduce(function(a, e) {
    if (e[0] != "") {
      return a.concat(e[0]);
    }
    return a;
  }, []);
  var form = FormApp.openById("1JHZoCdJrsRIltMwKqWGZizRQuy-2Ak2-XET83s04goc");
   var item = form.getItems()[itemNumber - 1];
  item.asCheckboxItem()
      .setTitle("SELECT NAME")
      .setChoiceValues(options)
      .showOtherOption(true);
}

Long version:

Goal: Google script in Google sheets, on trigger, updates targeted form's checklist options to reflect the items listed in the defined range (excluding blanks).

Purpose/Context: This is one part of a series of forms and spreadsheet that allow me to track arrivals, hall passes out/in, and departures from a study hall in which any 10-20 students from a pool of 120 possible may attend any given day. Spreadsheet is linked to forms to provide a "head's up display" of which students are present, and which are signed out to other locations (this all works fine). Restricting Hall Pass Out and Departure form answer choices (student names) to only those check in as "present" drastically cuts down on time and user errors in the logging system. Currently works with multiple choice, but students frequently arrive/leave in groups. Checkbox (multiple response) would further expedite the tracking process. Spreadsheet is otherwise set up to process multiple response entries; just need the form to appropriately update.

Process/Attempts: I've read of others who adjusted similar (different purpose) scripts to change from dropdown/multiple choice to checkbox without issue ("I just change this and it worked, great!" is the extent of what I've read), but as soon as I change to checkbox, I get the attached error for both the showOtherOption field, and (if that is removed), the setChoiceValues field. I'm thinking it could possibly be an issue with the checkbox item reading the array differently than the multiple choice item does? However, I haven't be able to find anything in the documentation or Q/A posts on a significant difference between the two functions parameters. At this point, I'm just a little flummoxed on what might be causing the issue.

Background: I have no formal (or significant informal) coding training, but have tweaked and adapted a variety of codes for about a decade. I understand the basic processes/concepts of code and programming logic, but lack a substantial/cohesive vocabulary.

I'm including a link to a dummy copy of the spreadsheet and the form, in case that's helpful.

Spreadsheet

Form

Thank you in advance for any insights!

Brandy




Aucun commentaire:

Enregistrer un commentaire