履修者名簿・提出物URL一覧をもとに、履修者間の相互評価用Googleフォームと評価集計用Googleスプレッドシートを自動生成する

はじめに

ある種の授業においては、＜教員が学生を評価する＞というだけでなく、＜学生が学生を評価する＞すなわち相互評価をさせるという方法が、教育上、有効なものとなります。

相互評価については、Daphne KollerのTED講演「オンライン教育が教えてくれること」で、MOOCsにおいて履修者同士に相互評価させる意義について語られている部分(10:43-)を見ておくと良いでしょう。Kollerは、Sadler&Good："The Impact of Self- and Peer-Grading
on Student Learning"の研究成果に触れつつ、「適切な動機づけのもとでは、教師による採点結果と学生による採点は高い相関を示す」ということで、学生同士が能動的に学び合えるようにするという文脈の中での相互評価の重要性を訴えています。

Moodleにも、相互評価に使える「ワークショップモジュール」がありました。私としても、かなり意気込んで使ってみたことが何度もあるのですが、学生たちには難しかったようで…。

時を経て、私は今、国際教養学部で教員をしています。この学部では、学生全員に留学を必須として指示しています。そうした中での留学前教育として、「日本での映像を撮影・編集」→「YouTubeの限定公開動画としてアップロード」→「GoogleClassroomのストリームへの投稿によりクラス内の他の学生と共有」→「学生同士で映像内容について相互評価」という課題に取り組ませてきました。この成果物となる映像作品は、留学先で出会った人たちに見せて、コミュニケーションのきっかけにしてもらおうというものです。授業の30人のクラスでの相互評価を実施する際には、ひとりずつの学生に、10点満点評価＋コメントの形で、自己評価1個+他者評価29個＝30個の評価を行わせる、クラス全体では30人ｘ30個＝900通りもの組み合わせで、相互評価を行わせるということをやっておりました。なかなか大変です。

というわけで、その経験を踏まえつつ、GoogleClassroomで相互評価を行う際の、なるべく一般的な支援ツールを作りたいと考えました。Googleスプレッドシート上に、履修者名簿・提出物一覧を用意すると、履修者間での相互評価用Googleフォームと、評価集計用Googleスプレッドシートとを、GoogleAppsScriptで自動作成するというものです。その、ひとまずの成果物を、この記事を通じて共有します。もしよろしければご利用ください。

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

導入

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

Googleアカウントで認証済みのWebブラウザで、Googleスプレッドシートを開き、画面右下隅の＋記号のアイコンから、「新しいスプレッドシートを作成」を実行して、新しいスプレッドシートを作成します。ここでは、ファイル名を「相互評価の集計」としました。

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

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

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

1. 「コード.gs」の内容として、以下に示すコードを貼り付けます。
   ※ここでは、それぞれの履修者が提出した動画タイトルと動画のURLについて、10段階で評価をするフォームを生成するというスクリプトでの例としています。

const FORM_NAME_PREFIX = '相互評価フォーム:';
const reviewRateMin = 0;
const reviewRateMax = 10;

const SHEET_NAME = {
  MEMBER: 'member',
  RESULT: 'result',
  SRC: 'src',
  RESPONSE_PREFIX: 'フォームの回答'
};

function onOpen(){
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('相互評価')
  .addItem('フォーム/集計表の作成', 'main')
  .addToUi();
}

