Our spreadsheet only has 2 columns for data to match students with tutors. The first column is student information. The second has "yes" or "no" for availability with a tutor. We want it so that after students have been selected from the Google Form by a tutor, they are marked "No" on the sheet and their information removed from the Form options:

function updateCHECKBOX() {
const ws = SpreadsheetApp.getActiveSpreadsheet();
const studentsSheet = ws.getSheetByName("Students")
const students =studentsSheet.getRange(2,1,studentsSheet.getLastRow(),1).getDisplayValues()

const studentsList = students.filter(row=>row[1]=="YES").map(row => row[0])

const form = FormApp.openById("1FvM3v3Cg1b2lSRTJpp3V_pl369wkJoAS7ypO0J4AhQg")
const studentsQuestion = form.getItemById ("2116012197")
studentsQuestion.asMultipleChoiceItem().setChoiceValues(studentsList)
}

However, when we run the final code, we get "Error, Exception: Array is empty: values". Images of the sheet and form are below:

"Students" sheet

Google form

CodePudding user response：

If you only have two columns of data

The perhaps this:

const datesList = dates.filter(row=>row[2]=="FREE").map(row => row[0]) 

should be this:

const datesList = dates.filter(row=>row[1]=="FREE").map(row => row[0])

I would have to see the spreadsheet and more of the code to be sure.

It should be fairly simple to figure out which rows to want to keep?