#40 生徒からの定時連絡を管理する
以下の記事でネタにしていた「Googleフォーム による健康観察」を簡単にするためのプログラムです。
実際の GAS によるプログラムの解説は一切行っておらず、プログラムの導入と動作についてのみ説明しています。そんなに高度な内容ではないと思いますが、それなりの長文となっています。
プログラムのポイント（利点）
今回のプログラム作成にあたってのポイントは、以下のような感じです。
毎日の指定された時間（チェック時刻）に
未回答の生徒にはメールで催促する。（催促メール）
その時点までの回答状況を、教師にメールで通知する。（状況メール）
個人の判別には、Google Classroom に登録されているメールアドレスで判別します。 ※組織の Google アカウントでログインしている状態での回答を想定
送信制限がかからないように、まとめてメールを送信するなど配慮。
Google Classroom の指定されたクラスを用いて
回答対象となる「教師」と「生徒」は、クラスに設定されている「教師」「生徒」の情報を利用する。
指定された時間（投稿時刻）に回答を促すメッセージ（案内）を予約投稿をする。
GAS のプログラムについて、理解できていなくても
各種のトリガー関数の設定も、ボタン一つで行える。一度設定を行えば、以降は自動的に設定が継続。
ある程度のカスタマイズが行えるように、Google スプレッドシートのシート上で可変項目が設定できるようにする。
プログラムによって、毎日指定された時間にずれなく処理を行う。
回答の収集に使用する Google フォームは用意していない。 ※別途作成するか、既存の Google フォームを使用。
同様のプログラムやテンプレートが、Web 上で公開されていると思いますが、上記の中に気になる内容があれば、試してみてください。
導入の手順
以降、つらつらと導入の手順を書き連ねます。場合によっては、一度すべての流れに目を通してから、実際に作業・操作した方が理解しやすいかもしれません。
手順１ スプレッドシートのコピー
以下の URL にアクセスして、自身の Google ドライブに、スプレッドシートをコピーします。 ※以降の操作を行うアカウントでログインした状態で、以下の URL にアクセスしてください。
https://docs.google.com/spreadsheets/d/1ZnlPPNA648i30cdLlNzUzIxbAFAQSqUezApRHPR8TRw/copy
コピーしたスプレッドシートは、適切なファイル名に変更し、適当なフォルダに移動させておきましょう。
スプレッドシートの中には、下図のような 4つのシートが保存されています。
シート①では、処理の対象となるクラスを指定するために、自身が「教師」として参加しているクラスの一覧を取得します。 ※初期状態では、見出し行しかない状態になっています。
シート②では、シート①で得られたクラスの一覧から一つを選んで、そのクラスに参加している「教師」と「生徒」の一覧を取得します。 ※初期状態では、見出し行しかない状態になっています。
実際の運用時に、生徒からの回答を確認するのも、このシートになります。
シート③では、この GAS によるプログラムのカスタマイズ可能な設定内容が一覧になっています。
このシートの内容については、GAS のプログラムを変更することなく、簡単に変更が可能です。
シート④には、プログラムの動作において意味や効果はありません。
シート①～③を区別するためにそれぞれのシートに色を設定してあります。Google が利用している 4色を利用したために、1色余っていたので何もしないシートですが追加しただけです。
手順２ カスタマイズ可能な設定項目を確認
まずは、シート③を確認します。下図は、ほぼ初期状態のままですが、いくつか設定内容を確認してください。少なくとも、セル B4 に生徒が回答するための「Google フォーム」の URL を入力しましょう。
セル B1・B2 ：チェックを行う時間・分 ※図の場合、8:25
セル B3 ：このプログラムが送信するメールの送信者の名前
セル B4 ：生徒が回答するための「Google フォーム」の URL
セル B7 ：生徒に対して催促メールを送信するかどうか
セル B8 ：生徒への催促メールの件名
セル B9 ：生徒への催促メールの本文（固定部分）
セル B12 ：教師への状況メールを送信するかどうか
セル B13 ：教師への状況メールの件名
セル B14 ：教師への状況メールの本文（固定部分）
セル B17 ：クラスへの投稿を行うかどうか
セル B18・B19 ：クラスへの投稿を行う時間・分 ※図の場合、6:00
セル B20 ：クラスへの投稿の本文
チェックする時間、予約投稿を行う時間については、それぞれの運用に合わせて変更しなければならないと思います。
メールや投稿の細かな体裁を変更しようとした場合には、プログラムを変更しなければなりませんが、上記の内容であればプログラムを変更することなく、簡単に変更できます。
手順３ フォームの用意とスプレッドシートとの紐づけ
このプログラムでは、回答を収集する Google フォームの用意は行っていません。別途作成するか、既存のフォームを利用します。
フォームの設定で注意していただきたいのは、「メールアドレスを収集する」を ON に設定して、Google スプレッドシートに集約される回答データにメールアドレスが含まれるようにしてください。
メールアドレスを手入力させると、入力間違いが発生しかねないので、「…と信頼できる組織のユーザーに限定する」を ON にすると、確実だと思います。
このフォームは繰り返し使用するので、「回答を 1回に制限する」は OFF にしておきます。
作成したフォームを、手順１で作成したスプレッドシートと紐づけます。
フォームの「回答」タブの右上にあるスプレッドシートのアイコン（紐づけされていなければ、「スプレッドシートの作成」とツールチップが表示されます）を押し、「回答先の選択」ダイアログを表示させます。
回答の保存先として、「既存のスプレッドシートを選択」を選択して、「作成」をクリックします。
下図のような画面が表示されるので、手順１で作成したスプレッドシートを選択して、画面下部の「選択」ボタンを押します。
選択したスプレッドシートに、フォームの回答が保存されるシートが作成されました。ここで確認していただきたい点が １つ！
下図のように 2列目（B列）にメールアドレスが保存されるようになっていることを確認してください。
今回のプログラムでは、この 2列目のメールアドレスによって、回答／未回答の判別を行っているので、この点は重要です。
手順４ クラスの確認
シート①を選択して、画面上部の「参加クラスの取得」ボタンを押します。
はじめて、プログラムを実行する場合には、以下のような承認を求める画面が表示されます。
このダイアログは、Google スプレッドシート内に埋め込まれている GAS のプログラムを実行してもよいか、どのアカウントで実行するのかを確認するものです。
この確認の手順については、以下の記事にまとめてあるので参照してください。
「承認が必要」ダイアログでの確認を求められた際には、確認（承認）だけで処理が終了してしまうので、もう一度、「参加クラスの取得」ボタンを押します。
実行しているアカウントが、「教師」として参加している Google Classroom のクラスが一覧で表示されます。 ※実際に取得された情報がマスク（モザイク処理）されているので、ちょっとイメージしづらくて申し訳ありません。
このシート①での操作は、これで終了です。
注意： シート②からシート①の内容を参照しているため、導入が完了した後も、「取得情報のクリア」ボタンを押すなど、このシートの内容を変更したりしないでください。
手順５ 教師と生徒の確認
次は、シート②を選択します。
セル B2 は初期状態で空欄になっていますが、セルの右端にある「▼」をクリックすると、下図のように手順４で取得できたクラスが選択できるようになっています。
上図のように表示されたクラスの中から、対象となるクラスを選択すると、セル B1 に当該クラスの courseId が表示されます。
セル B1 と B2 は、手入力することを想定していません。セル B2 に表示される選択肢の中から、対象となるクラスの名前を選択すれば、セル B1 には対応するクラスの courseId が表示されます。
想定されるケースとしては、スプレッドシートの MATCH 関数と INDEX 関数を用いて、クラス名から courseId を取得しているので、同じ名前のクラスが存在していた場合には、意図した動作をしない可能性があります。 ただ、そのようなクラス名は設定されないと考えられるため、問題にはならないでしょう。
courseId が表示されていることを確認して、「クラス参加者を取得」ボタンを押します。
プログラムが実行されて、下図のようにクラスの参加者が一覧で表示されます。 ※実際に取得された情報がマスク（モザイク処理）されているので、ちょっとイメージしづらくて申し訳ありません。
これらの情報に指名が設定されているのであれば、Google フォームで得られる回答にメールアドレスが含まれていれば、氏名を入力してもらう必要はありません。
この一覧に表示されているのは、実際にクラスに参加している「教師」「生徒」だけです。招待されているものの、実際に参加していない場合には一覧に表示されません。
この一覧表示された情報は、「教師」「生徒」の順に表示されていますが、運用しやすいようにソート（並び替え）を行って構いません。
列 G「Check」には、「教師」であれば 1、「生徒」であれば 2 が設定されています。この値によって、チェックを行った際の「催促メール」「状況メール」の対象になるかが決まります。
クラスには参加しているものの対象から除外したい場合には、1 か 2 ではない値を設定するか、空欄にしてください。
このように Google Classroom のクラスに登録されている「教師」「生徒」の情報をもとにして、処理の対象となるアカウントを設定できます。
手順６ トリガー関数の設定
手順５までの操作・設定が終わったら、シート②で「トリガー関数の設定」ボタンを押します。
このボタンを押すことで、
手順２で設定された「チェック時刻」に集約処理を行うトリガー関数の設定
手順３で紐づけられたフォームからの回答を処理するためのトリガー関数の設定
手順２で設定された「投稿時刻」に、クラスへの予約投稿を作成
といった処理を行います。
「トリガー関数の設定」とは表記されているものの、初回の予約投稿の作成もここで行われます。
この時点で、当該クラスにアクセスすると、「保存済みのお知らせ」として予約投稿が表示されています。
「トリガー関数の設定」を複数回実行しても、重複してトリガー関数が設定されることはありませんが、上図のような予約投稿は重複してしまいます。
シート③での設定内容を変更し、「トリガー関数の設定」ボタンを再度押した場合には、重複する予約投稿を削除するようにしてください。
これで準備は完了です！
動作の流れ
Step 0 予約投稿の公開待ち
導入の手順に沿って設定されていれば、下図のように対象となっているクラスには予約投稿が作成されているはずです。
決められた時間帯に回答するように連絡されているのであれば、予約投稿が必要ないケースもあると思います。
その場合には、シート③のセル B17 に何か入力（空でないことを判定しているので 1 と入力するだけでよい）して、予約投稿を作成しないようにも設定できます。
回答のための Google フォームの URL は固定なので、毎日連絡しなくてもいいという考え方もあると思います。
Step 1 予約投稿の公開
設定された時間になると予約投稿が公開されます。
1行目の文章が、シート③で設定した内容となります。
3行目以降の「以下の URL から…」という文章は固定メッセージです。
公開されるタイミングは、指定した時間にきっちり公開されるわけではないようなので余裕を持たせる必要があると思います。上図の例であれば、6:00 に設定してある予約投稿が、実際に公開されたのは 6:04 と表示されています。
Step 2 生徒がフォームから回答する
それぞれの生徒が公開された予約投稿や、事前に連絡された URL から回答を行うと、その回答内容はスプレッドシートのシート内に蓄積されていきます。
回答される度に設定されたトリガー関数によって処理が行われ、シート②の「教師」「生徒」の一覧部分の右側には、最新の回答内容がコピーされます。
この一覧に表示されていないメールアドレスでの回答があった場合、その回答は無視されます。 ※このシートにコピーされないだけで、Google フォームの回答には蓄積されています。
Step 3 生徒への催促メール送信（チェック時刻）
シート③で設定されたチェック時刻になっても回答していない生徒には、以下のようなメールが送信されます。
このメールは、プログラムのトリガーを設定した「教師」のメールアドレスから送信されますが、送信者の名前はシート③で設定されたセル B3 の内容が設定されます。 ※写真ではマスキング処理されていますが、アイコンは当該メールアドレスのアイコンが表示されます。
回答しやすいように、回答のための URL も記載してあります。
未回答者には BCC: で一斉に送信しており、TO: は空欄になっています。
Step 4 教師への状況メール送信（チェック時刻）
前項の Step 3 と同じタイミングで、教師への状況メールも送信されます。
このメールは、プログラムのトリガーを設定した「教師」のメールアドレスから送信されますが、送信者の名前はシート③で設定されたセル B3 の内容が設定されます。 ※写真ではマスキング処理されていますが、アイコンは当該メールアドレスのアイコンが表示されます。
このメールは、「教師」として登録されていた、シート②の列 G「Check」が 1 に設定されている人に送信されます。上図では、自分とマスキング処理された部分の 2名に送信されています。
回答済であるかの判断は、チェックを行う日の 0:00 からチェック時刻までに回答しているかどうか、で行っています。 そのため、前日の 23時過ぎに回答していた場合には、「未回答」と判断されます。
「回答済」「未回答」の人数をそれぞれ表示し、「未回答」の名前とメールアドレスも列挙します。
回答状況を確認しやすいように、シート②にアクセスするための URL をメール中に記載してあります。
※このプログラムでは、共有設定は行っていないので、手順１でスプレッドシートを作成したアカウント以外の「教師」がアクセスできるようにするための共有設定は、手作業で行ってください。
これで一連の動作は終了です。
チェック時刻の処理の中で、翌日の処理に必要な準備も自動的にプログラムが行ってくれるので、一度設定してしまえば、あとはシート②の内容をチェックするだけで大丈夫です！
補足
チェック時刻の誤差
このプログラムでは、時間ベースのトリガーを使用していますが、Web UI で指定する場合の「時間ベースのトリガー」ではなく、「特定の日時」によって指定しています。
そのため、プログラム中では翌日のチェック時刻に実行されるように「特定の日時」としてトリガーを設定し、「特定の日時」で呼び出されたトリガー関数の中で次回の「特定の日時」のトリガーを設定し直しています。 ※厳密には、実行し終わった「特定の日時」のトリガーの削除も行っています。
実際に実行されたタイミングを確認してみると、「特定の日時」として 8:25 に設定されたトリガーが実際にされたのは
8:25:39
8:25:48
という感じでした。多くのサンプル数で確認したわけではありませんが、ほぼ指定した時間に実行されているような感じです。
以下の記事で、同様の処理をくり返してみましたが、確認した限りでは指定した時間（hh:mm のうち）には実行されているようです。詳細に興味のある方は、以下の記事もご覧ください。
休日の取り扱い
このプログラムでは「休日」という概念がありません。日曜日や祝日であっても、前述の「動作の流れ」をくり返します。
実際の運用では、このような休日をどのように取り扱うのかを、事前に決めておくべきです。
「休日」の判定は簡単ではなく、土曜日や日曜日が「休日」ではなくなったり、平日が祝日などで「休日」になってしまうケースもあります。また学校では夏季休業や冬季休業といった、カレンダー通りではない登校しない「休日」に準ずる日もあり、プログラムで一律には判別しにくいのです。
「休日」として扱う日を別途指定することも考えましたが、処理が煩雑化するだけでなく、持続可能なものではないような気がしたので、プログラム側では対応しませんでした。
追記 2022/08/06：
コメントをいただいたので、簡単な休日判定を追加してあります。簡単な判定を行って「土曜日」「日曜日」「祝日」を処理の対象から除いているだけなので、前述のような学校特有のケースには対応しきれていない部分はご理解ください。
毎日の投稿の扱い
このプログラムでは、対象となるクラスのストリームに予約投稿を作成し、生徒にフォームへの回答を促しています。この投稿は、毎日自動的に追加されていきますが、プログラムでは削除を行っていません。
このプログラムを運用していると、公開された日付だけが違う、同じ内容のお知らせがいくつも並ぶことになります…
これらのお知らせについては、必要に応じて、手作業で削除してください。
また、予約投稿が公開される時間については、指定された時間から 5分くらいの誤差が生じてしまうようです。この詳細については、以下の記事をご覧ください。
条件付き書式やクエリー関数を併用する
シート②に集約される内容は、Google フォームからの回答をコピーしたものとなっています。
特定の内容について、容易に着目できるようにしたいのであれば、「条件付き書式」を利用して、セルの色を変えるなどすれば対応できます。
また、このプログラムはシート②でそれぞれの「生徒」の最新の回答を集約し、未回答であれば催促するなどの管理を目的としたものです。
特定の「生徒」の傾向を分析するといった用途は、QUERY 関数などを用いて、別のシートで分析するなどしてください。
利用を停止する場合
このプログラムを使いはじめたものの利用を停止する場合には、Google スプレッドシートのメニューから「拡張機能」→「Apps Script」を選択して、
GAS のプログラムを編集するエディタを表示させ、画面左側のメニューバーから「トリガー」を選択します。
トリガーとして、下図のように 2つのトリガーが設定されているはずです。
それぞれのトリガーについて、右端の三点リーダーをクリックして、「トリガーを削除」を選択します。設定されているトリガーを 2つとも削除してください。
導入は簡単に行えるようにしましたが、利用の停止はボタン一つでは行なえない点についてはご容赦ください。上記のように手順については、図解してあるので比較的簡単に行えるはずです。
最後に
最後に、お決まりのフレーズなどを書いておきます。
一応の動作確認は行っているものの、不慮のトラブルによって損害等が生じても、責任はとれませんので予めご了承ください。
コメントを含めても 560行くらいのスクリプトであり、実行に際して目的外の場所への書き出しや収集などは行っていません。
特別なエラー処理は行っていないので、意図しないケースでエラーが発生してしまうかもしれません。どうにもならない場合には、ご連絡ください。
わたし自身にしてみると、このような「スクリプト／プログラムを作ること」が目的になっているような感じですが、このスクリプト／プログラムが何かの役に立てば幸いです。
こんなプログラムがあると、「もっと学校の業務が楽になるのに！」というアイディアがあれば、以下の記事からお聞かせください。