Google スプレッドシートを利用した出席アプリケーション

作成経緯

週1で某大学の非常勤講師をさせて頂いてたりするのですが、2020年前期は COVID 問題でオンライン開講ということに。オンライン授業にあたり単純なところで出席どう管理するの的な話題が。確かに出席の管理はオンラインで且つ手作業となると地味にめんどくさいので、こんな時代だし絶対何かしらの方法はあると思われるので検討。

手短なところで Google Classroom にそもそもそういう機能があるとか zoom のチャットを工夫すれば対応出来そうな可能性は全然あると思いつつ、GAS(Google Apps Script) が手軽というお話をしてもらう機会があり 「GAS 触ってみたい！オシャンティなサーバーレスアプリを作成してみたい！！」と今更ながらになったので、Classroom や zoom は深堀せずに着手^^;（GAS の技術的なお話は近々また別の投稿で書こうと思います。今回は「誰でも出来る、Google スプレッドシートの出席アプリ」的なお話で。）

更新履歴

• 2020/05/04 遅刻判定処理追加
• 2020/05/03 入力者のユニーク判定処理誤り修正
  修正に伴いフォーマットのエクセル内、各授業シートの項目名を修正
• 2020/05/03 日時バッチ関数 execDailyBatch 追加
  関数の追加に伴いフォーマットのエクセル 基本設定 シートの項目追加

要件

実際に利用とするとなると組織的な運用承認が必要となるが、その辺はクリアした体で要件をざっくり定義。

• 非エンジニアでもアプリケーションを構築可能に
• 非エンジニアでも扱えるデータ形式に
• データは各組織が保持しサービスは保持しない（但し Google ドライブ上になる）
• サーバーを用意しなくても使えるように

という感じで、実際の導入は指定ファイルダウンロードして Google スプレッドシートのスクリプトにコピペすれば誰でも自分専用アプリとして利用できるところを到達点に。

仕様

• 出席時に自分の学籍番号を入力
• 出席は1日1回のみしか受け付けない
• 出席の一意性は Google アカウントのセッション情報を利用して保持
• 授業の1日での一意性は授業パスワードで保持
• 授業パスワードはタイマー処理で深夜に自動で変わる
• 授業パスワードは授業開始時に担当教員がアナウンス

実際の挙動

冒頭の画面キャプチャのような操作をおこなえばスプレッドシートに以下のように反映される。

導入方法

1. 必要ファイルのダウンロード

https://github.com/koronpo/attend-form の以下ボタンより「Dounload zip」を選択し解凍。

2. 新規スプレッドシート作成

Google ドライブで新規スプレッド作成。スプレッドシートの名前は任意で。

3. 雛形のエクセルファイルをインポート

作成したスプレッドシートのメニューより ファイル > インポート を選択。


この画面でダウンロードした xlsx_format フォルダ内の format.xlsx アップロードする。
インポートの種類は スプレッドシートを置換する を選択。

インポートが終わればシートが幾つか生成されている状態となる。

4. スクリプトの設定

スプレッドシートのメニューより ツール > スクリプトエディタ を選択。

スクリプトエディタ画面に切り替わるのでとりあえず適当な名前で保存。

5. 入力フォームに必要なファイルをコピーペーストで作成する

ダウンロードした src フォルダ内の以下を適当なテキストエディタで開いて内容をコピーペーストする。

1. css.html
2. index.html
3. js.html
4. main.js（main.tsでは無いので注意）

5-1. ダウンロードファイル css.html の反映

メニューより ファイル > New > html を選択し css というファイルを作成する。


作成時に自動生成されるコードは全て削除し、ダウンロードした css.html の内容を全てコピペする。

5-2. ダウンロードファイル index.html の反映

上記　5-1 の CSS と同様の手順で、メニューより ファイル > New > html を選択し index というファイルを作成、ダウンロードした index.html の内容を全てコピペする。

5-3. ダウンロードファイル js.html の反映

上記　5-1 の CSS と同様の手順で、メニューより ファイル > New > html を選択し js というファイルを作成、ダウンロードした js.html の内容を全てコピペする。

5-4. ダウンロードファイル main.js の反映

main.js（main.tsでは無いので注意）はスクリプトエディタ起動時に作成されている　コード.gs に main.js の内容を全てコピペする。

6. アプリケーションの公開

6-1. アプリケーションの公開

メニューより 公開 > ウェブアプリケーションとして導入 を選択。

6-2. 公開設定

公開設定は以下で

• Project version:
• Execute the app as: Me を選択
• Who has access to the app: 全ユーザー を選択
  （Anyone, even anonymous を選択すると回答者の Google アカウントは不要になるが、回答者の一意性は保てなくなる。）

公開すると WEB アプリの URL が表示されるのでコピーしてその URL にアクセス。

6-3. スクリプトからのアクセス権限付与

初回のみスクリプトからのアクセス確認が行われるので REVIEW PERMISSIONS を選択。

権限許可を行うアカウントを選択。

Google が作成したアプリでない警告が出るので左下の 詳細 をクリック。

左下の XXXに移動 を選択。

アクセスの許可を行う。

以上でアプリ画面が表示されるようになる。

7. 日次自動データ更新設定

日次処理にて自動で授業パスワードの書き換え設定を行います。
メニューより ファイル > 編集 > 現在のプロジェクトのトリガー を選択。

トリガー一覧の画面に遷移したら右下の「トリガーを追加」ボタンを押し、表示された画面で以下のように設定する。

これで毎日深夜に授業パスワードが更新されるようになる。

使い方

このアプリケーションはスプレッドシート内のシートのデータを利用しながら動作します。

基本設定シート

このシートの名称は変更不可。

• 授業として扱わないシート名（追加する場合は行末に追加してく）項目 >
  授業出席回答シートとして利用しないシート名を記入する。
• 格言の利用（利用する場合は する と入力）項目 >
  出席回答後に「格言」を表示するかを制御。
• パスワード日時変更（実行する場合は する と入力）項目 >
  「する」を入力していた場合は、日時バッチ処理にて授業パスワードが自動更新される
• パスワード文字列数 >
  タイマー処理時に授業パスワードを更新する処理が生成するパスワード文字列数を指定。
• 遅刻許容時間（分） >
  指定分以上遅れた場合に遅刻表示を行う。

名言シート

このシートの名称は変更不可。
基本設定シートにて「格言の利用（利用する場合は する と入力）」項目を「する」に設定した場合、回答後ここの名言がランダムで表示される。特に数の制限は無し。

授業名称1、授業名称2 シート

出席回答のフォーマットとなるシート。シート名が画面上にて授業名の選択プルダウンエリアに表示される。このシートを複製して授業を増やしていく。

• 開始時間、終了時間、授業パスワード項目 >
  該当する内容をそれぞれ入力。
• 授業参加生徒項目 >
  授業に参加する生徒の学籍番号と氏名を入力 >
  
• 出席項目 出席回答時に自動で入力される。

気になる点

負荷テスト的なことはやってないので、数百人が同時にアクセスしたらどうなるのかが気になる。

まとめ

サーバーレスでエクセルでのデータ取り扱いが出来るので上記の気になる点を除けば、若干導入ハードルが高いものの変なツールよりは扱いやすいのではと。Google App Script のプロジェクト内に zip ファイルポンとあげてスクリプト展開とか出来ればまだましなのだがやはりそんな都合良くはいかない。エンジニア向けには Clasp というコマンドベースでのファイル管理が出来るので、それを利用すればまだかなりマシになるので、その辺のお話はまた別の記事で。