GoogleClassroomの質問や課題への提出内容を、Googleスプレッドシートに抽出する

# はじめに

(※コードを修正し、AdminDirectory APIへの依存を解消しました (2020-05-19))
(※G Suite for Educationにおいて、学生の氏名・メールアドレスが表示されないバグを修正しました (2020-07-07))
(※スプレッドシートに出力される日時をローカルなタイムゾーンのDateオブジェクトとするように変更しました (2020-08-02))

GoogleClassroomは、直感的なユーザーインターフェイスで、とても便利なアプリなのですが、利用にあたって、たくさんのクリック・画面遷移をする必要があり、また、動作が重いと感じる場合があります。特に、「課題」や「質問」に対して学生が提出した内容（添付で提出されたファイル、解答欄に書かれたコメントなど）を、GoogleClassroomの画面内で一つずつ選んで内容を表示し、採点評価をするときに、ストレスを感じることがあるかと思います。

そこで、学生が提出した課題の内容を、Googleスプレッドシート上に一覧形式で抽出するスクリプトを開発しました。
このスクリプトは、G Suite for Educationを通じて、GoogleClassroomを利用されている教育機関にお勤めの方々に、ご活用いただければと考えて書いています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/c4fe20eb-e20d-a75e-e008-70ec384de185.png)

この記事は、「[Googleスプレッドシートで宛先ごとに個別文面のメールを作成して一括送信する](https://qiita.com/kubo_hiroya/items/c0054ebc03f2419a5f97)」「[Google Classroomへの各学生の課題提出内容を、Googleスプレッドシートに抽出する](https://qiita.com/kubo_hiroya/items/45b34dde5cf0439a7e23)」「[履修者名簿・提出物URL一覧をもとに、履修者間の相互評価用Googleフォームと評価集計用Googleスプレッドシートを自動生成する](https://qiita.com/kubo_hiroya/items/04791bfc6a26726065e8)」という、「遠隔授業支援ツール」のシリーズのひとつとして書きました。ほかの記事も併せてお読みいただければと思います。


# 導入

一連の手順を説明します。

## (1) 空のGoogleスプレッドシートを作成する

1. Googleアカウントで認証済みのWebブラウザで、[Googleスプレッドシート](https://spreadsheet.google.com/)を開き、画面右下隅の＋記号のアイコンから、「新しいスプレッドシートを作成」を実行して、新しいスプレッドシートを作成します。下図の例では、ファイル名を「Classroom課題提出物の一覧」としています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/32669b2a-14fd-a96c-9677-6bb0e0632a17.png)

## (2) スクリプトエディタを開いてスクリプトファイルを保存する

1. Googleスプレッドシートの画面上部のメニューバーの「ツール」から「スクリプトエディタ」を開きます。
　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/10ebf46b-0c8e-63fb-07fc-c515041319b1.png)

2. 「無題のプロジェクト」という画面が開くので、「コード.gs」の内容として、既存の ``` function myFunction(){ } ``` となっているものを削除します。
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/be934bff-3772-e957-9a14-ae6b26314a22.png)

3. 「コード.gs」の内容として、以下に示すコードを貼り付けます。

