見出し画像

QUERY 関数で日付を処理するとき

ともかつのノート

Google Workspace for Education の管理コンソールで、下図のように「レポート」→「アプリレポート」→「Classroom」などと選択すると、利用状況を確認できます。
このレポート表示では、過去 14日間に利用されたクラスの数が表示されていますが、なかなか細かな状況が掴めません。

管理コンソールで「レポート」　（１）

より細かな状況を確認するために、「レポート」→「ユーザーレポート」→「アプリの使用状況」を表示させ、ページ右上の「⇩（ダウンロード）」によって、Google スプレッドシートに出力することで、それぞれのユーザーがどのように利用しているのかを把握できます。
組織全体ではなく、うまく分類できれば、「学校」「学年」「学級」といった単位での状況が確認できるようになると考えたのです。

管理コンソールで「レポート」　（２）
レポートを「ダウンロード」

ダウンロードできるデータ

管理コンソールで、上記のように「レポート」→「ユーザーレポート」→「アプリの使用状況」でダウンロードすると、以下のような 32列のデータが出力されます。

  • ユーザー

  • Gmail の使用容量（MB） [YYYY-MM-DD GMT]

  • ドライブの使用容量（MB） [YYYY-MM-DD GMT]

  • フォトの使用容量（MB） [YYYY-MM-DD GMT]

  • 合計使用容量（MB） [YYYY-MM-DD GMT]

  • 使用容量（%） [YYYY-MM-DD GMT]

  • Classroom - 前回の使用

  • 作成されたクラス数 [YYYY-MM-DD GMT]

  • 投稿作成数 [YYYY-MM-DD GMT]

  • 合計メール件数 [YYYY-MM-DD GMT]

  • 送信済みメール数 [YYYY-MM-DD GMT]

  • 受信済みメール数 [YYYY-MM-DD GMT]

  • Gmail（IMAP）- 最終使用時刻

  • Gmail（POP）- 最終使用時刻

  • Gmail（ウェブ）- 最終使用時刻

  • 編集されたファイルの数 [YYYY-MM-DD GMT]

  • 閲覧されたファイルの数 [YYYY-MM-DD GMT]

  • ドライブ - 最終オンライン時刻 [YYYY-MM-DD GMT]

  • 追加されたファイルの数 [YYYY-MM-DD GMT]

  • 追加されたその他の種類の数 [YYYY-MM-DD GMT]

  • 追加された Google ドキュメントの数 [YYYY-MM-DD GMT]

  • 追加された Google スプレッドシートの数 [YYYY-MM-DD GMT]

  • 追加された Google スライドの数 [YYYY-MM-DD GMT]

  • 追加された Google フォームの数 [YYYY-MM-DD GMT]

  • 追加された Google 図形描画の数 [YYYY-MM-DD GMT]

  • 投稿数 [YYYY-MM-DD GMT]

  • +1 の数 [YYYY-MM-DD GMT]

  • 受け取った +1 の数 [YYYY-MM-DD GMT]

  • コメント数 [YYYY-MM-DD GMT]

  • 受け取ったコメントの数 [YYYY-MM-DD GMT]

  • 再共有数 [YYYY-MM-DD GMT]

  • 再共有された数 [YYYY-MM-DD GMT]

実際に出力されたスプレッドシートは、下図のような感じになります。組織内に作成されているユーザー数にもよるのでしょうが大量のデータから目的のデータを抽出することになるので、QUERY 関数を用いて抽出します。

出力されたスプレッドシートの例

はまったこと

管理コンソールで「レポート」→「アプリレポート」→「Classroom」では「過去 14日間」と期間が固定だったので、QUERY 関数を使って、任意の期間内に Classroom を利用したユーザーを確認しようとしました。

  • 希望する条件で抽出しようとするのですが、以下 URL で説明されているような datetime などを指定することで、指定された日付以降に利用されたユーザーを抽出します。

「これで、うまく処理できる！」と思ったら、次の点ではまりました…

（１）利用していないユーザーは「なし」

Google 側にしてみると親切心なのかもしれませんが、直近に当該サービスを利用した日時が格納される欄に、当該サービスを利用していない場合には「なし」が格納されています。
このような日付ではないデータが含まれていることで、うまく処理できていないようでした… 😖

  • 「なし」を空白（空欄）に置換

することで対応しました。

（２）日付データの形式が ISO 8601形式

前項のように日時が格納される場合、以下のような ISO 8601 形式で記録されています。

2022-12-07T16:45:48-08:00

この ISO 8601 形式については、詳細は以下 URL などを参照していただくこととして、ざっくりと表現すると「最近はやりの日付データの形式」という感じです。

人間が見る分には「日付と時間を ’T’ でつないで、後ろにタイムゾーンがついてるのね」という感じで理解できるのですが、QUERY 関数は理解してくれませんでした。

  • ’T’ を空白（' '）に置換

  • タイムゾーンを置換で削除

することで対応しました。

まとめ

「GAS」＋「Admin SDK」といった組み合わせで、データを抽出することも考えましたが、「スプレッドシート書き出し」＋「QUERY 関数」の方が手軽そうだったので試してみました。
管理コンソールで出力されたスプレッドシートは、上記のような点を注意して、前処理してやれば、QUERY 関数でうまく処理できます！

わたしの知識不足なだけで、こんな前処理をしなくても、QUERY 関数におまじないをついかしてやるだけで対応できるのかもしれませんが、ひとまずこんな感じで対応できました。
もっと、手軽な方法があればコメントください。

うまく処理できた結果、悲しい利用状況がわかってしまったのは内緒… 😖

いいなと思ったら応援しよう！