Classroomの「生徒別連絡」を一括化する｜スプレッドシート完結のGASツール【無料】

/**
 * ============================================================
 * Google Classroom 個別連絡ツール（Spreadsheet完結型）
 *
 * 【概要】
 * - Google Classroom の「生徒ごとにのみ表示されるお知らせ（Announcement）」を、
 *   スプレッドシート上でチェック＆文章入力して一括送信するGASです。
 * - UIはHTML（サイドバー等）を使わず、スプレッドシートだけで完結します。
 *
 * 【基本フロー（メニュー操作）】
 * ① コース一覧を更新（アクティブなコースのみ）
 *    → 「コース一覧」シートにコースを一覧表示（チェックで選択）
 * ② 生徒一覧を生成
 *    → 選択したコースの生徒をまとめて取得し「送信設定」シートに一覧化
 * ③ 送信
 *    → 「送信設定」でチェックされた行だけ、生徒個別にAnnouncementを作成
 *
 * 【設計方針】
 * - 1人 = 1投稿（Classroomのストリームが汚れる点は許容）
 * - 差し込み変数等は使わず、確実性と簡単さを優先
 * - courseId / studentId はAPI呼び出しに必須だが、
 *   教員が触る必要はないのでシート上では列を非表示にしている
 *
 * 【事前準備（重要）】
 * - Apps Script の「サービス」で Google Classroom API（Classroom）を有効化してください。
 *   これが無いと `ReferenceError: Classroom is not defined` になります。
 * ============================================================
 */


/**
 * スプレッドシートを開いたときに、操作用メニューを追加します。
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Classroom個別連絡')
    .addItem('①コース一覧を更新', 'refreshCourses')
    .addItem('②生徒一覧を生成', 'buildRecipients')
    .addSeparator()
    .addItem('③送信', 'sendFromSheet')
    .addToUi();
}


/**
 * ① コース一覧を取得して「コース一覧」シートに出力します。
 *
 * - teacherId: 'me' で「自分が教師のコース」に限定
 * - courseStates: ['ACTIVE'] で「アーカイブ済みを除外」
 * - APIはページングが必要なので nextPageToken を追って全件取得
 *
 * 出力列：
 * - 選択（チェックボックス）
 * - courseName（表示用）
 * - section（表示用）
 * - courseId（内部用：後続処理で必須だが列は非表示）
 */
function refreshCourses() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('コース一覧') || ss.insertSheet('コース一覧');
  sh.clear();

  // ヘッダ行（1行目）
  sh.getRange(1, 1, 1, 4).setValues([['選択', 'courseName', 'section', 'courseId']]);

  const rows = [];
  let pageToken;

  // Classroom APIのページング処理
  do {
    const res = Classroom.Courses.list({
      teacherId: 'me',
      courseStates: ['ACTIVE'], // ★アクティブのみ（アーカイブ除外）
      pageSize: 100,
      pageToken
    });

    (res.courses || []).forEach(c => {
      rows.push([
        false,           // 選択（チェックボックス）
        c.name || '',    // courseName
        c.section || '', // section
        c.id             // courseId（内部用）
      ]);
    });

    pageToken = res.nextPageToken;
  } while (pageToken);

  // データ行を書き込み
  if (rows.length) {
    sh.getRange(2, 1, rows.length, 4).setValues(rows);
    sh.getRange(2, 1, rows.length, 1).insertCheckboxes();
  }

  // ざっくり体裁（詳細は formatClassListSheet_ で調整）
  sh.setFrozenRows(1);
  sh.autoResizeColumns(1, 3);

  // courseId は見えなくてよい（内部キー）
  sh.hideColumn(sh.getRange('D:D'));

  // 見た目・操作性を整える（罫線、背景、フィルタなど）
  formatClassListSheet_(sh);
}


