Google Apps Script写入Google Sheets时提交状态与学生信息不匹配问题

问题背景 #

开发了一个程序，用于遍历Google Classroom课程，提取学生姓名、学生邮箱、作业名称和提交状态，并将其插入Google Sheets。但当前遇到问题：提交状态未按正确顺序插入，学生姓名与提交状态无法匹配。日志中能看到正确的对应关系，但写入Google Sheets的结果不符合预期（表格中状态列与学生行错位）。

原代码 #

function courseData() {
  const arguments = {
    teacherId: 'me',
    courseStates: 'ACTIVE'
  };

  try {
    const course = Classroom.Courses.list(arguments).courses
    for(let i = 0; i < course.length; i++){
      Logger.log("course name: " + course[i].name)
      Logger.log("course ID: " + course[i].id)
    }
  } catch (error) {
    Logger.log('Error: ' + error);
  }
}

function getAssignmentSubmissionState() {
  var courseId = 'YOUR_COURSE_ID';

  var assignments = Classroom.Courses.CourseWork.list(courseId).courseWork;
  var students = Classroom.Courses.Students.list(courseId).students;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var newCheckbox = SpreadsheetApp.newDataValidation().requireCheckbox().setAllowInvalid(false).build();

  sheet.clearContents();

  var title = ["name", "email"];
  var studentName = [];
  var studentEmail = [];
  var submissionState = [];
  var assignmentTitle = [];

  for (var i = 0; i < assignments.length; i++) {
    var assignment = assignments[i];
    var submissions = Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, assignment.id).studentSubmissions;

    for (var j = 0; j < submissions.length; j++) {
      var submission = submissions[j];
      var student = students.find(function(student) {
        return student.userId === submission.userId;
      });
      
      Logger.log("NAME: " + student.profile.name.fullName +", ASSIGNMENT: " + assignment.title + ", STATUS: " + submission.state);
      studentName.push(student.profile.name.fullName);
      studentEmail.push(student.profile.emailAddress);
      submissionState.push(submission.state);
    }
    assignmentTitle.push(assignment.title)
    title.push(assignment.title);
  }

  sheet.appendRow(title)
  var lastRow = sheet.getLastRow() + 1;
  
  for (var i = 0; i < studentName.filter((item, index) => studentName.indexOf(item) === index).length; i++) {
    sheet.getRange(lastRow + i, 1).setValue(studentName[i]);
  }
  for (var i = 0; i < studentEmail.filter((item, index) => studentEmail.indexOf(item) === index).length; i++) {
    sheet.getRange(lastRow + i, 2).setValue(studentEmail[i]);
  }

  var listLength = ((submissionState.length/assignmentTitle.length) - (studentName.filter((item, index) => studentName.indexOf(item) === index).length - assignmentTitle.length));
  var finalSubmissionState = [];
  for (var i = 0; i < submissionState.length; i += listLength) {
    var eachSubmissionState = submissionState.slice(i, i + listLength)
    finalSubmissionState.push(eachSubmissionState)
  }

  sheet.getRange(lastRow, 3, finalSubmissionState.length, finalSubmissionState[0].length).setValues(finalSubmissionState); 
  
  sheet.getRange(1, 1, 1, title.length).setValues([title]).setFontWeight("bold");
}

授权范围（Scopes） #

• https://www.googleapis.com/auth/classroom.courses
• https://www.googleapis.com/auth/classroom.coursework.me.readonly
• https://www.googleapis.com/auth/classroom.profile.emails
• https://www.googleapis.com/auth/classroom.profile.photos
• https://www.googleapis.com/auth/classroom.rosters
• https://www.googleapis.com/auth/classroom.coursework.me
• https://www.googleapis.com/auth/classroom.coursework.me.readonly
• https://www.googleapis.com/auth/classroom.coursework.students
• https://www.googleapis.com/auth/classroom.coursework.students.readonly
• https://www.googleapis.com/auth/spreadsheets.currentonly
• https://www.googleapis.com/auth/spreadsheets
• https://www.googleapis.com/auth/classroom.guardianlinks.me.readonly
• https://www.googleapis.com/auth/classroom.guardianlinks.students.readonly
• https://www.googleapis.com/auth/classroom.guardianlinks.students