```JavaScript
const users = {};

const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
function formatDateTime(datetime){
  return Utilities.formatDate(new Date(datetime), timezone, "yyyy/MM/dd HH:mm:ss");
}

function getTeachers(courseId){
  let teachers = [];
  let nextPageToken = null;
  do{
    const optionalArgs = {
      pageSize: 64
    };
    if(nextPageToken){
      optionalArgs.pageToken = nextPageToken;
    }
    const teacherList = Classroom.Courses.Teachers.list(courseId, optionalArgs);
    nextPageToken = teacherList.nextPageToken;
    teachers = teachers.concat(teacherList.teachers);
  }while(nextPageToken != undefined);
  return teachers;  
}

function getTeacher(courseId, teacherId){
  if(! users[teacherId]){
    try{
      const teacher = Classroom.Courses.Teachers.get(courseId, teacherId);
      users[teacherId] = teacher.profile;
      return teacher.profile;
    }catch(ex){
      return {
        name: {fullName: '('+teacherId+')'},
        emailAddress: '-',
      };
    }
  } else {
    return users[teacherId];
  }
}

function getStudent(courseId, studentId){
  if(! users[studentId]){
    const student = Classroom.Courses.Students.get(courseId, studentId);
    users[studentId] = student;
    return student;
  } else {
    return users[studentId];
  }
}

function getNumStudents(courseId){
    try{
      const students = Classroom.Courses.Students.list(courseId);
      return students.students.length;
    }catch(err){
      return 0;
    }
}

function listStudentSubmissions(courseId, courseWorkId, courseWorkName){
  let nextPageToken = '';  
  let submissions = [];
  do{
    var optionalArgs = {
      pageSize: 64,
    };
    if(nextPageToken){
      optionalArgs.pageToken = nextPageToken;
    }
    let studentSubmissions = Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, courseWorkId, optionalArgs);
    nextPageToken = studentSubmissions.nextPageToken;
    studentSubmissions.studentSubmissions.forEach(function(submission){
      submissions.push(submission);
    });
  }while(nextPageToken != undefined);

  const submissionToArray = function(studentSubmission){
    const user = getStudent(courseId, studentSubmission.userId);
    const row =  [
      user.profile.name.fullName,
      user.profile.emailAddress
    ];

    row.push(studentSubmission.state);
    row.push(formatDateTime(studentSubmission.creationTime));
    row.push(formatDateTime(studentSubmission.updateTime));
    
    if(studentSubmission.shortAnswerSubmission && studentSubmission.shortAnswerSubmission.answer){
      row.push(studentSubmission.shortAnswerSubmission.answer);
    }

    if(studentSubmission.assignmentSubmission && studentSubmission.assignmentSubmission.attachments){
      studentSubmission.assignmentSubmission.attachments.forEach(function(attachment){
        if(attachment.youTubeVideo){
          const youTubeVideo = attachment.youTubeVideo;
          row.push(youTubeVideo.title);
          row.push(youTubeVideo.alternateLink);
        }
        if(attachment.driveFile){
          row.push(attachment.driveFile.title);          
          row.push(attachment.driveFile.alternateLink);
          row.push(attachment.driveFile.thumbnailUrl);
        }
        if(attachment.form){
          row.push(attachment.form.title);          
          row.push(attachment.form.responseUrl);
        }
      });
    }
    return row;
  };

  const rows = [];
  submissions.forEach(function(submission){
    const row = [courseWorkName].concat(submissionToArray(submission));
    rows.push(row);
  });
  return rows;
}

function listCourseWorks(courseId){
  let nextPageToken = '';
  const values = [];
  do{
    const optionalArgs = {
      pageSize: 32,
    };
    if(nextPageToken){
      optionalArgs.pageToken = nextPageToken;
    }
    const courseWorks = Classroom.Courses.CourseWork.list(courseId, optionalArgs);
    nextPageToken = courseWorks.nextPageToken;
    if(! courseWorks.courseWork){
      throw new Error("NoCourseWorks");
    }
    courseWorks.courseWork.map(function(courseWork){
      return [
        ""+courseId,
        ""+courseWork.id, 
        courseWork.title, 
        courseWork.description,
        courseWork.state,
        formatDateTime(courseWork.creationTime),
        formatDateTime(courseWork.updateTime)
      ];
    }).forEach(function(courseWork){
      values.push(courseWork);
    });
  }while(nextPageToken != undefined);
  return values;
}


function listCourses(teacherId){
    let nextPageToken = '';
    const values = [];
    do{
      const optionalArgs = {
        pageSize: 100,
        teacherId: teacherId,
        courseStates: 'ACTIVE'
      };
      if(nextPageToken){
        optionalArgs.pageToken = nextPageToken;
      }
      const courses = Classroom.Courses.list(optionalArgs);
      nextPageToken = courses.nextPageToken;   
      courses.courses.map(function(course){
        const user = getTeacher(course.id, course.ownerId);
        Logger.log(course.ownerId,JSON.stringify(user));
        return [
          ""+course.id, 
          user.name.fullName, 
          user.emailAddress, 
          course.name, 
          course.enrollmentCode,
          formatDateTime(course.creationTime),
          formatDateTime(course.updateTime),
          (course.section == null)? '' : course.section, 
          course.courseState,
          getNumStudents(course.id)
        ];
      }).forEach(function(course){
        values.push(course);        
      });
    }while(nextPageToken != undefined);
    return values;
}

function updateCoursesSheet(sheet, data){
  sheet.clear();
  sheet.appendRow(['courseId','教員名','教員メールアドレス','授業名', 'クラスコード', '作成日', '更新日', 'セクション', '状態', '登録者数']);
  data.forEach(function(course){
    sheet.appendRow(course);
  });
}

function updateCourseWorksSheet(sheet, data){
  sheet.clear();
  sheet.appendRow(['courseId','courseWorkId','タイトル','説明','状態','作成日', '更新日']);
  data.forEach(function(courseWork){
    sheet.appendRow(courseWork);
  });
}

function updateStudentSubmissionsSheet(sheet, data){
  sheet.clear();
  data.forEach(function(submission){
    sheet.appendRow(submission);
  });
}

function updateMyCourses(){
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('courses');
  if(! sheet){
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('courses');
  }
  const email = Session.getActiveUser().getEmail();
  updateCoursesSheet(sheet, listCourses(email));
}

function updateCourseWorks(){
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('courses');
  if(! sheet){
    Browser.msgBox("エラー：「courses」シートがありません。「コース一覧表(courses)を更新」を実行してから、こちらを再実行してください。");
    throw new Error('エラー');
  }
  const values = sheet.getActiveRange().getValues();
  if(values.length != 1 && values[0].length < 5){
    Browser.msgBox("エラー：「courses」シートで、抽出したいクラスの行を、いずれか1行だけ選択状態にしてから、再実行してください。");
    throw new Error('エラー');    
  }
  const courseId = values[0][0];
  const courseName = values[0][3];

  let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(""+courseName);
  if(! targetSheet){
    targetSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(courseName);
  }
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(targetSheet);

  try{
    const courseWorks = listCourseWorks(courseId);
    updateCourseWorksSheet(targetSheet, courseWorks);
  }catch(error){
    Browser.msgBox("エラー：選択されたコース「"+courseName+"」には、課題が出題されていません。");
  }
}

function updateStudentSubmissions(){
  const sheet = SpreadsheetApp.getActiveSheet();
  const courseName = sheet.getName();
  const values = sheet.getActiveRange().getValues();
  const courseId = values[0][0];
  const courseWorkId = values[0][1];
  const courseWorkName = values[0][2];
  const studentSubmissions = listStudentSubmissions(courseId, courseWorkId, courseWorkName);
  const targetSheetName = courseName+" "+courseWorkName;
  let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
  if(! targetSheet){
    targetSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(targetSheetName);
  }
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(targetSheet);
  updateStudentSubmissionsSheet(targetSheet, studentSubmissions);
}

function onOpen(){
　const menu=[
   {name: "1.コース一覧(シート名：courses)を抽出", functionName: "updateMyCourses"},
   {name: "2.コースワーク一覧（シート名：クラス名）を抽出", functionName: "updateCourseWorks"},
   {name: "3.提出物一覧（シート名：クラス名 課題名）を抽出", functionName: "updateStudentSubmissions"}
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Classroomからの抽出", menu); //メニューを追加
}
```
ファイルメニューから「保存」を選び、このファイルを保存をしてください。その際、プロジェクト名を指定するように促されますので、適当に名前をつけてください。
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/5415a16a-cbcc-7366-ad6d-97ef4b466c8d.png)


## (3) APIを有効化する

1. スクリプトエディタの画面上部の「リソース」メニュー→「Googleの拡張サービス...」をクリックしてください。
2. ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/a7479170-f513-49ce-4fdd-4d8e111b1f39.png)

2. "Advanced Google Services"のダイアログボックスが開くので、次の2つのAPIを「ON」に設定し、「OK」を押してください。
  * Google Classroom API
  * Google Sheets API
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/bd061e41-1585-8c86-ca2b-4a171c63341c.png)
<img width="712" alt="スクリーンショット 2020-04-18 17.23.50.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/9592a884-9e19-bbb4-0882-338f217e23d3.png">

## (4) スクリプトを再読み込みして、メニュー表示をカスタマイズする

前項でスクリプトを保存・API有効化をしたら、ここで、もともとのスプレッドシートの画面において、Webブラウザの更新ボタンを押すなどして、このスプレッドシートを開き直してください。
内部的に、このスプレッドシートにバインドされているスクリプトの```onOpen```関数が呼び出されることにより、スプレッドシートに 「Classroomからの抽出」というメニューが増設されるはずです。 こうして、メニューから「1.コース一覧(シート名：courses)を抽出」「2.コースワーク一覧（シート名：クラス名）を抽出」「3.提出物一覧（シート名：クラス名 課題名）を抽出」という項目が選べるようになっていることが確認できたら、次の「使い方」に進みましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/052252ab-6324-bac9-1cd3-dcfa608b2532.png)

# 使い方

## (1) 自分が教師として担当するクラス一覧を抽出する

前項において増設された、「Classroomからの抽出」というメニューから、まず「1.コース一覧(シート名：courses)を抽出」をクリックします。

このとき、初回実行時については、以下「スクリプトの実行を許可する」の手順が必要になります。

### スクリプトの実行を許可する〔初回実行時）

1. 「承認が必要」というダイアログボックスが表示されるので、「続行」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/04f55abb-2c2d-99fc-cd2d-14882833275a.png)

3. 「（あなたの組織のG Suiteのドメイン）」のアカウントを選択してください」というダイアログボックスが表示されるので、GoogleClassroomの教師としての自分のアカウントを選択します。
<img width="712" alt="スクリーンショット 2020-04-18 17.23.50.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/38266bc8-57ae-19c7-a205-dbd083e7ae70.png">

4. 「Googleアカウントへのアクセスをリクエストしています」というダイアログボックスが表示されるので、「許可」をクリックします。
<img width="712" alt="スクリーンショット 2020-04-18 17.28.49.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/7516f589-ad5d-e174-419c-d44e51865e9a.png">

### スクリプトの実行結果

スクリプトの実行には、やや時間がかかるかと思います。終了すると、「courses」というシートが作成または更新され、その中に、このプログラムを実行中のユーザ（あなた）が教員となっている授業が、
1行1授業での一覧形式として、次のように作成されます。

<img width="1171" alt="スクリーンショット 2020-04-18 17.36.33.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/28532b56-47f8-287f-7e1d-6b5f3369ff01.png">

## (2) あるクラスにおいて出題されている課題一覧を抽出する

1. 次に、この「courses」シートにおいて、課題を抽出したい「授業」をいずれかひとつだけ選んで、その行を選択状態にします。
下図の例は、7行目で表される「授業」を選択しているところです。
<img width="1171" alt="スクリーンショット 2020-04-18 17.40.07.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/1b9fe71b-e351-daa0-7b1e-c6e487433bba.png">

2. 「Classroomからの抽出」のメニューから、「2.コースワーク一覧（シート名：クラス名）を抽出」を実行します。
<img width="407" alt="スクリーンショット 2020-04-18 17.42.53.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/2fb194b7-558e-da38-2900-d48c4878331d.png">

３． 実行が終わると、その授業の「授業名」シートが作成または更新され、
その中に、その授業において教員が出題した課題が、1行1課題の、一覧形式として表示されます。
下図の例では、「2019年度キャリアデザイン」という授業について、同名のシートが作成され、その２行目〜10行目に実行結果が抽出されています。
<img width="1012" alt="スクリーンショット 2020-04-18 17.44.37.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/5c097376-8435-cd9f-4344-26022bfaba33.png">


## (3) ある課題において提出されている提出内容一覧を抽出する

１． 授業名のシートにおいて、いずれかひとつの「課題」を表す行を選択状態にします。
下図の例は、7行目で表される「授業」を選択しているところです。
<img width="1012" alt="スクリーンショット 2020-04-18 17.55.03.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/2037932b-e911-086b-0432-25078d93734d.png">

2. 「Classroomからの抽出」のメニューから、 「3.提出物一覧（シート名：クラス名 課題名）を抽出」を実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/f2bd4a9d-3129-61c5-5ca0-7cbcde7a3fc4.png)

3. 実行が終わると、その授業・課題の「授業名 課題名」のシートが作成または更新され、その中に、その課題に対して学生たちが提出した内容が、1件1行での一覧形式として表示されます。

下図の例では、「グローバル化について」という課題について、学生の回答がEカラム以降に記載されています（実際の授業における提出物の画像を例としているので、画像に灰色の矩形を重ねて加工をしており、例としてよくわからないものになってしまっておりますが…）
<img width="1063" alt="スクリーンショット 2020-04-18 17.59.15.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193148/4e1d0b95-2462-0eaf-9989-aa015ef05390.png">

なお、課題の種類により、
その提出物が、GoogleClassroomにおける「質問」に対応する場合には、Eカラム以降のセルでは、学生の回答文面がセル値として表示されます。
その提出物が、GoogleClassroomにおける「課題」に対するファイルの提出物である場合には、そのファイルのGoogleドライブ上のURLが表示され、リンクが貼られるというような内容となります。

# おわりに

このしくみは、GoogleClassroomを利用した授業運営をするにあたって、多数の学生の多数の課題について、次々と採点評価をし、フィードバック内容をバリバリ書いていく作業をする際に、たいへん便利ですので、ご活用ください。

コロナ禍のもと、世界各国で遠隔授業が活発化しているためか、GoogleClassroomの動作がかなり重たくなってきているようです。それでも、各学生の課題提出内容を、いったん一括でGoogleスプレッドシートに抽出してしまえば、サクサク作業できるようになりますよ！

（もしいずれかの方面からの要望があれば、このGoogleスプレッドシート上に記入したgradeを、GoogleClassroomのgradeとして書き戻すしくみの開発もしてみようかとも思っております。また、スクリプトがもう少し複雑になってきたら[clap](https://github.com/google/clasp)を用いてみたいと思います。）

# License
[CC BY 4.0](https://creativecommons.org/licenses/by/4.0/deed.ja)