function main() {
  const parseMemberRow = function(row){
    return {
      email: row[0], 
      studentCode: row[1],
      studentName: row[2],
      title: row[3],
      url: row[4]
    };
  };

  function createFormSection(form, member, index){
    const numSectionsParPage = 10;
    const displayName = member.email+" "+member.studentName;
    const reviewTargetText = member.title + " "+ member.url;

    const sectionHeaderItem = form.addSectionHeaderItem();
    sectionHeaderItem.setTitle(displayName);
    sectionHeaderItem.setHelpText(reviewTargetText);

    const gridItem = form.addGridItem();
    gridItem.setTitle(displayName + ': ルーブリックに基づく評価');
    gridItem.setHelpText('不十分:0点/評価できる:1点/高く評価できる:2点');
    gridItem.setColumns(["0", "1", "2"]);
    gridItem.setRows([
      "①観点1",
      "②観点2",
      "③観点3",
      "④観点4",
      "⑤観点5",
    ]);

    const scaleItem = form.addScaleItem();
    scaleItem.setTitle(displayName + ": 評価の合計");
    scaleItem.setHelpText("※ルーブリックに基づく評価(0点〜2点x5観点)の合計を記入してください");
    scaleItem.setLabels("低", "高");
    scaleItem.setBounds(reviewRateMin, reviewRateMax);

    const paragraphTextItem = form.addParagraphTextItem();
    paragraphTextItem.setTitle(displayName+": 評価の理由・コメント");
    paragraphTextItem.setHelpText("この評価にあたっての理由(特に0点や2点とした要素について)を説明しつつ、内容を改善するための助言を書いてください");

    ((index % numSectionsParPage) == numSectionsParPage - 1) && form.addPageBreakItem();
  }

  function createResultSheetContent(resultSheet, headerColumnsData, headerRowData, numMembers){
    const startRow = 1;
    const startColumn = 1;
    const numHeaderRows = 1;
    const numHeaderColumns = 3;
    const reviewRateRange = reviewRateMax - reviewRateMin + 1;

    resultSheet.insertRows(numMembers + numHeaderRows);
    resultSheet.getRange(startRow + numHeaderRows, startColumn, numMembers, numHeaderColumns).setValues(headerColumnsData);
    resultSheet.getRange(startRow, startColumn + numHeaderColumns, numHeaderRows, numMembers).setValues([headerRowData]);

    const scoreRange = [];
    for(let i = reviewRateMin; i <= reviewRateMax; i++){
      scoreRange.push((i).toString());
    }
    resultSheet.getRange(startRow, startColumn + numHeaderColumns + numMembers, numHeaderRows, reviewRateRange).setValues([scoreRange]).setBackgroundRGB(255, 240, 240);
    resultSheet.getRange(startRow, startColumn + numHeaderColumns + numMembers + reviewRateRange, 1, 1).setValues([['平均点']]).setBackgroundRGB(240, 240, 255);
    resultSheet.getRange(startRow, startColumn + numHeaderColumns + numMembers + reviewRateRange + 1, 1, 1).setValues([['順位']]).setBackgroundRGB(240,255,255);

    resultSheet.setFrozenColumns(numHeaderColumns);
    resultSheet.setFrozenRows(numHeaderRows);

    const formulas = [];
    const headerStartColumn = 2;
    const headerStartRow = 1;
    const numColumnsParMember = 7;
    const indexOfColumnParMember = 5;

    const srcRange = `INDIRECT("'${SHEET_NAME.SRC}'!"&ADDRESS(2,2)&":"&ADDRESS(${(headerStartRow + numMembers)},${(headerStartColumn + (numMembers * numColumnsParMember) )}))`;

    for(let y=0; y < numMembers; y++){
      var row = [];
      var p2 = ''+(2+y);
      for(var x=0; x < numMembers; x++){
        const revieweeAddress = `LEFT(INDIRECT(ADDRESS(1,${(startColumn+numHeaderColumns+x)})), FIND(" ", INDIRECT(ADDRESS(1,${(startColumn+numHeaderColumns+x)})))-1)`;
        const index = `(ROW($A${p2})-2)*${numColumnsParMember}+${indexOfColumnParMember+2}`;
        row.push(`=IFERROR(VLOOKUP(${revieweeAddress}, ${srcRange}, ${index}, false))`);
      }
      for(let g = 0; g < reviewRateRange ; g++){
        const countIfRange = `INDIRECT(ADDRESS(${p2},${startColumn+numHeaderColumns})&":"&ADDRESS(${p2},${(startColumn+numHeaderColumns+numMembers-1)}))`;
        const value = `INDIRECT(ADDRESS(1, ${(startColumn+numHeaderColumns + numMembers + g )}))`;
        row.push(`=COUNTIF(${countIfRange},${value})`);
      }

      row.push(`=IFERROR(AVERAGEIF(INDIRECT(ADDRESS(${p2},${startColumn+numHeaderColumns})&":"&ADDRESS(${p2},${(startColumn+numHeaderColumns+numMembers)})),">=0"),0)`);
      row.push(`=IFERROR(RANK(INDIRECT(ADDRESS(${p2},${(startColumn+numHeaderColumns+numMembers+reviewRateRange)})), INDIRECT(ADDRESS(2,${(startColumn+numHeaderColumns+numMembers+reviewRateRange)})&":"&ADDRESS(${(1+numMembers)},${(startColumn+numHeaderColumns+numMembers+reviewRateRange)}))))`);

      formulas.push(row);
    }

    resultSheet.getRange(startRow + numHeaderRows, startColumn + numHeaderColumns, numMembers, numMembers + reviewRateRange + 2).setFormulas(formulas);  
  }

  execute(parseMemberRow, createFormSection, createResultSheetContent);

}

