見出し画像

生徒の授業振り返りに個別に返信する【Google Classroom×GAS】

phys_ken

高校教員（物理・情報）です。今回は、Google Apps Script（GAS）を使って作成した簡単なスプレッドシート用ソフトを紹介します。記事の最後には、実際のコードを全文掲載していますので、ぜひお試しください。

作ったソフトでできること

  • Classroomの質問に生徒が回答した内容をボタン一つでスプレッドシートに表示できる

  • スプレッドシート上で各生徒へのフィードバックを記入し、ボタン一つで「生徒の回答と教師からのフィードバック」を各生徒にストリーム上で個別配信できる

この記事の内容と、巷で流行しているスプレッドシートとGemini API連携やchatGPT API連携を組み合わせることで、とても強力なツールが出来上がるはずです。私個人は、すでに本記事の内容とGemini APIを組み合わせることで、生徒の振り返りに自動でAIによるフィードバックを実施しています。スプレッドシート上で生成AIを呼び出す方法は、以下の記事がとても参考になります。

＊注意事項＊
この記事の内容は間違いがないように気を付けているつもりですが、あくまでプログラミングが趣味の素人が書いた内容だという点をご了承ください。
ご利用の際は自己責任でお願いいたします。本ソフトの使用により生じたいかなる不具合に対しても、一切の責任を負いかねます。
ライセンスはGPL3.0で提供します。このコードの自由な発展を期待しますが、二次利用の際にはライセンスについて十分ご理解の上、この記事へのリンク等を張っていただけると嬉しいです。

作ったきっかけ

私の授業の進め方

Google Classroomの質問機能を使って、1時間ごとに生徒に授業資料を配信しています。

Classroom「質問」の例

教師による一斉講義ではなく、『学び合い』形式を目標にしています。「動画でも教科書でも好きなもので学んでよい」「配布した演習問題(4題程度)を全員が終わらせること」「終わった生徒は周りに教えることで自分の理解を深めましょう」と声掛けしています。

『学び合い』は「クラスの全員が、20年後、50年後も幸せに生活できるようになることを目指す授業」です。
上越教育大学の西川純教授が提唱し、全国で実践されています。
（～中略～）
下記の学校観・子ども観・授業観に基づいた教育を『学び合い』と呼びます。
学校観　　学校は、多様な人と折り合いをつけて自らの課題を達成する経験を通して、その有効性を実感し、より多くの人が自分の同僚であることを学ぶ場である
子ども観　子どもたちは有能である
授業観　　教師の仕事は、目標の設定、評価、環境の整備で、教授（子どもから見れば学習）は子どもに任せるべきである

『学び合い』wiki

こうした自由度の高い授業を通して、生徒各自が授業1コマの学びを建前でなく本音で振り返ることで、学習を自己調整する力を身に着けてほしいと願っています。毎時間の授業ふりかえり「授業振り返り(今日やったこと・自己評価(5点満点)・大事だと思ったこと)」は、Classroomの質問に回答する形で提出してもらっています。

Classroomの質問画面(生徒は相互に閲覧可能です。)

Classroomの質問機能では、以下の点がとても便利です。Googleフォームやドキュメント・スプレッドシートを使ったOPPAよりも、私にとっては使いやすいです。

  • 各授業の振り返りをClassroom上で簡単に確認できる

  • 未提出者数も把握しやすい

  • 事後の編集も相互閲覧も可能

  • 教師による返事ができる

授業振り返りへのフィードバック

Classroomの質問機能で生徒が記入した内容に対し、教師が返信することができます。しかし、クラス全員分に返信するとなると「ボタンを押して次へ進む・送信ボタンを押す」作業が積み重なり、結構時間がかかります。そこで、3年前に私が作成した「ストリーム上で個別に連絡するGASアプリ」を活用することで、スプレッドシート上で一気に振り返りの記入をして、最後にボタン一つで一括送信を可能にしました。

冒頭でもふれた通り、スプレッドシート上で生成AI(geminiやchatGPT)が動くようにすれば、生徒への振り返りが即座に作成されます。

わざわざClassroomのストリームを使わずとも、メールによるフィードバックでもいいのですが、個人的には授業に関する連絡はClassroomで完結させたいという思いがあります。

ソフトの準備＆操作方法

GASを使ったことがある方向けの説明になります。未経験の方でも、ChatGPTなどにコードやこの記事をコピーして質問すれば、詳細な操作方法を教えてくれるはずです。
ご質問があれば、この記事にコメントをください。可能な限り対応したいとは思っていますが、趣味の範囲で回答します。レスポンスの早さは期待しないでください。