/**
 * ② 「コース一覧」シートで選択されたコースの生徒を取得し、
 *   「送信設定」シートに統合一覧として出力します。
 *
 * 「送信設定」シートでやること：
 * - 送る生徒にチェック
 * - メッセージを入力（複数行想定）
 * - 送信後、status/error/announcementId が行ごとに記録される
 *
 * 出力列（9列）：
 * A: 送信（チェックボックス）
 * B: courseName（表示用）
 * C: studentName（表示用）
 * D: message（手入力）
 * E: courseId（内部用：非表示）
 * F: studentId（内部用：非表示）
 * G: status（SUCCESS / FAIL）
 * H: error（失敗理由）
 * I: announcementId（成功時のID）
 */
function buildRecipients() {
  const ss = SpreadsheetApp.getActive();
  const classesSh = ss.getSheetByName('コース一覧');
  if (!classesSh) throw new Error('「コース一覧」シートがありません。先に①を実行してください。');

  // 「コース一覧」全体を読み込み（ヘッダ含む）
  const values = classesSh.getDataRange().getValues();

  // チェックされたコースのみ抽出
  const selected = values.slice(1)
    .filter(r => r[0] === true)
    .map(r => ({ courseName: r[1], section: r[2], courseId: r[3] }));

  if (selected.length === 0) throw new Error('「コース一覧」でコースを選択してください。');

  // 出力先（送信設定）
  const sh = ss.getSheetByName('送信設定') || ss.insertSheet('送信設定');
  sh.clear();

  // ヘッダ行
  sh.getRange(1, 1, 1, 9).setValues([[
    '送信', 'courseName', 'studentName', 'message',
    'courseId', 'studentId', 'status', 'error', 'announcementId'
  ]]);

  const rows = [];

  // 選択コースごとに生徒一覧を取得（ページングあり）
  for (const c of selected) {
    const courseDisplay = [c.courseName, c.section].filter(Boolean).join(' ');
    let pageToken;

    do {
      const res = Classroom.Courses.Students.list(c.courseId, {
        pageSize: 100,
        pageToken
      });

      (res.students || []).forEach(s => {
        const p = s.profile;
        rows.push([
          false,                    // 送信チェック
          courseDisplay,            // 表示用コース名
          p.name?.fullName || '(no name)', // 生徒名
          '',                       // メッセージ（手入力）
          c.courseId,               // 内部用 courseId
          p.id,                     // 内部用 studentId
          '',                       // status（送信後に書き込む）
          '',                       // error（送信後に書き込む）
          ''                        // announcementId（送信後に書き込む）
        ]);
      });

      pageToken = res.nextPageToken;
    } while (pageToken);
  }

  // 書き込み＆チェックボックス化
  if (rows.length) {
    sh.getRange(2, 1, rows.length, 9).setValues(rows);
    sh.getRange(2, 1, rows.length, 1).insertCheckboxes();
  }

  // ざっくり体裁
  sh.setFrozenRows(1);
  sh.autoResizeColumns(1, 3);

  // 内部キー列は非表示（教員が触らないため）
  sh.hideColumn(sh.getRange('E:E')); // courseId
  sh.hideColumn(sh.getRange('F:F')); // studentId

  // 見た目・操作性（行高、折り返し、条件付き書式、フィルタなど）
  formatSendSettingsSheet_(sh, rows.length);
}


/**
 * ③ 「送信設定」シートのチェック行を送信します。
 *
 * - チェックされた行を抽出
 * - messageが空欄の行が混ざっていたら送信を中止（事故防止）
 * - 各行について1人1投稿でAnnouncementを作成
 * - 成功/失敗結果をシートに書き戻す
 *
 * ※ 「テスト送信」機能は今回は入れていません（要望により）
 */
