Overview I'm constructing a script to assign individuals to groups based on first-come-first-served ranked preference input data. The script constructs a response order from the data array using two For Loops, selecting student1, and then choice 1, 2, 3. It then checks to see which group matches the student's first choice, and if that group is below current capacity, it will copy student1 to that group and move onto student2 choice 1, 2, 3.
Issue ..The need to include a i++ loop iteration trigger (line 46) - my understanding is that after meeting all of the required criteria for the loop, it would iterate by itself. Removal of i++ at line 46 breaks the script.
A consequence of having this iteration trigger is that at i = respCount (number of student responses), after assigning the last student, the i++ will still iterate the counter, and then cause
"TypeError: Cannot read property '0' of undefined"
as it tries to read outside of the bounds of the responses array.
What I've Tried In order to prevent this error, I have added a check to see if the response array is undefined (line 21), however this merely hides the problem.
I am new to coding, and desire to write clear and maintainable code.
Example Spreadsheet: https://docs.google.com/spreadsheets/d/1pR8r2cjapY6YAbW5Yi3tY0nio-u9JLkRjG5JxV8S3fA/edit?usp=sharing
function assignGroups() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data") //Select the sheet in the document our data is located on
/** Obtain Data Range */
//https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column
const respCount = ss.getRange("A2:A").getValues().filter(String).length //Number of responses to parse
const responses = ss.getRange("A2:D"+(respCount+1)).getValues() //All students & responses as an array. (+1) gets accurate range size due to header row
const choices = 3 //Maximum number of preferences a scholar can declare. Must be edited to suit actual data
//Logger.log(respCount)
//Logger.log(responses.length)
/** Obtain Department Information */
const deptCount = ss.getRange("F2:F").getValues().filter(String).length //Count how many unique departments there are in this range
const departments = ss.getRange("F2:G"+(deptCount+1)).getValues() //Then, grab each department and its associated capacity as an array. +1 because of header rows
/** Construct Response Parse Order */
for (var i = 0; i < respCount; i++){ //Iterate through each row in the responses array, starting at row [0]
for (var j = 1; j <= choices; j++){ //Iterate through each column in the responses array, starting at column [k][1] and ending at column [k][choices]
if (responses[i] == undefined){ //Before declaring the next few variables, check and see if the counter is within the range of the array
break; //Break the script if i=respCount before declaring the next variables, and terminate the script
//This is only needed because I have to manually iterate the script following the nested ifs towards the end of the file
//If this wasn't here, studentName[i] would look outside of array bounds and throw "TypeError: Cannot read property '0' of undefined"
}
var studentName = responses[i][0] //Gets student name from position row i column 1
var studentChoice = responses[i][j] //Gets each student choice from row i column 2 through the upper bound denoted by var choices
//Logger.log(studentChoice+"_"+studentName)
/** Construct a List of Department Names and Capacities */
for (var k = 0; k < deptCount; k++){ //Iterate through each row in the departments array, starting at row [0]
var depID = departments[k][0] //Gets department ID from position row k column 1
var depSize = departments[k][1] //Gets department capacity from position row k column 2
//Logger.log(depID+"_"+depSize)
/** Do Some Assignment Logic */
if (studentChoice == depID){ //If the student choice matches the department ID...
var depSpace = ss.getRange("I2:I").offset(0,k).getValues().filter(String).length
//Get the first column in the department assignment area
//Offset by the department number given by k
//Count how many students are currently assigned to that department
if (depSpace < depSize){ //If the number of students currently assigned to the department is less than its capacity...
ss.getRange("I2").offset(depSpace,k).setValue(studentName); //Copy the current student's name to that department column...
i++;//Move on to next Student || THIS IS CAUSING ISSUES - why does this series not loop by itself without me iterating it?
j=0;//And reset Choice counter j back to choice 1
}
}
}
}
}
}
Edit: Shape of data array
| Names | Pref1 | Pref2 | Pref3 |
|---|---|---|---|
| Zi Yang | R | T | E |
| Reeva Dalton | T | Q | E |
| Keagan Mcguire | T | Y | R |
| Randy Robins | E | R | W |
Edit 2 - Thank you for all of your helpful responses. Error was caused by not breaking out of the nested if.
Edit 3 - Updated code as of 07/02/2022 15:23
function assignDepartment() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data")
/** Obtain Data Range */
const responses = ss.getRange("A2:D").getValues().filter(row => row[0] !== "");
const respCount = responses.length
const choices = 3
/** Obtain Department Information */
const departments = ss.getRange("F2:G").getValues().filter(row => row[0] !== "");
const deptCount = departments.length
/** Construct Response Parse Order */
for (let i = 0; i < respCount; i++) {
for (let j = 1; j <= choices; j++) {
var studentName = responses[i][0]
var studentChoice = responses[i][j]
/** Construct a List of Department Names and Capacities */
for (let k = 0; k < deptCount; k++) {
var depID = departments[k][0]
var depSize = departments[k][1]
/** Assignment Logic */
if (studentChoice == depID) {
var depSpace = ss.getRange("I2:I").offset(0,k).getValues().filter(String).length
if (depSpace < depSize) {
ss.getRange("I2").offset(depSpace,k).setValue(studentName);
j = choices+1;
break;
}
}
}
}
}
}
