I have created a google form which calls the values located in a google sheet. The issue i am having is figuring out how to prevent the user selecting two options which have the same answer from 2 different lists.
Basically this is an option form for a school. For example there are 4 option drop down lists. I dont want a person to be able to select French from two different lists and the value to be rejected. So all values are unique for each of the 4 choices a student makes?
Any help would be appreciated and my code is below. Thanks
function updateForm(){
// call your form and connect to the drop-down item
var form = FormApp.openById("1WJd0Vm-S6q9gLQEwfASCxaQMAYkfRJEuCwhb_vAHAQg");
var namesList = form.getItemById("914739628").asListItem();
var namesList2 = form.getItemById("974702039").asListItem();
var namesList3 = form.getItemById("997497838").asListItem();
var namesList4 = form.getItemById("1335685383").asListItem();
// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Sheet1");
// grab the values in the first column of the sheet - use 2 to skip header row
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();
var namesValues2 = names.getRange(2, 2, names.getMaxRows() - 1).getValues();
var namesValues3 = names.getRange(2, 3, names.getMaxRows() - 1).getValues();
var namesValues4 = names.getRange(2, 4, names.getMaxRows() - 1).getValues();
var studentNames = [];
var studentNames2 = [];
var studentNames3 = [];
var studentNames4 = [];
// convert the array ignoring empty cells
for(var i = 0; i < namesValues.length; i++)
if(namesValues[i][0] != "")
studentNames[i] = namesValues[i][0];
for(var f = 0; f < namesValues2.length; f++)
if(namesValues2[f][0] != "")
studentNames2[f] = namesValues2[f][0];
for(var g = 0; g < namesValues3.length; g++)
if(namesValues3[g][0] != "")
studentNames3[g] = namesValues3[g][0];
for(var h = 0; h < namesValues4.length; h++)
if(namesValues4[h][0] != "")
studentNames4[h] = namesValues4[h][0];
// Check values of data do not match
}
// populate the drop-down with the array data
namesList.setChoiceValues(studentNames);
namesList2.setChoiceValues(studentNames2);
namesList3.setChoiceValues(studentNames3);
namesList4.setChoiceValues(studentNames4);
}
Aucun commentaire:
Enregistrer un commentaire