欠席連絡Googleフォームがスプレッドシート連携で化けた話
お久しぶりです。みていです。
教員の働き方改革が叫ばれて久しいですが、その中でも「今更？」と思われるようなものを先週あたりからしこしこと作っておりました。なぜか記事が長くなってしまったので飛ばし飛ばしお読みいただければ幸いです。
１．まえがき
この記事は【まだオンラインでの欠席連絡を導入していない学校】や、【とりあえず導入したはいいけど上手く活用できていなさそうでモヤモヤしている学校】の方々向けです。もちろん、学校以外にも流用可能だと思います。
Googleフォーム、Googleスプレッドシート、クエリ等のキーワードに心当たりのある方にはおすすめできると思います。
皆さんご存じの通り、文科省がGIGAスクール構想を進めていく中で、学校現場の化石のような文化や慣習にデジタル化の波が広がっています。
文科省は「GIGAに慣れる」とか「StuDX」とかアヤシイ言葉遣いがお好きなようですが、そこには今回は触れないでおきましょう。
本題ですが、私が作っていたのは「欠席連絡」を受けるGoogleフォームです。そしてこの記事をご覧くださっている方（中でも教員）の多くはご存じだとは思いますが、すでに文科省は「欠席連絡をデジタル化」と銘打ち、働き方改革の事例集にも例を載せています。
そこに「雛形はこちら」という文言と共にGoogleフォームとMicrosoft Formsファイルへの共有リンクが張られています。
私は、Googleフォームの方の雛形しか確認していませんが、あまりに簡素。叩き台が過ぎる。叩かれると無くなりそう。むしろ叩くに値しない……というレベルで、こんなのGoogleフォームを導入できる環境の学校であれば叩き台にもならないんですよね。
じゃあ叩き台くらいにはなるものを作ってみようじゃあないか！と作っていたのが、ここで紹介する欠席連絡フォームです。下記リンクからGoogle Driveのフォルダにアクセスできますので、コピーしてお使いください。
（以下、特段断りがなければ「フォーム」や「スプレッドシート」等はGoogleのアプリケーションを指します。）
Driveのフォルダ内には以下の2つのファイルが入っています。
・欠席連絡フォーム
・フォーム付随のスプレッドシート
フォームだけなら比較的容易に作れる人が多いとは思いますが、Googleフォームの真骨頂はGoogleスプレッドシートとの連携です。簡単な単発の集計くらいであればGoogleフォーム編集画面で [回答] を確認するだけで十分なのですが、
・集計データをいちいち削除したくない
・集計データのシートにソートかけたくない
・クラス別に集計したい
・自分のクラスだけ確認したい
・登録日時順ではなく、出席番号順で見たい
・今日の連絡だけ見たい
というような場合に、上記2つのアプリケーションを上手く組み合わせていく必要があります。「わざわざそこまでしなくても」と、力技でなんとかしようとする人はそうしてください。ただ時間がかかるだけです。
むしろこれらのフォームやスプレッドシートは、パクって参考にしてもらえるほどうれしいことはありません。（その際は無言でもいいのでTwitterとかフォローしてもらえると喜びます。）
それでは、前置きが長くなりましたが、これから2つのファイルを紹介していきたいと思います。
２．欠席連絡フォームについて
2-0. 下準備
・Driveからそれぞれのファイルを開いただけでは、｢閲覧｣のみしかできません。そのため、コピーを作成し、自分のGoogleDrive(マイドライブ)に保存する必要があります。ファイルのメニューから｢コピーを送信｣→｢ドライブに保存｣の順に選び、自分のマイドライブへ保存してください。
・また、Google Apps Script（以下、GASも同義）もインストール推奨なので、インストール方法が分からない際は、以下の記事を参考にしてみてください。
≪超初心者へGoogleAppsScriptを始めるメリットをこれでもかと説明します (高橋宣成さんのブログ「いつも隣にITのお仕事」より)≫
≪Google Apps Script のはじめ方【超初級編】 (Koushi Kagawaさんのnote記事より)≫
2-1. 編集画面の見方と動かし方
①実際にフォームの編集画面を一緒にご覧ください。フォームを開くと以下のような画面になると思います。
このような画面になれば、編集可能です。
②中央上部にある[質問][回答][設定]タブから、[回答]タブを選択し、緑のスプレッドシートアイコンか、その隣の三点リーダーメニューをクリックしましょう。（三点リーダーメニューをクリックした場合、「回答先を選択」をクリックし、「既存のスプレッドシートを選択」の後、『欠席等連絡（回答）』のスプレッドシートを選択してください。）
③基本的には以上でフォームの準備は完了です。質問項目は使用環境に合わせていじってみてください。（質問内容や項目数を変更した場合、スプレッドシート側で多少の手直しが必要となりますが、それは後述します。）
④[設定]に関して、保護者しかアクセスさせないことを想定しているため、アドレス収集やログイン必須等の設定はオフにしています。このへんは利用環境や周知状況に応じて柔軟に変更して良い点かもしれません。
2-2. 利用者画面の挙動
編集画面の右上にある目のアイコンをクリックすると、実際に利用者が入力するフォームを体験することができます。実際に入力してデータ送信までできますので、動作確認等に役立ててください。
①まずはパソコンからアクセスした場合ですが、まあ、見てもらった通りです。氏名入力など、回答必須でない項目もありますので、各校の実情に合わせて変更してください。
②セクション2の日付入力はカレンダーからの選択が可能です。（画像省略）
③遅刻・早退を選んだ場合の時刻入力はPCでもそれ以外でも直接入力です。
④スマホ(iOS/Android)からの見た目もPCと大差ありません。
2-3. できること・できないこと
概要としては上に示したようなことで十分なのですが、もう少し踏み込んで書くと以下のようなことができたりできなかったりします。
①できること
・入力規制
氏名入力の項目において、(半角全角問わず)スペースを入力するとエラーが出るようになっています。以下の画像のように入力規制をかけることで実装可能です。(詳しくは「正規表現」でググってみてください。空白だけでなく、英数字等の入力規制も可能です。)
・回答に応じたセクション移動
例えば、欠席/早退/遅刻などの回答選択によって、「欠席であれば理由選択のセクションに移動」や「遅刻であれば到着予定時刻の入力セクションに移動してから理由入力のセクションに移動」ということができます。
②できないこと
・セクション2の日付入力ですがは、PCから開いた場合はカレンダー入力が可能なのですが、スマホではキーボードを使った入力になってしまいます。（携帯端末でもカレンダー入力やドラムロール選択をさせたい場合はGASからCSSを設定することで解決可能です。実装する気のある方はやってみてください。）
３．付随するスプレッドシートについて
「付随」とは言っていますが、むしろこっちがメインと言っても過言ではありません。こちらもファイルを開きながら確認していきましょう。
……後出しで大変申し訳ないのですが、スプレッドシートの編集画面から、以下の画像のように[ツール]→[フォームを管理]→[フォームを編集]でフォームを書き出した方が効率的だったりします。
また、先のフォームにすでに入力済みだった氏名データや、スプレッドシートに入力されてある氏名データは、≪疑似個人情報データ生成サービス≫より出力したものです。たとえ実在する氏名と合致していても、その人物とは全く関係ありませんのでご了承ください。
3-1. 各シートの説明
①ぜんぶシート
フォームに入力されたデータが蓄積されていきます。基本的にはタイムスタンプ順(フォームに入力された順)に一番下の行へデータが追加されていきます。
②全クラスシート
日付ごとに、その日の総連絡件数、学年ごとの件数、内容内訳が自動で表示されます。さらにこのシートでは、連絡内容検索、理由検索、内容と理由のクロス検索が利用できます。該当行には色がつきます。
③各クラスシート
A1セルに記載されたクラスごとに連絡が表示されます。
3-2. 使い方
①ぜんぶシートについて
・ここは基本的にほとんど編集しないシートです。
・フォームから送られてきた内容が蓄積されます。(弊職場での使用において、5000件溜まっても動作は軽快でした。)
②全クラスシートについて
・全てのクラスのその日の連絡がクラス順・出席番号順に表示されます。
・「TODAY」ボタンをクリックすると、C1セルがその日の日付になります。（このボタンにはGASでスクリプト設定しています。「承認」が必要となる場合がありますので、その際は「許可」をしてください。よくわからない場合は以下の記事を参照してください。）
≪【初心者向けGAS】スクリプト実行時の「承認」でびっくりしないために (高橋宣成さんのブログ「いつも隣にITのお仕事」より)≫
・日付が入っているC1セルはダブルクリックでカレンダーから日付選択が可能になっています。
・内容検索(F3セル)と理由検索(G3セル)において、セル内右端の下向き三角▼よりドロップダウンリストで項目が選択可能です。内容検索に合致する行は黄色、理由検索に合致する行は水色、どちらにも当てはまる場合は緑色に自動で着色されます。（F3とG3セルはBack spaceやDeleteキーで文字を消しても大丈夫です。H3セルは自動で表示されるのでいじらないでください。）
③各クラスシートについて
・このシートは最小限の機能にとどめて見やすさを重視してあります。
・「TODAY」ボタンや、ダブルクリックでカレンダーから選択可能なC1セルなどは同じです。
・出席番号順に表示されるようになっています。
・タイムスタンプの日付とC1の日付が一致していない場合がありますが、その連絡内容が(フォームのセクション2で)当日以外の日付で登録されたためです。各クラスシートにおいてはそちらの日付が優先され、表示されます。
・「TODAY」ボタンがたまにズレて読み込まれますが、機能に影響はありません。そのまま使ってください。
3-3. 仕組み
①ぜんぶシート
関数はA1やB1セルを見てもらえばわかると思いますが、GASで以下のようなコードを仕込み、5分に1度くらいの頻度のトリガーでB列の日付を各クラスシートで読み込めるような形式に変更させています。（ぜんぶシートのシート名を変更した際は下記コード内の“ぜんぶ”の部分を変更してください。）
function convertDateFormat() {
var ss=SpreadsheetApp.getActiveSpreadsheet(); //現在開いているブックを取得
var sh=ss.getSheetByName("ぜんぶ"); //「ぜんぶ」シートを取得
var forMat = 'yyyy-mm-dd'; //yyyy-MM-dd形式のカスタム日付に書き換えるよっていう宣言
sh.getRange("B2:B").setNumberFormat(forMat); //上のフォーマットに書き換え
}
②全クラスシート
各セルの関数と、[表示形式]→[条件付き書式]の合わせ技です。また、タイムスタンプのセルにQUERY関数を埋め込み、「ぜんぶ」シートのテーブルからデータを持ってきています。（間違ってA10セルから右下の部分を消しちゃった！という場合でも、A9セルにQUERY関数が入ってるので再表示されます。安心してください。逆に言うとA9セルは消さないでください。QUERY関数については後述。）
③各クラスシート
条件付き書式を設定していない以外は同上です。QUERY関数はA2セルに入っています。
3-4. できること・できないこと
①できること
・大体は上で説明した通りです。
・QUERY関数について
例えば、各クラスシートのA2セルには以下の画像のような関数が入っています。
=QUERY('ぜんぶ'!A:L, "select C,A,G,H,I,J,K,L where A like '33%' and B = date '"& TEXT(C1,"YYYY-MM-DD") &"' order by A asc",1)
というものですが、
QUERYの後の括弧内は次のような仕組みになっています。
・'ぜんぶ'!A:L →「ぜんぶ」シートのA～L列を参照するよ
・select C,A,G,H,I,J,K,L →その列からC,A,G,H,I,K,Lの順で表示するよ
・where A like '33%' and B = date '"& TEXT(C1,"YYYY-MM-DD") →「ぜんぶ」シートA列で「33」から始まるテキストと、「ぜんぶ」シートB列の日付と、そのクラスシートのC1セルの日付という2つの条件を照合して合致するものだけを抽出するよ
・ order by A asc",1 →「ぜんぶ」シートA列(4桁の学籍番号)を昇順にデータ並べ替えるよ
・フォームのところで“質問内容や項目数を変更した場合、スプレッドシート側で多少の手直しが必要となります”と書きましたが、具体的にはこのQUERY関数の話です。質問項目を変更するとスプレッドシートに影響が出るので、各シートのQUERY関数を調整する必要があります。
②できないこと
・今のところ、「全クラス」「各クラス」シートでは1日ごとの表示しかできません。一定期間の表示をするためには、日付表示をfrom-toで2つ用意し、その範囲でソートかけるようなやり方にするしかないかなあと思いながら、めんどくさくて実装していません。
・校務支援システム（各授業の出欠や単位履修の管理ができるようなもの）がある場合は、実質的な出欠はそちらの方に登録してもらった方が良いです。そこまでの連携や出欠管理のシステムは実装していません。一応、保護者からの欠席等連絡を受ける教員の手間を減らすためのものなので。
４．参考Webサイト等
全国の学校における働き方改革事例集 - https://www.mext.go.jp/content/20210330-mxt_kouhou01-100002245_1.pdf
いつも隣にITのお仕事 (高橋宣成さんのブログ) - https://tonari-it.com/
疑似個人情報データ生成サービス - https://hogehoge.tk/personal/
ランダムな日付・時間を生成 (phantom4) - https://blog.phantom4.org/archives/553
Googleフォームで出欠確認をして一覧をスプレッドシートにまとめる方法 (Apple technica) - https://technica-apple.com/6383/
Googleフォームで健康観察（検温入力付）を行い、スプレッドシートで集計する方法 (Qiita) - https://qiita.com/Ark2020/items/20faf40cf7db2b7e0fac
上記のWebサイト以外にもいろいろ調べながら作りましたが、主なものとして載せました。
注意事項
クラウドサービス上での個人情報の取り扱いについては、
各自治体や所属組織の指示に従いましょう。最悪死にます。
長くなりましたが、作ってみたファイルと見方・使い方の説明でした。正直全部を読むのは(今の時流的に)ツラいかと思いますが、少しでも目を通していただけていれば幸いです。
サポートしてくださると元気になります。お財布のご都合が良いときでよければお願いします。