Google Apps脚本，用于循环无法自动迭代使用嵌套if语句

Question

概述 我正在构建一个脚本，以基于先到先得的排名偏好输入数据将个人分配给组。该脚本使用两个循环，选择Student1，然后选择1、2、3。然后检查哪个组以查看哪个组与学生的首选匹配，如果该组低于当前容量，则它将将将Student1复制到该小组，然后转到Student2选择1、2、3。

问题 ..需要包括I ++循环迭代触发器（第46行） - 我的理解是，在满足循环的所有必需标准之后，它将单独迭代。在第46行中删除I ++会打破脚本。

具有这种迭代触发的结果是，在i = revercount（学生的回答数），分配了最后一个学生后，i ++仍将迭代计数器，然后导致

“ TypeError：无法读取未定义的属性'0'

，因为它试图在响应数组的范围之外读取。

我尝试过的 为了防止此错误，我添加了检查以查看响应数组是否未定义（第21行），但是这仅仅掩盖了问题。

我是编码的新手，并希望编写清晰可维护的代码。

示例电子表格：

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
          }
        }
      }
    }
  }
}

sharing

？	=	sharing
usp	>	=	“
https://docs.google.com/spreadsheets/d/1pr8r2cjapy6yabw5yi3ty0nio-u9jlkrjg5jxv8jxv8s3fa/edit？usp	​	​	​
Keagan McGuire	T	Y	R
Randy Robins	E	R	W

Edit 2-感谢您的所有有用的回复。错误是由于没有从嵌套的IF中脱离而引起的。

编辑3-截至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;
          }
        }
      }
    }
  }
}

Answer 1

不确定这是否会完全解决

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
}

问题

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...
  j = choices+1;  // will terminate j loop and continue i loop
  break;  // break out of k loop
}

您

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"
}

我

的 OP不安全。它不能保证从另一个范围获得适当的行数。它需要2个getValues（）呼叫，并且最多可以长1000或更多的空行。

假设我们有以下电子表格。

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    const respCount = sheet.getRange("A1:A").getValues().filter(String).length //Number of responses to parse
    const responses = sheet.getRange("A1:D"+(respCount+1)).getValues() //All students & responses as an array. (+1) gets accurate range size due to header row
    console.log(respCount);
    console.log(responses);
  }
  catch(err) {
    console.log(err);
  }
}

2:10:50 PM  Notice  Execution started
2:10:51 PM  Info    5
2:10:51 PM  Info    [ [ 'A', 1, '', '' ],
  [ 'B', 2, '', '' ],
  [ 'C', 3, '', '' ],
  [ '', 4, '', '' ],
  [ '', 5, '', '' ],
  [ 'D', 6, '', '' ] ]
2:10:51 PM  Notice  Execution completed

最喜欢的方法IMHO。它打电话给所有值并从空白开始，以获取所有值并过滤行。我不需要2个阵列可以使用。它还只能获得包含数据的行。

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    let responses = sheet.getDataRange().getValues();
    responses = responses.filter( row => row[0] !== "" );
    console.log(responses);
  }
  catch(err) {
    console.log(err);
  }
}

2:12:23 PM  Notice  Execution started
2:12:24 PM  Info    [ [ 'A', 1 ],
 [ 'B', 2 ],
 [ 'C', 3 ],
 [ 'D', 6 ],
 [ 'E', 7 ] ]
2:12:24 PM  Notice  Execution completed