#97 Google フォームでのテスト結果を GAS で分析する

ともかつのノート

2023年7月12日 22:16

先日の GEG北陸フェスタの会場で、口頭で以下のような GAS についての質問を受けました。

Google フォームを用いたテストで、セクションを分けて観点別の問題を出題しています。スプレッドシートに書き出された「スコア」は合計点数となっていますが、セクションごとの点数を出力できないでしょうか？

興味深い内容に感じたので、少し調べてプログラムを作成してみました。

作成したプログラム

作成した GAS のプログラムは、下図のような Google スプレッドシートに埋め込んであります。

このスプレッドシートは、以下 URL にアクセスして、画面右上の「テンプレートを使用」ボタンを押すと、自身の Google にコピーを作成できます。

https://bit.ly/tomokatsu_gas97
→ 利用件数を確認するために Bitly の短縮 URL で掲載しています。

プログラムをはじめて実行する際には、アカウントによる確認作業が必要になります。詳しくは以下の投稿をご覧ください。

操作手順

スプレッドシート内にも説明を書いておきましたが、セル B1 に解析対象となる Google フォームの「ファイル ID」を入力します。
ファイル ID というのは、Google フォームの編集画面で表示されている URL の https://docs.google.com/forms/d/ ○○○○○○○○○ /edit の ○○○○○○○○ の部分です。

Google フォームのファイル ID

「ファイル ID」を入力して、「回答を解析」ボタンを押すと、現在の日時とフォームのタイトルを組み合わせたシートを作成します。

作成されたシートの中には、以下の結果が書き出されています。

回答のあったタイムスタンプ
回答者のメールアドレス　※取得できれば
回答者の名前　※メールアドレスから取得できれば
それぞれの設問の回答と点数
それぞれのセクション毎の合計点数
合計点数

メールアドレスと名前

このプログラムでは、Googleフォームへの回答にメールアドレスが記録されている場合には、「メールアドレス」として列 B に出力されます。
設問としてメールアドレスを入力している場合には、列 B には表示されません。

列 B が空欄でない場合には、そのメールアドレスを Google Classroom のAPI（userProfiles.get）を用いてユーザー情報を取得し、可能であればそのユーザーの名前を列 C に出力します。　※この API でユーザー情報を取得できない場合には、列 C は空欄になります。

各設問の「回答」と「点数」

下図のように、1行目には「セクション名」、2行目には「設問のタイトル」が表示され、各設問ごとに「回答」と「点数」が出力されます。

Google フォームが出力する Google スプレッドシートと似たような体裁になっていますが、以下のような違いがあります。

セクションも意識して、見出し行が 2行にわたっている。
それぞれの設問毎に点数を表示する。

セクション毎の合計点数

今回のプログラムの主目的であるセクション毎の小計と、全体の合計点数がそれぞれの設問の回答に続けて書き出されます。

合計点数は、数式ではなくプログラム中で計算した値が設定されていますので、列の順序を入れ替えるなどしても値は変化しません。

スプレッドシートの体裁は自身で…

今回のプログラムでは、先頭の見出し行 2行を固定する操作はプログラム中で行っていますが、その他のスプレッドシートの体裁の調整・変更は行っていません。細かな調整などは、ご自身で行ってください。

セルに対する「条件付き書式」
列の順番
列の幅
フォントや文字の大きさ、文字色など

GAS ではできないこと

今回のプログラムを作成するにあたって、Google フォームの「テストにする」をオンにした場合の処理で、GAS にはできないことがいくつか見えてきました。
記録のために書き出しておきます。

満点だった場合の点数がわからない
Googleフォームが書き出す Google スプレッドシートには、「スコア」として「1 / 5」や「3 / 5」といったように満点だった時の点数が併記されています。
この満点の点数が GASからは取得できないように見受けられました。
採点対象ではない設問が区別できない
前項にも関連しますが、それぞれの設問に対する回答が何点だったのかはわかりますが、それぞれの設問に割り当てられている点数が GAS からは把握できません。
そのため、配点が 0 に設定されていて、採点対象となっていない設問がわからないのです。そのため、フォームの先頭などで基本情報などを入力している採点対象ではない部分にも「Score」として点数を表示しています。
「解答集を作成」に相当する API が GAS には提供されていない？
Google フォームの編集画面では、「解答集を作成」をクリックして、正解や点数を設定しますが、これらと同等の処理を行うための API が用意されていないように見受けられます。
これらの API が用意されていないので、↓ の安藤先生の YouTube 動画でも自動作成した Google フォームには手作業で「解答集を作成」を行うようになっています。
「選択式（グリッド）」「チェックボックス（グリッド）」は非対応
GAS のプログラムでは、これらの項目について得点をなぜか取得できないのです。きっと API のバグ（不具合）のように見受けられるのですが、この記事を作成している時点では対応されていません。　※この内容については、「#105 Google フォームでのテスト結果を GAS で分析する（その２）」で説明していますので、そちらをご覧ください。　2023/09/14 追記

以下の動画が、安藤先生が公開されている Google フォームで「テストにする」をオンにしたフォームを自動生成するプログラムですが、この動画で提供しているフォームでも「解答集を作成する」の内容は GAS のプログラムではなく、手作業で設定するようになっています。

最後に

今回のプログラムは、Googleフォームの「テストにする」の回答結果からセクション毎の合計点数を計算するものです。
単元や観点別にセクションを分けておけば、それぞれの合計点数を簡単に表にできます。

最後に、お決まりのフレーズなどを書いておきます。

一応の動作確認は行っているものの、不慮のトラブルによって損害等が生じても、責任はとれませんので予めご了承ください。
コメントを含めても 200行あまりのスクリプトであり、実行に際して目的外の場所への書き出しや収集などは行っていません。
特別なエラー処理は行っていないので、意図しないケースでエラーが発生してしまうかもしれません。どうにもならない場合には、ご連絡ください。

わたし自身にしてみると、このような「スクリプトを作ること」が目的になっているような感じですが、このスクリプトが何かの役に立てば幸いです。
「スキ ♡」を押してもらえると、このようなプログラム作成の励みになります。😍

この記事が気に入ったらサポートをしてみませんか？