LoginSignup
10
8

More than 3 years have passed since last update.

@kubo_hiroya(久保 裕也)

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

Last updated at Posted 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を利用されている教育機関にお勤めの方々に、ご活用いただければと考えて書いています。

image.png

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

導入

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

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

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

image.png

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

  1. Googleスプレッドシートの画面上部のメニューバーの「ツール」から「スクリプトエディタ」を開きます。
    　image.png

  2. 「無題のプロジェクト」という画面が開くので、「コード.gs」の内容として、既存の function myFunction(){ } となっているものを削除します。
    image.png

  3. 「コード.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); //メニューを追加
}

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

(3) APIを有効化する

  1. スクリプトエディタの画面上部の「リソース」メニュー→「Googleの拡張サービス...」をクリックしてください。

  2. image.png

  3. "Advanced Google Services"のダイアログボックスが開くので、次の2つのAPIを「ON」に設定し、「OK」を押してください。

    • Google Classroom API
    • Google Sheets API image.png スクリーンショット 2020-04-18 17.23.50.png

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

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

使い方

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

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

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

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

  1. 「承認が必要」というダイアログボックスが表示されるので、「続行」をクリックします。
    image.png

  2. 「（あなたの組織のG Suiteのドメイン）」のアカウントを選択してください」というダイアログボックスが表示されるので、GoogleClassroomの教師としての自分のアカウントを選択します。
    スクリーンショット 2020-04-18 17.23.50.png

  3. 「Googleアカウントへのアクセスをリクエストしています」というダイアログボックスが表示されるので、「許可」をクリックします。
    スクリーンショット 2020-04-18 17.28.49.png

スクリプトの実行結果

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

スクリーンショット 2020-04-18 17.36.33.png

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

  1. 次に、この「courses」シートにおいて、課題を抽出したい「授業」をいずれかひとつだけ選んで、その行を選択状態にします。
    下図の例は、7行目で表される「授業」を選択しているところです。
    スクリーンショット 2020-04-18 17.40.07.png

  2. 「Classroomからの抽出」のメニューから、「2.コースワーク一覧（シート名：クラス名）を抽出」を実行します。
    スクリーンショット 2020-04-18 17.42.53.png

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

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

１． 授業名のシートにおいて、いずれかひとつの「課題」を表す行を選択状態にします。
下図の例は、7行目で表される「授業」を選択しているところです。
スクリーンショット 2020-04-18 17.55.03.png

  1. 「Classroomからの抽出」のメニューから、 「3.提出物一覧（シート名：クラス名 課題名）を抽出」を実行します。
    image.png

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

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

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

おわりに

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

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

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

License

CC BY 4.0

10
8
11

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
8