Create Multiple Choice Question with data from Google Sheet

user_16321514 0
Viewed 64

I am creating a database for my study program's inventory which can be accessed for anyone who want to request using the item. For weeks, I am stuck with making the multiple choice question from the data in my Google Sheet Database.

These codes are the closest that I can do...Instead of creating all the choices, the program only puts different statements inside 1 choice... Could anyone help me making the proper multiple choice please?

function myFunction() {

var ss = SpreadsheetApp.openById('1lSB55vGZeuaJC0OS3zjpuRHEDCXpUTDYC7MPUVr0tMU');  
var form = FormApp.openById('1sH-qxqiiyh3Qqq28KuYoaNftnY3RUe4FhGq6qck2eMg');

var ss_mediaandchemical = ss.getSheetByName('Stock - Media & Chemical').getRange('K2:K').getValues();
var form_mediaandchemical_question = form.getItemById('447046025').asMultipleChoiceItem();


for (var i in ss_mediaandchemical){
 form_mediaandchemical_question.createChoice(ss_mediaandchemical[i]).isCorrectAnswer;
  form_mediaandchemical_question.setChoiceValues(ss_mediaandchemical[i]);

}
}

google-forms google-apps-script
1 Answers

Issues:

setChoiceValues(values) sets all the provided values in the array as choices of the item, removing the choices that were previously set.

Since you are iterating through the different choices, you are replacing each choice you set with the next value in the source array.

Also, getValues() returns a 2D array, and you want to provide a simple array. You can use flat() to fix that. Also, the source data contains several empty cells, I'd suggest removing them from the array (for example, using .filter(String).

Finally, createChoice doesn't add the choice to the item, and it should be used in combination with setChoices. It doesn't make sense to use it if you're then using setChoiceValues.

Solution:

Retrieve a simple array with values, removing empty values, and set the choices directly using setChoiceValues, without iterating through the array:

var ss_mediaandchemical = ss.getSheetByName('Stock - Media & Chemical').getRange('K2:K').getValues().flat().filter(String);
var form_mediaandchemical_question = form.getItemById('447046025').asMultipleChoiceItem();
form_mediaandchemical_question.setChoiceValues(ss_mediaandchemical);

If you want to create the choices via createChoice (it could allow more customization, like setting a choice as correct or adding a navigation item), you can do this instead:

var ss_mediaandchemical = ss.getSheetByName('Stock - Media & Chemical').getRange('K2:K').getValues().flat().filter(String);
var form_mediaandchemical_question = form.getItemById('447046025').asMultipleChoiceItem();
var choices = ss_mediaandchemical.map(value => form_mediaandchemical_question.createChoice(value));
form_mediaandchemical_question.setChoices(choices);
user_106120110
Related