function execute(parseMemberRow, createFormSection, createResultSheetContent){
  const spreadsheet = SpreadsheetApp.getActive();
  const form = createReviewForm(FORM_NAME_PREFIX+getYYYYMMDD());
  form.setDestination(FormApp.DestinationType.SPREADSHEET, spreadsheet.getId());

  const headerColumnsData = [];
  const headerRowData = [];

  const memberSheet = spreadsheet.getSheetByName(SHEET_NAME.MEMBER);

  const numMembers = evalMemberRows(memberSheet, function(row, index){
    const member = parseMemberRow(row);
    if(createFormSection){
      createFormSection(form, member, index);
    }
    headerColumnsData.push(["", member.email, member.studentName]);
    headerRowData.push(member.email+" "+member.studentName);
  });

  try{
    spreadsheet.deleteSheet(spreadsheet.getSheetByName(SHEET_NAME.SRC));
    spreadsheet.deleteSheet(spreadsheet.getSheetByName(SHEET_NAME.RESULT));
  }catch(ignore){}

  renameFormDestinationSheet(spreadsheet, SHEET_NAME.RESPONSE_PREFIX, SHEET_NAME.SRC);

  const resultSheet = spreadsheet.insertSheet(SHEET_NAME.RESULT);    
  createResultSheetContent(resultSheet, headerColumnsData, headerRowData, numMembers);

}

function createReviewForm(formName){
  const form = FormApp.create(formName);
  form.setRequireLogin(true);
  form.setCollectEmail(true);
  form.setAllowResponseEdits(true);
  form.setLimitOneResponsePerUser(true);
  return form;
}

function getYYYYMMDD(){
  const now = new Date();
  const date = now.getFullYear() + '/' + ('00'+(now.getMonth()+1)).slice(-2) + '/'  + ('00'+now.getDate()).slice(-2);
  return date;
}

function renameFormDestinationSheet(spreadsheet, prefix, newName){
  let sheets;
  for(let j = 0; j < 10; j ++){
    sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for(let i = 0; i < sheets.length; i++){
      const sheet = sheets[i];
      if(sheet.getName().startsWith(prefix)){
        sheet.setName(newName);
        return;
      }
    };
  }
  throw new Error("No Prefix:<"+prefix+"> "+sheets.map(function(sheet){return sheet.getName()}).join(", "));
}

function evalMemberRows(sheet, callback){
  let numMembers = 0;
  const startRow = 2; // skip 1st row to ignore header
  const startColumn = 1;
  const range = sheet.getRange(startRow, startColumn, sheet.getMaxRows(), sheet.getMaxColumns());
  const rows = range.getDisplayValues();
  rows.filter(function(row){return 0 < row.length && 0 < row[0].length}).sort(function(row1, row2){return (row1[0]<row2[0])?-1:((row1[0]>row2[0])?1:0)}).map(function(row, index){
    numMembers = Math.max(numMembers, index + 1);
    callback(row, index);
  });
  return numMembers;
} 

コピペをしたファイルを保存してください。プロジェクト名を入力するように促されるので、「相互評価の集計」など、適宜名前をつけてください。

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

