できること
- Spreadsheetに記録した、学生の欠席回数に応じたリマインドのメールをGmailで自動送信。
- リマインドの回数は、欠席2回目、3回目、4回目の3度で、それぞれ1度しか送信しない。
- メールを送った回数を、送信するたびに、自動でSpreadsheetに記録するようなコードを含めている。よって、各講義での欠席を記録するだけでよい（出席 = 1, 欠席 = 0）。
注意点
メール送信の上限に注意
一日に送ることのできるメールの回数は、無課金アカウントだと一日100通、Google Workspace accounts（課金）だと1500通みたいです[1]。
各自のニーズに合わせて書き換えてください
どのセルを参照するかは、どこのセルに何のデータがあるかによって異なるので、各自のSpreadsheetに合わせてコードを修正してください。
配列のインデックスは0から始まる
0から数えるという意味で、例えば、セルA1は0行目、0列目のセルと見なされます。エラーに繋がりやすいので気をつけましょう！
いざ実装
Spreadsheetの用意
データが記録されているSpreadsheetを開きます。今回は、こんな感じでデータが入力されています。
CからG：講義があった日時で、出席していれば1、欠席だと0を入力しています。
「欠席数」：CからGまでを合計。つまり、累積欠席数
「メール通知回数」：リマインドのメールを送った回数です。
「残り欠席可能数」：欠席できる回数です。
追記（20230927）：これから出欠のデータを集める場合
「欠席数」、「メール通知回数」、「残り欠席可能数」には、常に何らかの数値が入力されている状態にしておいてください。でないと、以下のコードは動きません。例えば、講義開始前に準備をする場合、「欠席数」、「メール通知回数」、「残り欠席可能数」が空白のセルの場合、処理が行われません。ですので、以下の準備が必要です。
- 「欠席数」のセルには、
countif関数などで、欠席数をあらかじめ入力してください（countif(セルの範囲, "0")）。講義開始直前は 0 が入力されていると思います。 - 「メール通知回数」にははじめ、 0 を入力しておいてください。メールを送るたびに自動でここの数は変化します。
- 「残り欠席可能数」のセルにも値の入力がされている必要があります。例えば、4回欠席でアウトであれば、
4-(欠席数のセル)のように指定しておくとよいでしょう。
↓例えば、最初はこのような形になっていればOKです。
コードの貼り付け
Spreadsheetのツールバーから「拡張機能」→ 「Apps Script」を選択します。そして、「コード.gs」に以下のコードを貼り付けます。
！注意！追記（2024/6/29）
// 欠課数が4以上となった学生（単位を落とした学生）へメールを送らないように設定
if(statusValue >= 5){return}
| 上のコードをつけ加えました。理由として、すでに4回欠席した学生の欠席数を記録し続けたい場合があると思います。しかし、これまでのコードでは、0を4回以上加えると、空メールが送信されていました（欠席が4回以上の場合のメッセージを設定していなかったため）。ですので、欠席数の合計が4を超えた場合にはメールを送らないように明示しています。 |
|---|
function SendEmailsBasedOnCondition() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップ
var row = data[i];
var email = row[1]; // B列「メールアドレス」
var statusCell = sheet.getRange(i + 1, 8); //H列「欠席数」*この指定方法では0からではなく1から数える
var status = statusCell.getValue();
var rest = row[9]; //J列「残りの欠席回数」
var numcell = sheet.getRange(i + 1, 9); // I列「メール通知回数」*この指定方法では0からではなく1から数える
// ↓ セルの値を取得し、それらを数値に変換してから減算する。
var statusValue = parseFloat(status);
var numCellValue = parseFloat(numcell.getValue());
//結果数 - メールの通知数が2のセルのみ、メール送信の手続きへ（重複して送信しないように)
var count = statusValue - numCellValue;
// 欠課数が4以上となった学生（単位を落とした学生）へメールを送らないように設定
if(statusValue >= 5){return}
// ↓ 回数によって、メッセージを分ける
if (count == 2) {
if (statusValue == 2 || statusValue == 3){
var subject = "欠席について";
var message = "あと" + rest + "回欠席すると落単（落胆）です";
} else if (statusValue == 4){
var subject = "欠席について";
var message = "本日4回目の欠席されましたので、単位不認定となりました。";
}
// メールを送信
MailApp.sendEmail(email, subject, message);
// I列「メール通知回数」のセルに、メールを送信するたびに1を加算
var numcellValue = numcell.getValue();
numcell.setValue(numcellValue + 1);
}
}
}
テスト
「実行」を押すと、書いたコードが動くか実際にテストできます。トリガーを設定すれば（次のセクション）、実装したコードを任意のタイミングで実行できます。
トリガーの設定
今回実装した処理を、どのタイミングで実行するかを指定します。指定すると、そのタイミングで定義した処理が自動で実行されます[2]。目覚ましのマークをクリックし、「+ トリガーを追加」を押すと、以下の画面が表示されます。「イベントの種類を選択」以外は、画像と同じように設定してください。設定したら「保存」クリックしてください。
実行結果
実行した結果、それぞれ画像のようなメールが届きました。
*簡易的なメッセージです、いつもはもっと温かいメッセージです。
以下のブロックの、messageでメッセージの中身を定義します。改行等も、記号を入れればできます！
// ↓ 回数によって、メッセージを分ける
if (count == 2) {
if (statusValue == 2 || statusValue == 3){
var subject = "欠席について";
var message = "あと" + rest + "回欠席すると落単（落胆）です";
} else if (statusValue == 4){
var subject = "欠席について";
var message = "本日4回目の欠席されましたので、単位不認定となりました。";
}
// メールを送信
MailApp.sendEmail(email, subject, message);
// I列「メール通知回数」のセルに、メールを送信するたびに1を加算
var numcellValue = numcell.getValue();
numcell.setValue(numcellValue + 1);
}
2回欠席 or 3回欠席した学生へのリマインド
4回欠席（落単）の学生へのリマインド
【補足】なぜ「結果数 - メールの通知数」が2のセルだけメールを送る？
以下の抜粋した箇所の理由を説明します。ここは、どのタイミングで送りたいかで各自変更してください。
//結果数 - メールの通知数が2のセルのみ、メール送信の手続きへ（重複して送信しないように)
var count = statusValue - numCellValue;
// ↓ 回数によって、メッセージを分ける
if (count == 2) {
僕の場合、メールをリマインドする学生は、2回～4回欠席した学生です。そして、その学生の欠席回数と、リマインドメールを送る回数には、以下の関係があります。
欠席 2 回 → リマインドメールを送った回数 0 回 = 2
欠席 3 回 → リマインドメールを送った回数 1 回 = 2
欠席 4 回 → リマインドメールを送った回数 2 回 = 2
なので、「結果数 - メールの通知数」が2となっている学生だけメールを送ればよいことになります。このように指定すると、欠席回数1以下の学生にメールを送ることもないですし、同じメッセージを何回も送ったりすることはないです。
*「メール通知回数」の数は、メールを送るたびに、自動で1が追加されます。
最後に
GASはあまり触ったことがないので、もっと良い関数や、コードの書き方等ございましたら、コメント欄、編集サジェストよろしくお願いいたします！