準備

  • スプレッドシートでGASエディタを開き、この記事の最後にあるコードをコピー＆ペーストします。

  • 「コード.gs」と「appsscript.json」の両方を編集する必要があります。

    • 「appsscript.json」は、GASエディタの「プロジェクトの設定」で「appsscript.jsonマニフェストファイルをエディタで表示する」にチェックを入れると表示されます。

appsscript.jsonの表示方法(スクショ)

  • setupを実行すると、必要なシートが作成されます。

実行方法

  • 「URL」シートに、Classroomの質問のURL（例：https://classroom.google.com/c/（エンコードされたコースID）/sa/（エンコードされた投稿ID）/details）を入力し、上部メニューの「Classroomツール」から「提出物を取得」をクリックします。すると、「Submissions」シートに生徒の提出物が表示されます。

setupを正しく実行すると、タブからコードが実行できるようになります。

  • 「教師フィードバック」（F列）に各生徒への返答を入力し、「Classroomツール」タブから「フィードバックを送信」をクリックすると、各生徒にフィードバックが返信されます。
    ※ここで、冒頭で紹介したgemini関数を使うと、フィードバックが自動生成されます。

※再送防止のためのログ機能や、Classroomからデータを取得する際のログとの照合チェックも実装しています。詳細な説明は割愛しますが、使いながら理解していただけると思います。


コード

ChatGPTと協力しながら作成しました。ライセンスはGPL3.0で提供いたします。

appsscript.json

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Classroom",
        "version": "v1",
        "serviceId": "classroom"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/classroom.coursework.students",
    "https://www.googleapis.com/auth/classroom.courses",
    "https://www.googleapis.com/auth/classroom.rosters",
    "https://www.googleapis.com/auth/classroom.coursework.me",
    "https://www.googleapis.com/auth/classroom.announcements",
    "https://www.googleapis.com/auth/classroom.profile.emails"
  ]
}


コード.gs

/*
2024.10.10 
created by phys-ken
classroom投稿のURLから、生徒のclassroom質問への回答を一括取得する。
また、取得した質問への返答を一括でストリームに返信する。
おまけ機能：
一括返信時にログを記録することで、重複返信を防ぐことができる。
*/


function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Classroomツール')
    //.addItem('初期設定', 'setup')
    //.addSeparator()
    .addItem('提出物を取得', 'writeStudentSubmissions')
    .addItem('フィードバックを送信', 'postFeedbackToStudents')
    .addToUi();
}

function setup() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheets = ss.getSheets();



    // URLシートの設定
    let urlSheet = ss.getSheetByName('URL');
    if (urlSheet) {
      ss.deleteSheet(urlSheet);
    }
    urlSheet = ss.insertSheet('URL');
    urlSheet.clear();
    urlSheet.getRange('A1').setValue('ここに課題のURLを入力してください');
    // 2行目以降と2列目以降を削除
    const maxRows = urlSheet.getMaxRows();
    const maxColumns = urlSheet.getMaxColumns();
    if (maxRows > 1) {
      urlSheet.deleteRows(2, maxRows - 1);
    }
    if (maxColumns > 1) {
      urlSheet.deleteColumns(2, maxColumns - 1);
    }

    // Submissionsシートの設定
    let submissionsSheet = ss.getSheetByName('Submissions');
    if (submissionsSheet) {
      ss.deleteSheet(submissionsSheet);
    }
    submissionsSheet = ss.insertSheet('Submissions');
    submissionsSheet.clear();
    submissionsSheet.appendRow(['生徒ID', '生徒メールアドレス', '生徒名', '提出状況', '回答', '教師フィードバック', '送信状況']);

    // 課題と評価方法シートの設定
    let assignmentSheet = ss.getSheetByName('課題と評価方法');
    if (assignmentSheet) {
      ss.deleteSheet(assignmentSheet);
    }
    assignmentSheet = ss.insertSheet('課題と評価方法');
    assignmentSheet.clear();
    assignmentSheet.getRange('A1').setValue('クラス名');
    assignmentSheet.getRange('A2').setValue('課題名');
    assignmentSheet.getRange('A3').setValue('課題本文');
    
    // フィードバッククラスログシートの設定
    let classLogSheet = ss.getSheetByName('フィードバッククラスログ');
    if (classLogSheet) {
      ss.deleteSheet(classLogSheet);
    }
    classLogSheet = ss.insertSheet('フィードバッククラスログ');
    classLogSheet.clear();
    classLogSheet.appendRow(['タイムスタンプ', 'コースID', 'コース名', '課題ID', '課題名', '在籍生徒数', '返却実施生徒数']);

    // 生徒フィードバックログシートの設定
    let studentLogSheet = ss.getSheetByName('生徒フィードバックログ');
    if (studentLogSheet) {
      ss.deleteSheet(studentLogSheet);
    }
    studentLogSheet = ss.insertSheet('生徒フィードバックログ');
    studentLogSheet.clear();
    studentLogSheet.appendRow(['タイムスタンプ', 'コースID', '課題ID', '生徒ID', '生徒メールアドレス', '生徒名', '提出状況', '回答', '教師フィードバック', '送信状況']);
    // 不要なシートを削除（Sheet1など）
    sheets.forEach(sheet => {
      if (sheet.getSheetName() === 'Sheet1' || sheet.getSheetName() === 'シート1') {
        ss.deleteSheet(sheet);
      }
    });
    SpreadsheetApp.getUi().alert('初期設定が完了しました。');
  } catch (error) {
    SpreadsheetApp.getUi().alert(`初期設定中にエラーが発生しました: ${error.message}`);
  }
}

