快速入门:查看 Google Meet 课程的学生出席情况

完成本页面上的步骤可创建一个 Google 表格插件,用于跟踪使用 Google Meet 会议提供的 Google 课堂课程的出席情况。下图显示了已记录考勤的课程:

设置

如需设置本快速入门,请按以下步骤操作:

  1. 创建新的 Google 表格
  2. 在新工作表中,依次选择扩展程序 > Apps 脚本。如果您看到欢迎屏幕,请点击空白项目
  3. 删除脚本编辑器中的代码。
  4. 点击“无标题项目”项目名称,然后将其重命名为“出席情况”。
  5. 依次点击资源 > 高级 Google 服务
  6. 在显示的对话框中,找到并点击 Google Classroom APIAdmin Reports API开启/关闭开关。
  7. 点击 Code.js 文件名右侧的向下箭头。
  8. 选择重命名
  9. 将文件重命名为“出席”。
  10. 将以下代码复制并粘贴到脚本编辑器中:
/* Menu Options */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Options')
      .addItem("Import Last 5 Courses", 'importCourses')
      .addItem('Check Attendance on Current Sheet', 'checkAll')
      .addToUi();
}

/*
  Description: Option for teachers to import their
  most 5 recently created courses
*/
function importCourses() {
  var optionalArgs = {
    teacherId: 'me',
    courseStates: 'ACTIVE'
  };
  var response = Classroom.Courses.list(optionalArgs);
  var courses = response.courses
  for (var i = 0; i < courses.length; i++) {
    var courseName = courses[i].name
    var courseId = courses[i].id
    insertCourse(courseName, courseId)
  }
}

/*
  Description: Create the Sheet for Course
  @param {String} courseName - Name of Course
  @param {String} courseId - Corresponding Classroom ID
*/
function insertCourse(courseName, courseId) {
    var spreadsheetName = courseName + "(" + courseId + ")"
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var yourNewSheet = activeSpreadsheet.getSheetByName(spreadsheetName);

    if (yourNewSheet != null) {
        return
    }
    yourNewSheet = activeSpreadsheet.insertSheet();
    yourNewSheet.setName(spreadsheetName);
    yourNewSheet.appendRow(['Student Name', 'Email Address', 'Replace with Meet Code'])
    yourNewSheet.setFrozenRows(1)
    var studentNames = getRoster(courseId)["studentNames"]
    var studentEmails = getRoster(courseId)["studentEmails"]
    for (var i = 0; i < studentNames.length; i++) {
      yourNewSheet.appendRow([studentNames[i],studentEmails[i]])
    }
    yourNewSheet.autoResizeColumns(1, 2)
    yourNewSheet.setFrozenColumns(2)
  }

/*
  Description: Adds the course's students to the course sheet
  @param {String} courseId - Corresponding Classroom ID
*/
function getRoster(courseId) {
  var studentNames = []
  var studentEmails = []
  var optionalArgs = {
      pageSize: 100
  };
  var response = Classroom.Courses.Students.list(courseId, optionalArgs)
  var students = response.students

  for (var i = 0; i <= students.length; i++) {
    try {
      studentNames.push(students[i].profile.name.fullName)
      studentEmails.push(students[i].profile.emailAddress)
    } catch (err) {
       return { "studentNames":studentNames, "studentEmails":studentEmails }
   }
 }
}

/*
  Description: Retrieves the Meet code from the Course Sheet
  and uses helper function to check attendance
*/
function checkAll() {
  var ss = SpreadsheetApp.getActiveSheet();
  var sheet = ss.getDataRange().getValues();
  for (var i = 2; i < sheet.length * 100; i++){
    var meetCode = getCleanCode(sheet[0][i])
    // No Meet code given
    if (meetCode == null) {
      break;
    }
    else {
      // check whether each student was present in Meet
      checkMeet(meetCode, i+1);
    }
  }
}

/*
  Description: Checks the Meet for attendance of the given student
  @param {String} meetCode - Raw Meet Code from Course Sheet
  @param {Integer} index - Index corresponding to the Student's row
  in the Course Sheet
*/
function checkMeet(meetCode, index) {
  // universal settings - static
  var userKey = 'all';
  var applicationName = 'meet';
  var ss = SpreadsheetApp.getActiveSheet();
  var sheet = ss.getDataRange().getValues();
  for (var i = 0; i < sheet.length-1; i++) {
    var emailAddress = sheet[i+1][1]
    var optionalArgs = {
      event_name: "call_ended",
      filters: "identifier==" + emailAddress + ",meeting_code==" + meetCode
    };
    try {
      var response = AdminReports.Activities.list(userKey, applicationName, optionalArgs);
      var activities = response.items;
      if (activities == null) {
        markAbsent(ss,i+2,index)
      }
      else {
        markPresent(ss,i+2,index)
      }
    } catch (err) {
        continue
     }
  }
}

/*
  Description: Strips any "-' Characters to match needed format
  for Reports API
  @param {String} meetCode - Raw Meet Code from Course Sheet
*/
function getCleanCode(meetCode) {
  try{
    return meetCode.replace("/-/g","")
  } catch (err) { return meetCode; }
}

/*
  Description: Marks the student as absent for their corresponding cell
  @param {Object} sheet - Course Sheet object
  @param {Integer} i - Index of Sheet cell column to be filled
  @param {Integer} j - Index of Sheet cell row to be filled
*/
function markAbsent(sheet, i, j) {
    var cell = sheet.getRange(i, j);
    cell.setValue("Absent");
}

/*
  Description: Marks the student as absent for their corresponding cell
  @param {Object} sheet - Course Sheet object
  @param {Integer} i - Index of Sheet cell column to be filled
  @param {Integer} j - Index of Sheet cell row to be filled
*/
function markPresent(sheet, i, j) {
    var cell = sheet.getRange(i, j);
    cell.setValue("Present");
}
  1. 选择文件 &gt 保存全部

试试看

请按以下步骤尝试本快速入门:

  1. 切换回电子表格并重新加载页面。菜单栏中会显示一个选项菜单。
  2. 点击选项 &gt;导入过去 5 个课程。系统会显示一个对话框,指示脚本需要授权。
  3. 点击继续。第二个对话框会请求对特定 Google 服务进行授权。点击允许。现在,该电子表格中包含一张表格,在底部以制表符表示您的每 5 门课程。每门课程的第一列显示参加者名单,第一行显示 Google Meet ID。
  4. 点击其中一个课程表。
  5. 点击选项 &gt;在当前工作表中查看出席情况。系统会为当前课程的 Google Meet ID 输入出席情况。
  6. 为另外 4 个类重复第 4 步和第 5 步。

发布

这是一个示例插件,我们的教程到此结束。如果您要开发真正的插件,最后一步是发布该插件,以供其他人查找和安装。

使用其他语言创建出席应用

您还可以使用我们的 REST API 或相应的库来创建出席应用,或向现有远程学习应用添加出席功能。

本快速入门中使用的 REST API 包括:

本快速入门中的方法名称与这些 REST API 中的方法名称相同。例如,AdminReports.Activities.list 对应于 Google Reports API 中的 Activities.list

了解详情

如需继续了解如何使用 Apps 脚本扩展 Google 表格,请查看以下资源: