More than 3 years have passed since last update.

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

Last updated at 2020-12-17Posted at 2020-04-18

はじめに

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

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

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

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

導入

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

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

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

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

Googleスプレッドシートの画面上部のメニューバーの「ツール」から「スクリプトエディタ」を開きます。
　
「無題のプロジェクト」という画面が開くので、「コード.gs」の内容として、既存の function myFunction(){ } となっているものを削除します。
「コード.gs」の内容として、以下に示すコードを貼り付けます。

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); //メニューを追加
}

ファイルメニューから「保存」を選び、このファイルを保存をしてください。その際、プロジェクト名を指定するように促されますので、適当に名前をつけてください。

(3) APIを有効化する

スクリプトエディタの画面上部の「リソース」メニュー→「Googleの拡張サービス...」をクリックしてください。
"Advanced Google Services"のダイアログボックスが開くので、次の2つのAPIを「ON」に設定し、「OK」を押してください。

Google Classroom API
Google Sheets API

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

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

使い方

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

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

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

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

「承認が必要」というダイアログボックスが表示されるので、「続行」をクリックします。
「（あなたの組織のG Suiteのドメイン）」のアカウントを選択してください」というダイアログボックスが表示されるので、GoogleClassroomの教師としての自分のアカウントを選択します。

「Googleアカウントへのアクセスをリクエストしています」というダイアログボックスが表示されるので、「許可」をクリックします。

スクリプトの実行結果

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

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

次に、この「courses」シートにおいて、課題を抽出したい「授業」をいずれかひとつだけ選んで、その行を選択状態にします。
下図の例は、7行目で表される「授業」を選択しているところです。

「Classroomからの抽出」のメニューから、「2.コースワーク一覧（シート名：クラス名）を抽出」を実行します。

３．実行が終わると、その授業の「授業名」シートが作成または更新され、
その中に、その授業において教員が出題した課題が、1行1課題の、一覧形式として表示されます。
下図の例では、「2019年度キャリアデザイン」という授業について、同名のシートが作成され、その２行目〜10行目に実行結果が抽出されています。

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

１．授業名のシートにおいて、いずれかひとつの「課題」を表す行を選択状態にします。
下図の例は、7行目で表される「授業」を選択しているところです。

「Classroomからの抽出」のメニューから、「3.提出物一覧（シート名：クラス名課題名）を抽出」を実行します。
実行が終わると、その授業・課題の「授業名課題名」のシートが作成または更新され、その中に、その課題に対して学生たちが提出した内容が、1件1行での一覧形式として表示されます。

下図の例では、「グローバル化について」という課題について、学生の回答がEカラム以降に記載されています（実際の授業における提出物の画像を例としているので、画像に灰色の矩形を重ねて加工をしており、例としてよくわからないものになってしまっておりますが…）

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

おわりに

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

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

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

License

CC BY 4.0

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up