QUERY関数で指定日時以降のデータを抽出する
Facebook の公開グループ「Google for Education 研究グループ」に以下のような質問が寄せられていました。質問の意図とすれば、「GAS でどのようにしたらよいでしょう？」というものですが、
2学期から欠席届をGoogleフォームでしてもらうことになり、電話連絡がかなり減ったのは良かったのですが、毎日の結果を保存しておけと管理職から命じられました。
やることは
Googleフォームの回答からスプレッドシートを開く
↓
別名でコピーしてGoogleドライブの共有ドライブに保存
↓
元のスプレッドシートの内容を削除
↓
Googleフォームの回答をクリア
という簡単な事なのですが、毎日となるとそれなりに面倒ですし、何より恥ずかしながらよく忘れるのです。
これらを自動化できる(できそうな)Google app Scriptなるものがあるらしいことまでは分かったのですが、ググっても初心者ゆえにイマイチよく分からないのです。
お知恵をお借りしたいのは、
そもそもこれらの作業の自動化は可能なのかどうか。
さして、参考になりそうなサイトをご存知ではないでしょうか？
GAS による解決方法
けいすけさんが
簡単なプログラムだったので、先述されるコメントを参考に作って解説しておきました。
基本は、私も削除の必要はない派ですが、運用上、今はこれが必要だと思いますので！！
よければ使ってください！フォームで行うまでのご苦労、お察しします！！
GAS - フォームの回答前削除
https://youtu.be/drw8pmXD8Ug
GAS - Google スプレッドシート のコピーと貼り付けの自動化
https://youtu.be/RT8TI3chpWg
という感じで、解決方法を提示されています。
こんな短時間でプログラムを作ったうえで、解説動画まで作るのはステキ！
QUERY 関数による解決方法
ということで、GAS による解決方法を複数人で焼き直しても有用ではないので、以前の記事でもネタにした Google スプレッドシートの QUERY 関数での解決方法を考えてみました。
（１）サンプルデータ
まず、下図のようなサンプルデータを用意してみました。サンプル数は 25件と少ない感じですが、Google フォームで集めた健康観察のデータをイメージして作成してみました。
列 A「タイムスタンプ」のデータは、手持ちのデータからコピペした生データとなっています。
D 列の名前については、以下の「個人情報おテストジェネレーター」によって生成したものなので、仮に実在する個人と一致するものだったとしても、意図したものではありません。
（２）作成したスプレッドシート
今回作成したのは、下図のようなスプレッドシートです。右上の黄色のマーカー部分で、QUERY 関数をどのように使うかを設定すれば、セル B1 に QUERY 関数に指定しなければならないクエリが生成され、セル A7 に設定された QUERY 関数は前述のサンプルデータをそのクエリによって処理します。
以下の URL にアクセスすると、自身の Google ドライブにコピーできるので実際に試してみてください。
https://docs.google.com/spreadsheets/d/1Aaz44FFOuS5rfHrAf9POyb8Vcx5hV_0I0AsuroV3c1U/copy
（３）クエリのポイント
どの日付（期間）のデータを処理の対象とするかを、スプレッドシート上で簡単に指定できるようにしています。クエリに指定する日付は / ではなく - で区切らなければならないこともあって、年月日をそれぞれ入力して指定します。
普段は、「指定日」のチェックをOFF にしておけば、当日のデータが対象となります。
ここで単純に日付だけではなく、時間も対象としています。健康観察ではなく欠席連絡として利用する場合、日付の変わる前に入力されてしまうことも考えられ、そのデータが前日分として扱われないように「前日の何時からを対象とするか？」と指定できるようにしてあります。
右側のオレンジ色のセルには、前日 17時の日時が表示されており、この日時以降のデータを抽出するため、4/26 を指定しても、4/25 17:34 のデータが抽出されます。
また、使い勝手などの観点から、日付を「昇順」か「降順」のどちらで表示するのかも選択可能です。
学校全体や学年で共通のフォームで回答を集めていた場合、自身の担当するクラスのデータだけを確認したい、発熱者がいるかだけを確認したい、といった絞り込みを行いたい場合にも、チェックボックスを操作するだけで簡単に絞り込みが行えます。
QUERY 関数は元データを参照しているだけで、元データには手を加えていません。複数人で同時に上記の絞り込みを行うためのチェックボックスを操作した場合、表示する内容の奪い合いが発生してしまいますが、それぞれの担当者ごとにシート「QUERY」をコピーして、自身のよく使う絞り込みの状態にしておけば奪い合いも発生しません。
（４）自身のデータで使うときは？
今回のスプレッドシートは、用意したサンプルデータを対象に行っています。GAS のプログラムなどを利用したものではなく、シート「QUERY」が、シート外のデータを参照しているのは、セル A7 の QUERY 関数だけです。
列 R のオレンジ色のセルや、セル B1 には関数や数式が設定してあります。
セル J5、J6 には「セルの入力規則」を設定してあります。
セル A7 の QUERY 関数の第一引数に処理対象となるデータ範囲を指定すれば、そのデータ範囲に対して処理を行います。
サンプルデータでは絞り込みの条件として、「学年」「組」「体温」を指定していますが、これらが指定したデータ範囲の何行目にあるのかを、列 K で指定しています。図のように C、D、F と入力してある部分が、それに当たります。
最後に
QUERY 関数は、前述したように指定されたデータ範囲を参照して、指定したデータを抽出するために使います。そのため、データを更新するなどの目的には使えません。
GAS と QUERY 関数などの関数をうまく使い分けて、業務が楽になればいいなと思います。
ちなみに…
元の質問のように、毎日のようにスプレッドシートからデータを削除する必要はありませんが、↓ の URL で案内されているように、Google スプレッドシートの扱えるセル数の上限が規定されているのも事実なので、この制限を超えないようにしなければならないことも事実。
この制限を回避するためには、別のスプレッドシート（ファイル）に退避させて元のシートから削除しなければならないので注意が必要です。
また、
今回の QUERY 関数に与えるクエリについては、以下のページを参考にさせていただきました。細かなクエリの記述やカスタマイズについては、そちらをご覧ください。
クエリの基本的な記述方法については、こちらを参考に…
今回の日時についての抽出は、こちらのページを参考にしています。
この記事が気に入ったらサポートをしてみませんか？