function getUrlFromCell(cell) {
  const richText = cell.getRichTextValue();
  if (richText) {
    const runs = richText.getRuns();
    for (let i = 0; i < runs.length; i++) {
      const run = runs[i];
      const url = run.getLinkUrl();
      if (url) {
        return url;
      }
    }
    // リンクがない場合はテキストを返す
    return richText.getText();
  } else {
    return cell.getValue();
  }
}

function extractIDs(url) {
  const regex = /\/c\/([a-zA-Z0-9_-]+)\/sa\/([a-zA-Z0-9_-]+)/;
  const match = url.match(regex);
  if (match) {
    const encodedCourseId = match[1];
    const encodedAssignmentId = match[2];

    // Base64デコード（URL安全な形式）
    const courseId = Utilities.newBlob(Utilities.base64DecodeWebSafe(encodedCourseId)).getDataAsString();
    const assignmentId = Utilities.newBlob(Utilities.base64DecodeWebSafe(encodedAssignmentId)).getDataAsString();

    Logger.log(`デコードされたcourseId: ${courseId}`);
    Logger.log(`デコードされたassignmentId: ${assignmentId}`);

    return {
      courseId: courseId,
      assignmentId: assignmentId
    };
  } else {
    Logger.log("URLからIDが抽出できませんでした");
    SpreadsheetApp.getUi().alert("有効な課題URLを入力してください。");
    return null;
  }
}