启用服务（Services） #

• Classroom
• Sheets

日志中的正确对应关系 #

10:38:07 PM Info    NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 4, STATUS: TURNED_IN
10:38:07 PM Info    NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info    NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info    NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info    NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info    NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:08 PM Info    NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info    NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info    NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info    NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info    NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info    NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info    NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info    NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info    NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 2, STATUS: TURNED_IN
10:38:08 PM Info    NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info    NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info    NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:09 PM Info    NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 1, STATUS: TURNED_IN
10:38:09 PM Info    NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 1, STATUS: CREATED
10:38:09 PM Info    NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 1, STATUS: TURNED_IN
10:38:09 PM Info    NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 1, STATUS: CREATED
10:38:09 PM Info    NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 1, STATUS: NEW
10:38:09 PM Info    NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 1, STATUS: TURNED_IN

问题分析 #

原代码的核心问题在于数据存储和匹配逻辑混乱：

1. 用数组存储重复的学生姓名、邮箱和状态，后续去重后直接取原数组索引，无法保证顺序一致
2. 计算listLength的逻辑过于复杂，依赖数组长度的除法和减法，一旦学生数或作业数变化就会出错
3. 拆分submissionState的方式没有考虑学生与状态的对应关系，导致状态错位

解决方案 #

改用对象映射存储每个学生的完整信息，确保每个学生的作业状态与本人绑定，最后统一转换成表格需要的二维数组写入：

function getAssignmentSubmissionState() {
  const courseId = 'YOUR_COURSE_ID';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();

  // 获取作业和学生列表
  const assignments = Classroom.Courses.CourseWork.list(courseId).courseWork;
  const students = Classroom.Courses.Students.list(courseId).students;

  // 构建表头：姓名、邮箱 + 所有作业名称
  const header = ['name', 'email', ...assignments.map(assign => assign.title)];

  // 创建学生映射：以userId为键，存储姓名、邮箱和各作业的状态
  const studentMap = {};
  students.forEach(student => {
    const userId = student.userId;
    studentMap[userId] = {
      name: student.profile.name.fullName,
      email: student.profile.emailAddress,
      // 初始化所有作业状态为空
      ...assignments.reduce((acc, assign) => {
        acc[assign.id] = '';
        return acc;
      }, {})
    };
  });

  // 遍历每个作业，填充对应学生的提交状态
  assignments.forEach(assignment => {
    const submissions = Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, assignment.id).studentSubmissions;
    submissions.forEach(submission => {
      const userId = submission.userId;
      if (studentMap[userId]) {
        studentMap[userId][assignment.id] = submission.state;
      }
    });
  });

  // 将映射转换为表格行数据：每行是[姓名, 邮箱, 作业1状态, 作业2状态, ...]
  const rows = Object.values(studentMap).map(student => {
    return [
      student.name,
      student.email,
      ...assignments.map(assign => student[assign.id])
    ];
  });

  // 写入表头和数据
  sheet.getRange(1, 1, 1, header.length).setValues([header]).setFontWeight('bold');
  sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
}

// 原courseData函数保留不变
function courseData() {
  const arguments = {
    teacherId: 'me',
    courseStates: 'ACTIVE'
  };

  try {
    const course = Classroom.Courses.list(arguments).courses
    for(let i = 0; i < course.length; i++){
      Logger.log("course name: " + course[i].name)
      Logger.log("course ID: " + course[i].id)
    }
  } catch (error) {
    Logger.log('Error: ' + error);
  }
}

修改说明 #

1. 学生映射：用userId作为唯一标识，避免姓名重复导致的匹配错误，每个学生对象包含姓名、邮箱和所有作业的状态字段
2. 状态填充：遍历作业时，直接将提交状态赋值给对应学生的作业字段，确保一一对应
3. 统一生成行数据：最后将映射转换为二维数组，保证每行的状态顺序与表头的作业顺序完全一致
4. 简化写入逻辑：一次性写入表头和所有行，减少多次调用getRange的性能消耗

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