如何在Google Sheets中通过App Script获取Google Classroom作业并筛选状态

解决方案 #

你需要修改代码中两个核心逻辑：1. 补充获取作业提交的逾期标记late字段 2. 新增状态映射规则，将原始返回值转换为你需要的三种展示状态。
修改后的完整代码如下：

const courseWork = Classroom.Courses.CourseWork.list(courseId, { orderBy: 'dueDate desc' }).courseWork
  .filter(x => _.lowerCase(x.title).indexOf('assignment') !== -1 || _.lowerCase(x.title).indexOf('challenge') !== -1)
  .map(x => {
    return _.assign(
      x,
      // 存储时同时保留提交状态和逾期标记
      { sub: _.groupBy(Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, x.id).studentSubmissions.map(sub => ({
          state: sub.state,
          late: sub.late || false
        })), 'userId') 
      }
    );
  });

const topics = _(courseWork)
  .groupBy('topicId')
  .mapKeys((v, k) => _.find(Classroom.Courses.Topics.list(courseId).topic, ['topicId', k]).name)
  .toPairs()
  .map(x => { return { name: x[0], cw: x[1] }})
  .value();

sheet.getRange('F1:1').clear();
sheet.getRange(1, 6, 1, topics.length + courseWork.length).setValues([_.flatMap(topics, t => [t.name, ..._.map(t.cw, 'title')])]);

// 新增状态映射函数
const getSubStatus = (subInfo) => {
  if (subInfo.state === 'TURNED_IN' || subInfo.state === 'RETURNED') {
    return subInfo.late ? '逾期提交' : '已提交';
  }
  // 其余未提交状态统一返回待提交
  return '待提交';
}

sheet.getRange('C2:100').clear().removeCheckboxes();
sheet.getRange(2, 3, studentsList.length, 3 + topics.length + courseWork.length)
  .setValues(studentsList.map(s => {
    const topicTreeForStudent = topics.map(t => {
      // 替换为映射后的自定义状态
      const sub = t.cw.map(x => getSubStatus(x.sub[s.userId][0]));
      return _.assign(t, { sub: sub, submittedCount: sub.filter(st => st === '已提交' || st === '逾期提交').length });
    });

    return [
      s.profile.name.fullName, s.profile.emailAddress, _.sumBy(topicTreeForStudent, 'submittedCount') / courseWork.length,
      ..._(topicTreeForStudent).map(x => [x.submittedCount / x.sub.length, ...x.sub]).flatten()
    ];
  }));

核心修改说明 #

• 拉取作业提交数据时补充获取late字段，用于区分正常提交和逾期提交
• 新增getSubStatus函数统一处理状态映射，自动将原始CREATED状态转换为对应展示值，不需要额外过滤
• 同步修改提交计数逻辑，将逾期提交也纳入已完成统计，你也可以根据自己的需求调整计数规则

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