前項でスクリプトを保存・API有効化をしたら、ここで、もともとのスプレッドシートの画面において、Webブラウザの更新ボタンを押すなどして、このスプレッドシートを開き直してください。
内部的に、このスプレッドシートにバインドされているスクリプトのonOpen関数が呼び出されることにより、スプレッドシートに 「相互評価」というメニューが増設されるはずです。 こうして、メニューから「フォーム/集計表の作成」という項目が選べるようになっていることが確認できたら、次の「使い方」に進みましょう。

使い方

(1) 評価対象者の名簿を設定する

1. 既存の「シート1」という名称の空のシートについて、シート名を変更し「member」というシートにします。
   

2. 「member」シートについて、1行目の各セルを次のように記入して、見出し行を作成してください。

A	B	C	D	E
email	学籍番号	氏名	videoTitle	VideoUrl

さらに続けて、2行目以降の行で、履修者1人・その提出物1個について、1行での定義をした形でのデータを定義してください。これにより、シート全体は、たとえば次のようになります。

A	B	C	D	E
email	学籍番号	氏名	videoTitle	VideoUrl
[e-mail]	00001	studentName1	title1	https://youtu.be/...
[e-mail]	00002	studentName2	title2	https://youtu.be/...
[e-mail]	00003	studentName3	title3	https://youtu.be/...

(2) フォーム・集計表を自動作成する

スプレッドシートの画面上部のメニュー「相互評価 > フォーム/集計表の作成」をクリックして、実行をしてください。

スクリプト実行許可（初回実行時のみ）

1. 初回実行時には、次のように「承認が必要」ダイアログボックスが表示されるので、「続行」を押してください。
   

2. もしあなたが、G Suiteを契約している組織のGoogleアカウントではなく、
   無償のGoogleアカウントを用いて、このスクリプトを実行しようとしている場合には、
   次のような警告が表示されることがあります。
   警告画面右下の「詳細」をクリックし、さらに「安全ではないページへ移動」をクリックしてください。
   

3. 下のような画面により、アクセスのリクエストに対する許可を発行することができます。「許可」を押してください。
   

4. このようにして、いったん「許可」をしておけば、実行したときに、警告画面が表示されることなしに、スクリプトが動作するようになります。

自動作成の結果

このスクリプトの実行により、次のような結果が得られます。

1. 相互評価用のフォームが自動的に作成されます。Googleドライブ上に、 スクリプトを実行した当日の日付の名前として、たとえば「相互評価フォーム:2020/04/18」というような名前のものとなります。
   

2. 元となるスプレッドシートが更新され、「member」のシートに加えて、新しく2つのシートが作られます。

• 「src」というシートが作成されます。これが、1.で自動作成されたフォームの回答が追記的に格納される場所になります。
  
• 「result」というシートが作成されます。これが、フォームの回答結果となる「src」シートをもとに、評価結果を自動的に集計表示する内容として利用されるものになります。

(3) 自動作成されたフォームを微調整する

前項(2)で自動作成したフォームの内容を確認してください。必要に応じてフォームのファイル名の変更、フォーム設定の変更、フォーム名の変更、説明文言の追加、スタイルの変更などを適宜行ってください。ただし、既存の設問の追加・削除、設問名の変更はしないでください。

(4) 履修者にフォームのURLを通知し、回答の受け付けを開始する

前項(2)で自動作成したフォームを履修者に対して提示し、回答の受付を開始してください。

(5) 集計表のシートを開いて、集計結果を閲覧利用する

「result」というシートを開いて、評価結果を自動的に集計表示した内容を利用してください。
相互評価での記入内容に応じて、集計結果の数値が、リアルタイムで更新されます。

おわりに

このしくみは、GoogleClassroomを利用した授業運営をするにあたって、「一方通行にならない授業」した場合に、なかなか有用なものであると考えています。ここぞ！という時に、ご活用ください。

なお、相互評価用のフォーム生成内容や、相互評価集計・ビジュアライズのしくみについては、ハードコーディングしてしまっているので、もう少し工夫の余地があるかというようにも思っております。また、スクリプトがもう少し複雑になってきたらclapを用いてみたいと思います。

License

CC BY 4.0