Google Script数组求值为null求助：迟到邮件通知函数故障

我改编了一个之前可用的函数，用于发送学生迟到次数的邮件通知，但当前无法正常运行。问题出在gradeNameList变量的处理环节：使用for循环和if语句进行匹配扫描时，数组求值为null。执行函数时，期望向"1666@usa.org"发送邮件并抄送g@hotmail.com，以下是相关代码，恳请提供解决思路：

function likeMessage() {
  
  var mainSheet = SpreadsheetApp.openById('1giEC08YlkXhwIibHt30BdDJmuwwlq9ZzfQdhkQETogo'); 
  var tardyTrackerSheet = mainSheet.getSheetByName('new_tardy_tracker_form_responses'); 
  var tardyTrackerLastRow = tardyTrackerSheet.getLastRow(); 
    
  var teacher = tardyTrackerSheet.getRange(tardyTrackerLastRow, 2).getValue();  
  var tardyBlock = tardyTrackerSheet.getRange(tardyTrackerLastRow, 7).getValue();
  var grade = tardyTrackerSheet.getRange(tardyTrackerLastRow, 3).getValue();
      
  var gradeColumn; //declared a variable, so we can tweak it next...
  
  var adminTeam = ""; 

  if (grade == "sixth") { 
    gradeColumn = 3;
    adminTeam = "g@hotmail.com";
  } else if (grade == "seventh") {
    gradeColumn = 4;
    adminTeam = "do@lcps.org";
  } else if (grade == "eighth") {
    gradeColumn = 5;
    adminTeam = "j@lcps.org";
  }

  var studentName = tardyTrackerSheet.getRange(tardyTrackerLastRow, gradeColumn).getValue();
  
  var gradeSheet = mainSheet.getSheetByName(grade); 

  var gradeSheetLastRow = gradeSheet.getLastRow();  
  var gradeNameList = gradeSheet.getRange(2, 1, (gradeSheetLastRow - 1)).getValues();
  var studentRow = 0; //declaring, to use below
  var flag = true;
  
  var i;
  for (i = 0; i < gradeSheetLastRow; i++) { 
    if (gradeNameList[i] == studentName) {
      studentRow = i + 2;
      }
    else {
      flag = false;  
    }
  }
    

  var emailMessage = "";
  var studentId = "";
  var studentEmail = "";
  var currentTotalTardies = "";
  var parentEmails = "";

  if (flag){
    studentId = gradeSheet.getRange(studentRow, 2).getValues();  
    studentEmail = studentId +"@lcps.org";  
    currentTotalTardies = gradeSheet.getRange(studentRow, 19).getValues(); 
    parentEmails = gradeSheet.getRange(studentRow, 20).getValues(); 
      
    emailMessage = "Dear sir, etc.";  

      MailApp.sendEmail({
      to: studentEmail,
      cc: adminTeam,
      subject: receivedLikeSubject,
      htmlBody: receivedLikeMessage
      });
  }     
}

问题分析与修复方案 #

1. 数组匹配逻辑错误 #

getValues()返回的是二维数组，gradeNameList[i]实际是一个包含单个元素的数组（如["张三"]），直接与字符串studentName比较永远不相等。需修改为：

if (gradeNameList[i][0] == studentName)

2. 循环中flag的错误赋值逻辑 #

当前循环只要有一次不匹配就将flag设为false，哪怕之前已找到匹配项。应调整为找到匹配后立即跳出循环，并仅在循环结束后确认未找到时才标记flag为false：

var flag = false; // 初始标记为未找到
for (i = 0; i < gradeNameList.length; i++) { 
  if (gradeNameList[i][0] == studentName) {
    studentRow = i + 2;
    flag = true;
    break; // 找到匹配后终止循环
  }
}

3. 单个单元格取值方法误用 #

单个单元格应使用getValue()而非getValues()（后者返回数组），否则会导致后续拼接邮箱等操作出现格式错误：

studentId = gradeSheet.getRange(studentRow, 2).getValue();  
currentTotalTardies = gradeSheet.getRange(studentRow, 19).getValue(); 
parentEmails = gradeSheet.getRange(studentRow, 20).getValue(); 

4. 未定义的邮件变量 #

代码中使用的receivedLikeSubject和receivedLikeMessage未定义，需补充实际内容：

var receivedLikeSubject = `迟到通知：${studentName}累计迟到${currentTotalTardies}次`;
var receivedLikeMessage = `您好，学生${studentName}目前累计迟到${currentTotalTardies}次，请关注。`;

5. 边界情况校验 #

增加工作表存在性判断，避免因工作表名错误导致的空值异常：

if (!gradeSheet) {
  console.error(`未找到名为${grade}的工作表`);
  return;
}

内容的提问来源于stack exchange，提问作者gonzalo2000