function writeStudentSubmissions() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const urlSheet = ss.getSheetByName('URL');
    const submissionsSheet = ss.getSheetByName('Submissions');
    const assignmentSheet = ss.getSheetByName('課題と評価方法');
    const classLogSheet = ss.getSheetByName('フィードバッククラスログ');
    const studentLogSheet = ss.getSheetByName('生徒フィードバックログ');

    if (!urlSheet || !submissionsSheet || !assignmentSheet || !classLogSheet || !studentLogSheet) {
      throw new Error("必要なシートが見つかりません。'setup'関数を実行して初期設定を行ってください。");
    }

    const cell = urlSheet.getRange('A1');
    const url = getUrlFromCell(cell);

    if (!url) {
      throw new Error('課題のURLが入力されていません。');
    }

    const ids = extractIDs(url);
    if (!ids) return;

    const courseId = ids.courseId;
    const assignmentId = ids.assignmentId;

    Logger.log(`使用するcourseId: '${courseId}'`);
    Logger.log(`使用するassignmentId: '${assignmentId}'`);

    // フィードバッククラスログを確認
    const classLogData = classLogSheet.getDataRange().getValues();
    let feedbackGiven = false;
    for (let i = 1; i < classLogData.length; i++) {
      const row = classLogData[i];
      const loggedCourseId = String(row[1]).trim(); // コースID
      const loggedAssignmentId = String(row[3]).trim(); // 課題ID

      Logger.log(`比較対象の loggedCourseId: '${loggedCourseId}', courseId: '${courseId}'`);
      Logger.log(`比較対象の loggedAssignmentId: '${loggedAssignmentId}', assignmentId: '${assignmentId}'`);

      if (loggedCourseId === courseId && loggedAssignmentId === assignmentId) {
        feedbackGiven = true;
        break;
      }
    }

    let importFeedbackFromLog = false;
    if (feedbackGiven) {
      const ui = SpreadsheetApp.getUi();
      const response = ui.alert('このクラスへのフィードバックはすでに実施しています。', 'フィードバック済みの回答をログから取得しますか？', ui.ButtonSet.YES_NO_CANCEL);
      if (response == ui.Button.YES) {
        importFeedbackFromLog = true;
      } else if (response == ui.Button.NO) {
        importFeedbackFromLog = false;
      } else {
        ui.alert('キャンセルしました。');
        return;
      }
    }

    // コース情報を取得
    const course = Classroom.Courses.get(courseId);
    assignmentSheet.getRange('B1').setValue(course.name);

    // 課題情報を取得
    const coursework = Classroom.Courses.CourseWork.get(courseId, assignmentId);
    assignmentSheet.getRange('B2').setValue(coursework.title);
    assignmentSheet.getRange('B3').setValue(coursework.description || '（課題の説明なし）');

    // StudentSubmissionsを取得
    const submissionsResponse = Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, assignmentId, {
      states: ['TURNED_IN', 'RETURNED', 'CREATED', 'NEW']
    });

    const submissions = submissionsResponse.studentSubmissions;

    if (!submissions || submissions.length === 0) {
      throw new Error('提出物が見つかりませんでした。');
    }

    // シートをクリアして見出しを設定
    submissionsSheet.clearContents();
    submissionsSheet.appendRow(['生徒ID', '生徒メールアドレス', '生徒名', '提出状況', '回答', '教師フィードバック', '送信状況']);

    // 生徒フィードバックログを取得
    const studentLogData = studentLogSheet.getDataRange().getValues();

    submissions.forEach(submission => {
      let fullName = '';
      let email = '';
      try {
        const student = Classroom.UserProfiles.get(submission.userId);
        fullName = student.name.fullName || '名前を取得できません';
        email = student.emailAddress || 'メールアドレスを取得できません';
      } catch (error) {
        fullName = '名前を取得できません';
        email = 'メールアドレスを取得できません';
        Logger.log(`生徒情報の取得中にエラーが発生しました (userId: ${submission.userId}): ${error.message}`);
      }

      let answer = '';

      // 生徒の回答を取得
      if (submission.shortAnswerSubmission && submission.shortAnswerSubmission.answer) {
        answer = submission.shortAnswerSubmission.answer;
      } else if (submission.multipleChoiceSubmission && submission.multipleChoiceSubmission.answer) {
        answer = submission.multipleChoiceSubmission.answer;
      } else {
        answer = '回答なし';
      }

      let teacherFeedback = '';
      let sendStatus = '';

      if (importFeedbackFromLog) {
        // 生徒フィードバックログからフィードバックと送信状況を取得
        for (let i = 1; i < studentLogData.length; i++) {
          const logRow = studentLogData[i];
          const logCourseId = String(logRow[1]).trim();
          const logAssignmentId = String(logRow[2]).trim();
          const logStudentId = String(logRow[3]).trim();

          if (logCourseId === courseId && logAssignmentId === assignmentId && logStudentId === submission.userId) {
            teacherFeedback = logRow[8]; // 教師フィードバック
            sendStatus = logRow[9]; // 送信状況
            break;
          }
        }
      }

      submissionsSheet.appendRow([
        submission.userId,
        email,
        fullName,
        submission.state,
        answer,
        teacherFeedback,
        sendStatus
      ]);
    });

    SpreadsheetApp.getUi().alert('生徒の提出物を取得しました。');
  } catch (error) {
    SpreadsheetApp.getUi().alert(`エラーが発生しました: ${error.message}`);
    Logger.log(`エラー詳細: ${error.stack}`);
  }
}