function sendFromSheet() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('送信設定');
  if (!sh) throw new Error('「送信設定」シートがありません。先に②を実行してください。');

  const values = sh.getDataRange().getValues();
  if (values.length <= 1) throw new Error('送信対象がありません。');

  // 送信対象を抽出（チェック = TRUE の行だけ）
  const items = [];
  for (let i = 1; i < values.length; i++) {
    const r = values[i];
    if (r[0] === true) {
      items.push({
        rowIndex: i + 1, // 書き戻し先（シート行番号）
        courseName: r[1],
        studentName: r[2],
        message: r[3],
        courseId: r[4],
        studentId: r[5]
      });
    }
  }

  if (items.length === 0) throw new Error('送信チェックが入っていません。');

  // 空メッセージ事故防止（チェックされているのに本文が空）
  const empties = items.filter(x => !x.message || !x.message.trim());
  if (empties.length > 0) {
    throw new Error(`メッセージ空欄の送信対象が ${empties.length} 件あります。`);
  }

  // バッチID（今回は表示のみ。ログシート等に保存する拡張も可能）
  const batchId = Utilities.getUuid();

  // 1人ずつ投稿（1人=1投稿）
  for (const item of items) {
    // 「その生徒だけに見える」Announcement
    const body = {
      text: item.message,
      assigneeMode: 'INDIVIDUAL_STUDENTS',
      individualStudentsOptions: { studentIds: [item.studentId] }
    };

    try {
      // レート制限・一時エラー対策でバックオフ付きにする
      const created = withBackoff_(() =>
        Classroom.Courses.Announcements.create(body, item.courseId)
      );

      // 成功時：結果を行に反映
      sh.getRange(item.rowIndex, 7).setValue('SUCCESS');       // status
      sh.getRange(item.rowIndex, 8).setValue('');              // error
      sh.getRange(item.rowIndex, 9).setValue(created.id || ''); // announcementId

    } catch (e) {
      // 失敗時：エラーを行に反映
      const msg = e?.message || String(e);
      sh.getRange(item.rowIndex, 7).setValue('FAIL');
      sh.getRange(item.rowIndex, 8).setValue(msg);
    }

    // APIを連打しすぎないための小休止（必要に応じて調整）
    Utilities.sleep(150);
  }

  SpreadsheetApp.getUi().alert(
    `送信完了（バッチID: ${batchId}）\n結果は「送信設定」シートをご確認ください。`
  );
}


/**
 * レート制限（429）や一時的なサーバーエラー（500/503）などの際に、
 * 指数バックオフで再試行するためのヘルパー関数です。
 *
 * - maxRetries 回まで再試行
 * - wait（待機時間）は 300ms → 600ms → 1200ms … と増やす（上限5秒）
 *
 * 「確実に全部送る」ことより、「送信が途中で止まりにくい」ことを優先しています。
 */
function withBackoff_(fn) {
  const maxRetries = 5;
  let wait = 300;

  for (let i = 0; i <= maxRetries; i++) {
    try {
      return fn();
    } catch (e) {
      const msg = e?.message || String(e);

      // リトライしたいエラーだけ判定（雑に拾ってOK）
      const retryable =
        /429|RESOURCE_EXHAUSTED|Rate Limit|Service invoked too many times|500|503/i.test(msg);

      // リトライ対象外 or 最終回ならそのまま投げる
      if (!retryable || i === maxRetries) throw e;

      Utilities.sleep(wait);
      wait = Math.min(wait * 2, 5000);
    }
  }
}


/**
 * 「コース一覧」シートのUI整形
 * - 罫線、背景、列幅、フィルタなどを整える
 * - 2回目以降の実行でもエラーにならないよう、既存フィルタは削除してから作成
 */
function formatClassListSheet_(sh) {
  // ヘッダ固定＆高さ
  sh.setFrozenRows(1);
  sh.setRowHeight(1, 28);

  // 列幅（操作性を優先）
  sh.setColumnWidth(1, 60);   // 選択
  sh.setColumnWidth(2, 320);  // courseName
  sh.setColumnWidth(3, 140);  // section
  // D列は非表示なので幅は気にしない

  // ヘッダ行の見た目
  const header = sh.getRange(1, 1, 1, 4);
  header.setFontWeight('bold')
        .setHorizontalAlignment('center')
        .setVerticalAlignment('middle')
        .setBackground('#f3f4f6');

  // データ範囲がある場合のみ整形
  const lastRow = sh.getLastRow();
  if (lastRow >= 2) {
    // データ行は縦中央
    const data = sh.getRange(2, 1, lastRow - 1, 3);
    data.setVerticalAlignment('middle');

    // 見やすい罫線（A〜C列）
    sh.getRange(1, 1, lastRow, 3).setBorder(true, true, true, true, true, true);

    // フィルタ（既存があれば削除）
    const existingFilter = sh.getFilter();
    if (existingFilter) existingFilter.remove();
    sh.getRange(1, 1, lastRow, 3).createFilter();
  }
}


