小学校での日記活動は、児童の自己表現力や思考力を育む上で非常に重要な役割を果たします。しかし、先生方が児童一人ひとりの日記を丁寧に評価し、フィードバックをすることは、時間的にも労力的にも大きな負担となります。そこで、Googleフォーム、スプレッドシート、そしてGoogle Apps Script（GAS）を組み合わせることで、この課題を解決するシステムを構築しました。
この記事では、その具体的な仕組みと、GASコードの詳細について解説します。
システムの概要
このシステムは、以下の3つの主要な機能で構成されています。
- データ収集: Googleフォームを使って、児童が毎日1行日記を入力します。
- データ集約と評価分析: フォームのデータは自動的にスプレッドシートに集約され、GASを使って日記内容の評価とレジリエンス（困難に立ち向かう力）分析を行います。
- ドキュメント出力: 評価結果と分析結果を、児童一人ひとりの個別ドキュメントとして出力し、配布できるようにします。
1. データ収集：Googleフォーム
児童が毎日日記を記入するためのフォームを作成しました。
以下のような項目を設定してあります。
- タイムスタンプ: 自動で記録されるように設定
- 学年: プルダウンメニューなどで選択
- 番号: プルダウンメニューなどで選択
- 日記内容: 1行で記述できるテキストエリア
フォームの回答は、スプレッドシートの「MainTable」シートに自動的に保存されるように設定します。
2. データ集約と評価分析：GAS
スプレッドシートに記述されたGASコードを使って、評価と分析を行います。GASコードは、大きく分けて以下の3つの機能を提供します。
- メニュー作成: スプレッドシート上にメニューを追加し、評価シート作成とレジリエンス評価の実行を可能にします。
- 個別評価シート作成: 各児童の日記内容を月ごとに集計し、評価とコメントを加えて、個別のGoogleドキュメントとして出力します。
- レジリエンス評価: 日記内容を基にGemini APIを使用してレジリエンススコアを算出します。
以下が、GASコードの詳細です。
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu("評価シート作成")
.addItem("個別評価シート作成", "generateSingleReport")
.addToUi();
ui.createMenu("レジリエンス評価")
.addItem("月毎レポート作成", "evaluateResilience")
.addToUi();
}
function generateSingleReport() {
var ui = SpreadsheetApp.getUi();
ui.alert("個別評価シート作成を開始します。");
var ss = SpreadsheetApp.getActiveSpreadsheet();
// ★GoogleドライブのフォルダIDとGoogleドキュメントのテンプレートIDを設定
var docTemplateId = '( )'; // GoogleドキュメントのテンプレートID
var folderId = '( )'; // GoogleドライブのフォルダID
var settingsSheet = ss.getSheetByName("設定");
if (!settingsSheet) {
Logger.log("設定シートが見つかりません。スクリプトを停止します。");
ui.alert("設定シートが見つかりません。スクリプトを停止します。");
return;
}
var monthNames = settingsSheet.getRange(2, 1, settingsSheet.getLastRow() - 1, 1).getValues().flat();
if (monthNames.length === 0) {
Logger.log("設定シートにデータがありません。スクリプトを停止します。");
ui.alert("設定シートにデータがありません。スクリプトを停止します。");
return;
}
var firstSheet = ss.getSheetByName(monthNames[0]);
if (!firstSheet) {
Logger.log("最初の月のシートが見つかりません。スクリプトを停止します。");
ui.alert("最初の月のシートが見つかりません。スクリプトを停止します。");
return;
}
var data = firstSheet.getDataRange().getDisplayValues();
var folder = DriveApp.getFolderById(folderId);
var docCopy = DriveApp.getFileById(docTemplateId).makeCopy("個別評価シート", folder);
var doc = DocumentApp.openById(docCopy.getId());
var body = doc.getBody();
body.clear();
for (var i = 1; i < data.length; i++) {
// ページ区切りを挿入(最初の生徒以外)
if (i > 1) {
body.appendPageBreak();
}
var 学年 = data[i][0];
var 番号 = data[i][1];
// タイトルを追加
body.appendParagraph("1行日記個別評価（配布用）").setHeading(DocumentApp.ParagraphHeading.TITLE);
body.appendParagraph("学年: " + 学年 + " 番号: " + 番号).setHeading(DocumentApp.ParagraphHeading.HEADING2);
var tableData = [];
tableData.push(["月", "日記内容", "評価", "コメント"]);
for (var j = 0; j < monthNames.length; j++) {
var sheet = ss.getSheetByName(monthNames[j]);
if (!sheet) {
Logger.log("シート '" + monthNames[j] + "' が見つかりません。スキップします。");
continue;
}
var rowData = sheet.getRange(i + 1, 3, 1, 3).getDisplayValues()[0];
var 日記内容 = rowData[0] || "（記録なし）";
var 評価 = rowData[1] || "（評価なし）";
var コメント = rowData[2] || "（コメントなし）";
tableData.push([(j + 1) + "月", 日記内容, 評価, コメント]);
}
if (tableData.length > 1) {
var table = body.appendTable(tableData);
// **セルのフォントサイズと幅を調整**
for (var row = 0; row < table.getNumRows(); row++) {
for (var col = 0; col < table.getRow(row).getNumCells(); col++) {
var cell = table.getRow(row).getCell(col);
var text = cell.getText();
// 「月」と「評価」の列を小さくする
if (col === 0) {
cell.setWidth(25); // 月の列の幅を25ポイントに設定（1～12の数字に対応）
cell.setFontSize(8);
} else if (col === 2) {
cell.setWidth(35); // 評価の列の幅を35ポイントに設定（分数に対応）
cell.setFontSize(8);
} else {
cell.setWidth(200); // 幅を200ポイントに設定
cell.setFontSize(12); // **通常のサイズ**
}
}
}
}
body.appendParagraph("");
}
doc.saveAndClose();
Logger.log("個別評価シートが正常に作成されました。");
ui.alert("個別評価シートが正常に作成されました。");
}
function evaluateResilience() {
var ui = SpreadsheetApp.getUi();
ui.alert("レジリエンス評価を開始します");
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var mainTable = sheet.getSheetByName("MainTable");
var data = mainTable.getDataRange().getValues();
if (data.length < 2) {
ui.alert("データが不足しています。");
return;
}
var header = data[0].map(h => h.toString().trim());
var dateIndex = header.indexOf("タイムスタンプ");
var diaryIndex = header.indexOf("日記内容");
var gradeIndex = header.indexOf("学年");
var numberIndex = header.indexOf("番号");
if (dateIndex === -1 || diaryIndex === -1 || gradeIndex === -1 || numberIndex === -1) {
ui.alert("必要な列が見つかりません。「タイムスタンプ」「学年」「番号」「日記内容」の列があるか確認してください。");
return;
}
var groupedByMonth = {};
for (var i = 1; i < data.length; i++) {
if (!data[i][diaryIndex]) continue;
var date = new Date(data[i][dateIndex]);
var yearMonth = date.getFullYear() + "-" + ("0" + (date.getMonth() + 1)).slice(-2);
if (!groupedByMonth[yearMonth]) {
groupedByMonth[yearMonth] = {};
}
var key = data[i][gradeIndex] + "-" + data[i][numberIndex];
if (!groupedByMonth[yearMonth][key]) {
groupedByMonth[yearMonth][key] = {
学年: parseInt(data[i][gradeIndex], 10) || 0,
番号: parseInt(data[i][numberIndex], 10) || 0,
日記内容: []
};
}
groupedByMonth[yearMonth][key].日記内容.push(data[i][diaryIndex]);
}
Object.keys(groupedByMonth).forEach(month => {
var sheetName = month + "レポート";
var reportSheet = sheet.getSheetByName(sheetName) || sheet.insertSheet(sheetName);
var output = [["学年", "番号", "日記内容", "レジリエンス評価", "コメント"]];
var entries = Object.values(groupedByMonth[month]);
entries.sort((a, b) => {
if (a.学年 !== b.学年) return a.学年 - b.学年;
return a.番号 - b.番号;
});
entries.forEach(entry => {
var joinedDiary = entry.日記内容.join(", ");
var response = callGoogleGeminiAI(joinedDiary);
if (response.score === "エラー") {
output.push([entry.学年, entry.番号, joinedDiary, "エラー", response.comment]);
} else {
output.push([entry.学年, entry.番号, joinedDiary, response.score, response.comment]);
}
});
reportSheet.clear();
reportSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
});
ui.alert("月ごとのレポート作成とレジリエンス評価が完了しました！");
}
function callGoogleGeminiAI(text) {
var GOOGLE_API_KEY = PropertiesService.getScriptProperties().getProperty('GOOGLE_API_KEY');
if (!GOOGLE_API_KEY) {
Logger.log("APIキーが設定されていません。スクリプトプロパティに GOOGLE_API_KEY を設定してください。");
return { score: "エラー", comment: "APIキーが設定されていません。スクリプトプロパティに GOOGLE_API_KEY を設定してください。" };
}
var prompt = `次の日記の内容をもとに、レジリエンス力（困難に立ち向かう力）をポジティブに評価してください。
評価は 0〜10 点（0: 非常に低い、10: 非常に高い）で数値化し、その理由を励ましの言葉とともに200文字以内で説明してください。
【評価基準】
- 自分の気持ちを表現できているか
- 他者との関わりを大切にしているか
- 前向きな考え方ができているか
- 困難や挑戦に対する姿勢はどうか
【日記内容】：「${text}」
出力フォーマット：
{
"評価": "<数値>/10",
"コメント": "<励ますメッセージ>"
}
必ずJSON形式で出力してください。`;
var url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${GOOGLE_API_KEY}`;
var payload = {
contents: [{ role: "user", parts: [{ text: prompt }] }]
};
var options = {
method: "post",
headers: {
"Content-Type": "application/json"
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
var response = UrlFetchApp.fetch(url, options);
var json = JSON.parse(response.getContentText());
Logger.log("APIレスポンス: " + JSON.stringify(json));
if (json.candidates && json.candidates.length > 0) {
var output = json.candidates[0].content.parts[0].text.trim();
try {
var parsedOutput = JSON.parse(output);
return { score: parsedOutput["評価"], comment: parsedOutput["コメント"] };
} catch (e) {
Logger.log("JSONパースエラー: " + output + ", Error: " + e);
return { score: "エラー", comment: "AIの応答フォーマットが正しくありません。" };
}
}
} catch (error) {
Logger.log("APIエラー: " + error);
return { score: "エラー", comment: "APIリクエストに失敗しました。" };
}
return { score: "エラー", comment: "評価に失敗しました" };
}
GASコードの詳細説明
- onOpen(): スプレッドシートを開いた際にメニューを追加します。
- generateSingleReport():
- 設定シートから月ごとのシート名を取得し、各児童のデータを集計します。
- GoogleドライブのフォルダIDとGoogleドキュメントのテンプレートIDを設定します。
- GoogleドライブのフォルダIDの取得方法: Googleドライブで、ドキュメントを保存したいフォルダを開き、URLのhttps://drive.google.com/drive/folders/の後ろにある文字列がフォルダIDです。
- GoogleドキュメントのテンプレートIDの取得方法: テンプレートとして使用したいGoogleドキュメントを開き、URLのhttps://docs.google.com/document/d/の後ろにある文字列がドキュメントIDです。
- Googleドキュメントのテンプレートをコピーし、各児童の個別評価シートを作成します。
- 各月の記録をテーブル形式でドキュメントに追加します。
- 表の表示を調整し、見やすくします。
- evaluateResilience():
- MainTableシートから日記データを読み込みます。
- 各児童のデータを月ごとに集計します。
- Gemini APIに日記内容を送り、レジリエンス評価を行います。
- この処理は callGoogleGeminiAI(text) 関数で行われます。
- 評価結果を月ごとのシートに出力します。
- callGoogleGeminiAI(text): レジリエンス評価のためのGemini API連携:
* APIキーの設定:- Gemini APIを利用するには、Google Cloud PlatformでAPIキーを取得し、GASのスクリプトプロパティに GOOGLE_API_KEY という名前で設定する必要があります。
- スクリプトエディタで、「ファイル」>「プロジェクトのプロパティ」>「スクリプトのプロパティ」で設定できます。
* プロンプトの作成: - Gemini APIに送信するプロンプトは、日記の内容を基にレジリエンス力を評価させるための指示です。
- 以下のプロンプトを使用します。（GASコードから抜粋）
次の日記の内容をもとに、レジリエンス力（困難に立ち向かう力）をポジティブに評価してください。 評価は 0〜10 点（0: 非常に低い、10: 非常に高い）で数値化し、その理由を励ましの言葉とともに200文字以内で説明してください。 【評価基準】 - 自分の気持ちを表現できているか - 他者との関わりを大切にしているか - 前向きな考え方ができているか - 困難や挑戦に対する姿勢はどうか 【日記内容】：「${text}」 出力フォーマット： { "評価": "<数値>/10", "コメント": "<励ますメッセージ>" } 必ずJSON形式で出力してください。 - このプロンプトは、以下の点を考慮しています。
- 評価の明確化: 0〜10点の数値で評価するように指示しています。
- 評価基準の設定: 具体的な評価基準（自己表現、他者との関わり、前向きな考え方、挑戦姿勢）を示しています。
- ポジティブな評価: 励ましの言葉とともにコメントを返すように指示しています。
- 出力形式の指定: JSON形式で出力するように指定し、GASで扱いやすい形式にしています。
* APIリクエストの送信:
- UrlFetchApp.fetch() を使用して、Gemini APIにリクエストを送信します。
- リクエストのオプションには、メソッド、ヘッダー、ペイロードが含まれます。
* APIレスポンスの処理: - APIからのレスポンスをJSON形式で解析します。
- JSONデータから、評価スコアとコメントを抽出します。
- もしJSON解析に失敗した場合、エラーメッセージを返します。
* エラー処理: - APIリクエストに失敗した場合や、JSON解析に失敗した場合に、エラーメッセージを返します。
- APIキー設定: Gemini APIを使用するためには、スクリプトプロパティにGOOGLE_API_KEYを設定する必要があります。
3. ドキュメント出力：個別評価シート
generateSingleReport()関数によって、各児童の日記内容と評価が記載された個別のGoogleドキュメントが生成されます。
これらのドキュメントは、先生が児童にフィードバックしたり、保護者の方に活動の様子を伝えたりする際に活用できます。
事前準備：テンプレートと設定シート
このシステムを円滑に運用するためには、以下の事前準備が必要です。
- Googleドキュメントのテンプレート:
- 個別評価シートのベースとなるテンプレートを作成します。
- テンプレートには、タイトル、学年・番号を表示する箇所、月ごとの評価を記載するテーブルなどを設けます。
- このテンプレートのドキュメントIDをGASコードに設定します。
- テンプレートの構成例:
- タイトル: 「1行日記個別評価（配布用）」
- 学年・番号: 「学年: {学年} 番号: {番号}」
- テーブルヘッダー: 「月」「日記内容」「評価」「コメント」
- スプレッドシートの「設定」シート:
- 月ごとのシート名をリスト形式で記述します。
- 1行目はヘッダーとして、2行目以降に月ごとのシート名（例: 4月、5月、6月…）を入力します。
- GASコードはこの設定シートを参照し、各月のデータを取得します。
- 例:
| | A |
| — | ———– |
| 1 | 月 |
| 2 | 4月 |
| 3 | 5月 |
| 4 | 6月 |
- 例:
- 評価・コメントシート（月ごと）:
- 各月のシートを作成します。
- 各シートには、児童名簿に合わせて、以下の形式でデータを入力できるようにしてください。
- A列：学年
- B列：番号
- C列：日記内容
- D列：評価
- E列：コメント
システムのメリット
- 効率化: 日記の集計、評価、ドキュメント作成作業を自動化し、先生の負担を大幅に軽減します。
- 個別対応: 児童一人ひとりの日記内容を丁寧に分析し、個別の評価シートを作成できます。
- 可視化: 児童の成長過程を、月ごとのデータと個別評価シートによって可視化できます。
- 公平性: Gemini APIによるレジリエンス評価を取り入れることで、評価の客観性を高められます。
- モチベーション向上: 児童が自分の成長を可視化することで、日記活動へのモチベーションを維持できます。
導入の注意点
- APIキーの設定: Gemini APIを使用するには、Google Cloud PlatformでAPIキーを取得し、GASのスクリプトプロパティに設定する必要があります。
- テンプレートの作成: 個別評価シートのベースとなるGoogleドキュメントのテンプレートを作成する必要があります。
- 設定シートの準備: 月の名前を設定する「設定」シートを準備する必要があります。
- 評価基準の明確化: Gemini APIの評価基準は、プロンプトによって調整可能ですが、必要に応じて微調整してください。
まとめ
このシステムは、GASとGoogleの各種ツールを組み合わせることで、小学校での日記活動をより効果的に実践するための強力なツールとなります。先生方の業務効率化はもちろん、児童の自己成長を促す上でも、非常に役立つでしょう。ぜひ、この記事を参考に、あなた自身の学校に合わせたシステムを構築してみてください。
コメント