ChatGPTに手伝ってもらってGoogle Apps Scriptでテスト採点のプログラムを書きました
山崎講師やりたいこと
グーグルフォームから投入された個々人の解答を正解と照らし合わせて、正解なら5点を加える。合計点の列と正解率の行を加えて個々人の入力値の下の行に追記する。ChatGPTに手伝ってもらって楽にGoogle Apps Scriptを書きました。
別のシートにエクセル形式のダウンロードができるリンクを表示する。
前提
- 解答は択一方式であり問題数は20問と決まっている。
- 正解は2行目に入力済みである。
- 回答者の人数は定まっていない。
- 私はJavascriptはよく分かっていない。
メイキング 集計機能
最初のプロンプト
経験上、最初にChatGPTにデータを解析させて、提案をさせるのが良いということが分かっていましたのでそのようにしてみました。
以下のようなテーブルがあります。どのようなGoogle Apps Scriptが作成できそうですか？
"""
タイムスタンプ スコア 社員番号： 氏名（例：今井克哉）： 問01 問02 問03 問04 問05 問06 問07 問08 問09 問10 問11 問12 問13 問14 問15 問16 問17 問18 問19 問20
正解 カ ウ カ エ オ ア カ エ ウ カ ア エ ウ ク ア ク カ エ エ カ
2023/06/28 10:04:47 23504 鈴木 太郎カ ウ ウ エ ウ ア カ エ エ カ ア エ イ ク ア ク ク エ エ カ
2023/06/28 10:18:28 23501 鈴木 さやか カ ウ カ エ オ ア カ エ ウ カ ア エ ウ ク ア ク カ エ エ カ
（中略）
このテーブルは、おそらく何らかの試験やアンケートの結果を示しているようです。Google Apps Scriptを使用して、このデータに対して様々な操作や分析を行うことができます。以下にいくつかのアイデアを示します：
- 自動採点機能の作成：
- 各参加者の回答を正解と比較し、正答数をカウントします。
- 正答数に基づいてスコアを計算し、それをテーブルに追加します。
- データの要約と分析：
- 各問題に対する正答率を計算します。
- 最もよく間違えられた問題や最も簡単な問題を特定します。
- 参加者ごとのパフォーマンスを分析し、平均スコアや合格者の割合などを報告します。
- 個別フィードバックの生成：
- 各参加者に対して、彼らの回答と正解を比較したフィードバックを生成します。
- 正答と誤答を含む個別のレポートを作成し、メールで送信することができます。
- データの視覚化：
- スコア分布、正答率、各問題の難易度などをグラフで表示します。
- Google Sheetsのチャート機能を活用して、データを視覚的に理解しやすくします。
- データベースとしての活用：
- 参加者の履歴データを保持し、時間の経過とともに彼らのパフォーマンスの変化を追跡します。
- 長期的なトレンドや改善点を分析するためのデータベースとして利用します。
これらの機能は、Google Apps Scriptのスプレッドシート操作、データ処理、メール送信機能を組み合わせて実現することができます。それぞれの機能はスクリプトのモジュールとして分割し、必要に応じて拡張や改良が可能です。
2回目のプロンプト
提案を受けて以下のようにお願いしてみました。
正答数に基づいてスコアを計算し、それをテーブルに追加するgasを作成して下さい。正解は5点として現在の表の下に追記して下さい。
正答数に基づいてスコアを計算し、それをテーブルに追加するためのGoogle Apps Script（GAS）の例を示します。このスクリプトは、各参加者の回答を正解と比較し、正答数に基づいてスコアを計算し、そのスコアを元のスプレッドシートの下に追加します。正解は5点として計算します。
function calculateScores() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var answers = data[1]; // 正解は2行目にあると仮定
var scoreColumn = data[0].length; // スコアを追加する列
// 各参加者の回答をチェックしてスコアを計算
for (var i = 2; i < data.length; i++) {
var score = 0;
for (var j = 4; j < answers.length; j++) { // 回答は5列目から始まると仮定
if (data[i][j] === answers[j]) {
score += 5; // 正解は5点
}
}
// スコアをシートに追加
sheet.getRange(i + 1, scoreColumn + 1).setValue(score);
}
}このスクリプトを使用するには、以下の手順に従います：
- Google スプレッドシートを開き、メニューから「拡張機能」>「Apps Script」を選択します。
- 上記のスクリプトをエディタに貼り付けます。
- 「保存」アイコンをクリックしてスクリプトを保存します。
- スクリプトエディタで「実行」ボタンをクリックし、必要な権限を付与します。
- スプレッドシートに戻り、スクリプトが正しく動作しているかを確認します。
このスクリプトは、上記のテーブル構造を前提としています。テーブルの構造が異なる場合は、適切に調整する必要があります。また、このスクリプトは簡単なエラーチェックを含んでいませんので、実際のデータに合わせて適宜修正することをお勧めします。
3回目のプロンプト
一人ひとりがどの問題に正解したかを表にしたいです。現在のデータの下に社員番号と氏名をコピーして、20問のうち正解は5，不正解は0となるセルを埋めるGASを作成して下さい。
一人ひとりがどの問題に正解したかを表すために、社員番号と氏名をコピーし、各問題に対して正解は5点、不正解は0点として記入するGoogle Apps Script（GAS）を以下のように作成します。
このスクリプトでは、まず既存のデータから社員番号と氏名をコピーし、それに続いて各問題のスコア（正解なら5点、不正解なら0点）を新たな行に記入します。
function recordIndividualScores() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var answers = data[1]; // 正解は2行目にあると仮定
var numRows = data.length;
var numQuestions = answers.length - 4; // 回答は5列目から始まると仮定
for (var i = 2; i < numRows; i++) {
var newRow = [];
newRow.push(data[i][2]); // 社員番号
newRow.push(data[i][3]); // 氏名
for (var j = 4; j < 4 + numQuestions; j++) {
if (data[i][j] === answers[j]) {
newRow.push(5); // 正解は5点
} else {
newRow.push(0); // 不正解は0点
}
}
// 新たな行をシートの末尾に追加
sheet.appendRow(newRow);
}
}
左に2列目ずれてコピーされてしまったため次の次のプロンプトで修正を試みました。
4回目のプロンプト
集計機能を追加するように依頼しました。タイポがありますが、ChatGPTは正しく意図を汲み取ってくれます。
上記のGASに個人の合計点とここの問題の正解率を加えて下さい。
指定された要件に従って、Google Apps Script（GAS）を拡張し、個人の合計点と各問題の正解率を計算し、それらを表に追加する機能を追加します。このスクリプトでは、まず個人の問題ごとのスコアを計算し、それらを表に追加します。次に、個人の合計点と各問題の正解率を計算して表の最後に追加します。
function recordScoresAndCalculateStats() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var answers = data[1]; // 正解は2行目にあると仮定
var numParticipants = data.length - 2;
var numQuestions = answers.length - 4; // 回答は5列目から始まると仮定
var correctCounts = new Array(numQuestions).fill(0); // 各問題の正答数を格納する配列
// 各参加者のスコアを計算し、シートに記録
for (var i = 2; i < data.length; i++) {
var newRow = [];
var totalScore = 0; // 個人の合計点
newRow.push(data[i][2]); // 社員番号
newRow.push(data[i][3]); // 氏名
for (var j = 4; j < 4 + numQuestions; j++) {
if (data[i][j] === answers[j]) {
newRow.push(5); // 正解は5点
totalScore += 5;
correctCounts[j - 4]++; // 正答数をカウント
} else {
newRow.push(0); // 不正解は0点
}
}
newRow.push(totalScore); // 個人の合計点を追加
sheet.appendRow(newRow); // 新たな行をシートの末尾に追加
}
// 各問題の正解率を計算し、シートに記録
var statsRow = ["合計点", "正解率"];
for (var k = 0; k < numQuestions; k++) {
var correctRate = (correctCounts[k] / numParticipants) * 100;
statsRow.push(correctRate.toFixed(2) + "%"); // 正解率をパーセンテージで表記
}
sheet.appendRow(statsRow); // 統計情報の行をシートの末尾に追加
}5回目のプロンプト
集計機能までできましたので次は列が2つ左にズレている問題の修正を試みます。
社員番号と氏名の列が左に2セル分ズレています。 社員番号は社員番号の列に、氏名は氏名の列に来るように変更して下さい。
社員番号と氏名が正しい列に配置されるように、スクリプトを修正します。社員番号と氏名の列を左に2セル分ずらし、それに合わせて各問題のスコアと統計情報も適切な位置に配置するように変更します。
function recordScoresAndCalculateStats() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var answers = data[1]; // 正解は2行目にあると仮定
var numParticipants = data.length - 2;
var numQuestions = answers.length - 4; // 回答は5列目から始まると仮定
var correctCounts = new Array(numQuestions).fill(0); // 各問題の正答数を格納する配列
// 各参加者のスコアを計算し、シートに記録
for (var i = 2; i < data.length; i++) {
var newRow = [];
var totalScore = 0; // 個人の合計点
// 社員番号と氏名を適切な位置に配置
newRow.push(data[i][2]); // 社員番号
newRow.push(data[i][3]); // 氏名
newRow.push(''); // スコアの前に2つの空セルを追加
for (var j = 4; j < 4 + numQuestions; j++) {
if (data[i][j] === answers[j]) {
newRow.push(5); // 正解は5点
totalScore += 5;
correctCounts[j - 4]++; // 正答数をカウント
} else {
newRow.push(0); // 不正解は0点
}
}
newRow.push(totalScore); // 個人の合計点を追加
sheet.appendRow(newRow); // 新たな行をシートの末尾に追加
}
// 各問題の正解率を計算し、シートに記録
var statsRow = ['', '', '']; // 社員番号と氏名の列、および空白の列
for (var k = 0; k < numQuestions; k++) {
var correctRate = (correctCounts[k] / numParticipants) * 100;
statsRow.push(correctRate.toFixed(2) + "%"); // 正解率をパーセンテージで表記
}
sheet.appendRow(statsRow); // 統計情報の行をシートの末尾に追加
}
これで概ね作りたい機能ができました。
メイキング ユーザーインターフェース
今のままでは、Apps Scriptの実行ボタンからしか実行できないためユーザーインターフェースの改善を試みます。
1回目のプロンプト
GASの関数をスプレッドシートから実行するにはどうしたらいいですか？
Google スプレッドシートから Google Apps Script（GAS）の関数を実行するには、主に次の方法があります：
1. 直接実行
- スクリプトエディタで関数を選択し、再生ボタン（▶）をクリックして直接実行します。
- これはスクリプトをテストしたり、一度きりの実行に便利です。
2. カスタムメニューを追加
- スプレッドシートにカスタムメニューを作成し、そのメニューからスクリプトを実行できるようにします。
- 以下はカスタムメニューを追加する例です：
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('カスタムメニュー')
.addItem('スコア計算と統計', 'recordScoresAndCalculateStats') // 関数名を指定
.addToUi();
}- このコードをスクリプトに追加し、スプレッドシートを開くと「カスタムメニュー」という新しいメニュー項目が表示されます。その中の「スコア計算と統計」を選択すると、
recordScoresAndCalculateStats関数が実行されます。
3. トリガーを設定
- 特定の条件や指定した時間に自動的にスクリプトを実行するようにトリガーを設定できます。
- スクリプトエディタの左側にある時計のアイコン（現在のトリガー）をクリックし、「トリガーを追加」を選択してトリガーを設定します。
- 例えば、スプレッドシートが開かれたときや特定の時間に定期的に関数を実行するように設定できます。
これらの方法でスプレッドシートからスクリプトを実行することができます。使用する方法は、スクリプトの使用頻度や目的に応じて選択してください。
提案に従い、2の方法で以下のようなメニューを追加できました。
2回目のプロンプト
このスプレッドシートをエクセル形式でダウンロードする処理を加えてください。
Google Apps Script（GAS）は、直接的なファイルダウンロード機能を提供していませんが、スプレッドシートをエクセル形式（.xlsx）にエクスポートし、ダウンロード用のURLを生成する方法があります。このURLを使用して、ユーザーは手動でファイルをダウンロードできます。
以下のコードをrecordScoresAndCalculateStats関数の下に追加してください。これにより、処理が完了した後にエクスポート用のURLをログに出力します。
function recordScoresAndCalculateStats() {
// ...既存のコード...
// 統計情報の行を追加するコードの後に以下を追加
exportSheetToExcel();
}
function exportSheetToExcel() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId();
var sheetId = spreadsheet.getActiveSheet().getSheetId();
var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx&gid=" + sheetId;
Logger.log("Download your Excel file here: " + url);
}
しかし、この方法はログにURLを出力するというもので必ずしも使いやすいとは言えません。
そこで改善をお願いしたのですが、一番驚いたのがこの後のプロンプトです。
3回目のプロンプト
ログよりも見つけやすいところに出力してください。
スプレッドシートの見つけやすい場所にエクスポート用のURLを出力するには、スクリプトを修正してスプレッドシートの新しいシートにURLを記載する方法があります。以下はそのためのコードです：
function recordScoresAndCalculateStats() {
// ...既存のコード...
// 統計情報の行を追加するコードの後に以下を追加
exportSheetToExcel();
}
function exportSheetToExcel() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId();
var sheetId = spreadsheet.getActiveSheet().getSheetId();
var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx&gid=" + sheetId;
// 新しいシートを作成し、URLを記載する
var downloadSheet = spreadsheet.getSheetByName("Download Link");
if (!downloadSheet) {
downloadSheet = spreadsheet.insertSheet("Download Link");
} else {
downloadSheet.clear(); // 既にシートがある場合はクリアする
}
downloadSheet.appendRow(["エクセル形式でダウンロードするには以下のリンクをクリックしてください："]);
downloadSheet.appendRow([url]);
}新しいシートに以下のようなURLを貼り付けてくれました。
AIには身体性がないので人間の気持ちは分からないとはよく言われることです。
しかし、「ログよりも見つけやすいところ」という指示にChatGPTは見事に応えてくれました。
これもパラメータ数が膨大なことの恩恵でしょうか。
最終的なコード
最終的なコードを掲載します。
どなたかの参考になりましたら幸いです。
function recordScoresAndCalculateStats() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var answers = data[1]; // 正解は2行目にあると仮定
var numParticipants = data.length - 2;
var numQuestions = answers.length - 4; // 回答は5列目から始まると仮定
var correctCounts = new Array(numQuestions).fill(0); // 各問題の正答数を格納する配列
// 各参加者のスコアを計算し、シートに記録
for (var i = 2; i < data.length; i++) {
var newRow = [];
var totalScore = 0; // 個人の合計点
// 社員番号と氏名を適切な位置に配置
newRow.push(data[i][2]); // 社員番号
newRow.push(data[i][3]); // 氏名
newRow.push(''); // スコアの前に2つの空セルを追加
for (var j = 4; j < 4 + numQuestions; j++) {
if (data[i][j] === answers[j]) {
newRow.push(5); // 正解は5点
totalScore += 5;
correctCounts[j - 4]++; // 正答数をカウント
} else {
newRow.push(0); // 不正解は0点
}
}
newRow.push(totalScore); // 個人の合計点を追加
sheet.appendRow(newRow); // 新たな行をシートの末尾に追加
}
// 各問題の正解率を計算し、シートに記録
var statsRow = ['', '', '']; // 社員番号と氏名の列、および空白の列
for (var k = 0; k < numQuestions; k++) {
var correctRate = (correctCounts[k] / numParticipants) * 100;
statsRow.push(correctRate.toFixed(2) + "%"); // 正解率をパーセンテージで表記
}
sheet.appendRow(statsRow); // 統計情報の行をシートの末尾に追加
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('カスタムメニュー')
.addItem('スコア計算と統計', 'recordScoresAndCalculateStats') // 関数名を指定
.addToUi();
}
function recordScoresAndCalculateStats() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var answers = data[1]; // 正解は2行目にあると仮定
var numParticipants = data.length - 2;
var numQuestions = 20; // 20問の正誤判定
var correctCounts = new Array(numQuestions).fill(0); // 各問題の正答数を格納する配列
// 各参加者のスコアを計算し、シートに記録
for (var i = 2; i < data.length; i++) {
var newRow = [];
var totalScore = 0; // 個人の合計点
newRow.push(''); // A列は空白
newRow.push(''); // B列は空白
newRow.push(data[i][2]); // 社員番号をC列に
newRow.push(data[i][3]); // 氏名をD列に
for (var j = 4; j < 4 + numQuestions; j++) {
if (data[i][j] === answers[j]) {
newRow.push(5); // 正解は5点
totalScore += 5;
correctCounts[j - 4]++; // 正答数をカウント
} else {
newRow.push(0); // 不正解は0点
}
}
newRow.push(totalScore); // 個人の合計点を追加
sheet.appendRow(newRow); // 新たな行をシートの末尾に追加
}
// 各問題の正解率を計算し、シートに記録
var statsRow = ['', '', '', '']; // A列、B列、社員番号、氏名の列を空白に
for (var k = 0; k < numQuestions; k++) {
var correctRate = (correctCounts[k] / numParticipants) * 100;
statsRow.push(correctRate.toFixed(2) + "%"); // 正解率をパーセンテージで表記
}
sheet.appendRow(statsRow); // 統計情報の行をシートの末尾に追加
exportSheetToExcel();
}
function exportSheetToExcel() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetId = spreadsheet.getId();
var sheetId = spreadsheet.getActiveSheet().getSheetId();
var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx&gid=" + sheetId;
Logger.log("Download your Excel file here: " + url);
}