function postFeedbackToStudents() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const urlSheet = ss.getSheetByName('URL');
    const submissionsSheet = ss.getSheetByName('Submissions');
    const assignmentSheet = ss.getSheetByName('課題と評価方法');
    const classLogSheet = ss.getSheetByName('フィードバッククラスログ');
    const studentLogSheet = ss.getSheetByName('生徒フィードバックログ');

    if (!urlSheet || !submissionsSheet || !assignmentSheet || !classLogSheet || !studentLogSheet) {
      throw new Error("必要なシートが見つかりません。'setup'関数を実行して初期設定を行ってください。");
    }

    const cell = urlSheet.getRange('A1');
    const url = getUrlFromCell(cell);

    if (!url) {
      throw new Error('課題のURLが入力されていません。');
    }

    const ids = extractIDs(url);
    if (!ids) return;

    const courseId = ids.courseId;
    const assignmentId = ids.assignmentId;

    // 課題タイトルを取得
    const coursework = Classroom.Courses.CourseWork.get(courseId, assignmentId);
    const assignmentTitle = coursework.title;

    // Submissionsシートからデータを取得
    const dataRange = submissionsSheet.getDataRange();
    const data = dataRange.getValues();

    const headers = data[0];
    const studentIdIndex = headers.indexOf('生徒ID');
    const emailIndex = headers.indexOf('生徒メールアドレス');
    const nameIndex = headers.indexOf('生徒名');
    const statusIndex = headers.indexOf('提出状況');
    const responseIndex = headers.indexOf('回答');
    const teacherFeedbackIndex = headers.indexOf('教師フィードバック');
    const sendStatusIndex = headers.indexOf('送信状況');

    if ([studentIdIndex, emailIndex, nameIndex, statusIndex, responseIndex, teacherFeedbackIndex, sendStatusIndex].includes(-1)) {
      throw new Error("必要な列が見つかりません。");
    }

    const studentsToSend = [];

    // フィードバックを送信すべき生徒を収集
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const submissionState = row[statusIndex];
      const studentResponse = row[responseIndex];
      const teacherFeedback = row[teacherFeedbackIndex];
      const sendStatus = row[sendStatusIndex];

      if ((submissionState === 'TURNED_IN' || submissionState === 'RETURNED') && studentResponse && teacherFeedback && sendStatus !== '送信済み') {
        // 未提出・空欄の生徒は除外、送信済みの生徒は除外
        studentsToSend.push({ rowNumber: i + 1, data: row }); // 行番号はシートの行番号に合わせる
      }
    }

    if (studentsToSend.length === 0) {
      SpreadsheetApp.getUi().alert('送信対象の生徒がいません。');
      return;
    }

    // 確認ダイアログを表示
    const ui = SpreadsheetApp.getUi();
    const result = ui.alert(`${studentsToSend.length}人に投稿します。投稿は消せません。よろしいですか？`, ui.ButtonSet.OK_CANCEL);

    if (result !== ui.Button.OK) {
      ui.alert('キャンセルしました。');
      return;
    }

    // 在籍生徒数を取得
    const studentsResponse = Classroom.Courses.Students.list(courseId);
    const enrolledStudents = studentsResponse.students || [];
    const enrolledStudentCount = enrolledStudents.length;

    // コース名を取得
    const course = Classroom.Courses.get(courseId);
    const courseName = course.name;

    // 個別フィードバックを送信
    studentsToSend.forEach(student => {
      const rowNumber = student.rowNumber;
      const row = student.data;

      const studentId = row[studentIdIndex];
      const studentName = row[nameIndex];
      const studentResponse = row[responseIndex];
      const teacherFeedback = row[teacherFeedbackIndex];

      const messageText = `${studentName}さんへ個別フィードバック\n\n【課題タイトル】\n${assignmentTitle}\n\n【あなたの振り返り】\n${studentResponse}\n\n【教師からのフィードバック】\n${teacherFeedback}`;

      // API用のデータを準備
      const announcement = {
        "text": messageText,
        "assigneeMode": "INDIVIDUAL_STUDENTS",
        "individualStudentsOptions": {
          "studentIds": [studentId]
        },
        "state": "PUBLISHED"
      };

      // 個別アナウンスを送信
      Classroom.Courses.Announcements.create(announcement, courseId);

      // 送信状況を更新
      submissionsSheet.getRange(rowNumber, sendStatusIndex + 1).setValue('送信済み');

      // 生徒フィードバックログに記録
      const timestamp = new Date();
      studentLogSheet.appendRow([
        timestamp,
        courseId,
        assignmentId,
        studentId,
        row[emailIndex],
        row[nameIndex],
        row[statusIndex],
        row[responseIndex],
        row[teacherFeedbackIndex],
        '送信済み'
      ]);
    });

    // フィードバッククラスログに記録
    const timestamp = new Date();
    classLogSheet.appendRow([
      timestamp,
      courseId,
      courseName,
      assignmentId,
      assignmentTitle,
      enrolledStudentCount,
      studentsToSend.length
    ]);

    SpreadsheetApp.getUi().alert('ストリームに、個別のフィードバックを送信しました。');

  } catch (error) {
    SpreadsheetApp.getUi().alert(`エラーが発生しました: ${error.message}`);
    Logger.log(`エラー詳細: ${error.stack}`);
  }
}

記事は以上です！ご覧いただきありがとうございました！

余談：

従来の質問には各質問への返信機能があります。しかしどうやら、2024年10月の時点ではGASから簡単にClassroomのコメント欄を制御することはできないみたいです。

この記事が気に入ったらサポートをしてみませんか？