/**
 * 「送信設定」シートのUI整形
 * - 200行程度でも編集しやすいよう、メッセージ列を広く・折り返し・上詰め
 * - 行の高さを確保（複数行メッセージを想定）
 * - status列にSUCCESS/FAILの色付け
 * - 2回目以降でもエラーにならないようフィルタは削除してから作成
 */
function formatSendSettingsSheet_(sh, rowCount) {
  // rowCount: データ行数（ヘッダ除く）
  sh.setFrozenRows(1);
  sh.setRowHeight(1, 30);

  // 列幅（重要）
  sh.setColumnWidth(1, 60);   // 送信
  sh.setColumnWidth(2, 220);  // courseName
  sh.setColumnWidth(3, 150);  // studentName
  sh.setColumnWidth(4, 520);  // message（主役）
  sh.setColumnWidth(7, 90);   // status
  sh.setColumnWidth(8, 260);  // error（長くなりがち）
  sh.setColumnWidth(9, 180);  // announcementId

  // ヘッダの見た目（濃色）
  const header = sh.getRange(1, 1, 1, 9);
  header.setFontWeight('bold')
        .setHorizontalAlignment('center')
        .setVerticalAlignment('middle')
        .setBackground('#111827')
        .setFontColor('#ffffff');

  // データが無ければここで終了
  if (rowCount <= 0) return;

  const startRow = 2;

  // 行の高さ：メッセージ欄を複数行で編集しやすくするため高め
  sh.setRowHeights(startRow, rowCount, 54);

  // メッセージ列：折り返し＋上詰め（入力しやすい）
  const msgRange = sh.getRange(startRow, 4, rowCount, 1);
  msgRange.setWrap(true)
          .setVerticalAlignment('top');

  // 主要列の整列（見やすさ）
  sh.getRange(startRow, 1, rowCount, 3).setVerticalAlignment('middle');
  sh.getRange(startRow, 1, rowCount, 3).setHorizontalAlignment('center');
  sh.getRange(startRow, 2, rowCount, 1).setHorizontalAlignment('left'); // courseName
  sh.getRange(startRow, 3, rowCount, 1).setHorizontalAlignment('left'); // studentName

  // 表としての罫線
  sh.getRange(1, 1, rowCount + 1, 9).setBorder(true, true, true, true, true, true);

  // 背景（必要最低限：真っ白。交互色にしたい場合は拡張）
  sh.getRange(startRow, 1, rowCount, 9).setBackground('#ffffff');

  // フィルタ（既存があれば削除）
  const existingFilter = sh.getFilter();
  if (existingFilter) existingFilter.remove();
  sh.getRange(1, 1, rowCount + 1, 9).createFilter();

  // 条件付き書式（status列）
  const statusRange = sh.getRange(startRow, 7, rowCount, 1);
  const rules = [];

  // SUCCESS：薄い緑
  rules.push(
    SpreadsheetApp.newConditionalFormatRule()
      .whenTextEqualTo('SUCCESS')
      .setBackground('#dcfce7')
      .setRanges([statusRange])
      .build()
  );

  // FAIL：薄い赤
  rules.push(
    SpreadsheetApp.newConditionalFormatRule()
      .whenTextEqualTo('FAIL')
      .setBackground('#fee2e2')
      .setRanges([statusRange])
      .build()
  );

  // 既存ルールは置き換える
  sh.setConditionalFormatRules(rules);
}