ちょっと長い前書き
Ingressで遊んでいるAgentの皆さんこんにちは。
Agentの人が読んでるとはかぎらないのですがｗ
旧Ingressのスキャナ「Scanner[REDACTED]」が先日終わっちゃいましたね。
これからはIngress Prime、そう、新しいスキャナでやっていくことになります。
で、今まではStatsの画像を共有できるような仕組みがあったわけですが、PrimeではStatsの情報がタブ区切りのテキストで共有することが可能になりました。
こんな感じでね
Time Span Agent Name Agent Faction Date (yyyy-mm-dd) Time (hh:mm:ss) Lifetime AP Current AP Unique Portals Visited Portals Discovered Seer Points XM Collected OPR Agreements Distance Walked Resonators Deployed Links Created Control Fields Created Mind Units Captured Longest Link Ever Created Largest Control Field XM Recharged Portals Captured Unique Portals Captured Mods Deployed Resonators Destroyed Portals Neutralized Enemy Links Destroyed Enemy Fields Destroyed Max Time Portal Held Max Time Link Maintained Max Link Length x Days Max Time Field Held Largest Field MUs x Days Unique Missions Completed Hacks Glyph Hack Points Longest Hacking Streak Mission Day(s) Attended NL-1331 Meetup(s) Attended First Saturday Events Recursions
全期間 krohigewagma Resistance 2019-09-29 10:33:06 64418949 12741333 4951 64 41 248859038 421 2950 82168 9824 4792 299711 51 7298 89807328 8405 1828 11067 125916 18454 25603 14107 462 190 3718 188 1376870 134 56705 109906 506 2 1 4 1
となると、ファーストサタデーなんかで集計を担当していると、これを使って楽したくなるわけです。
このテキスト、Agentの実績によって、項目の有無が変わってきます。
例えばファーストサタデーに参加している人、していない人では、「First Saturday Events」があったりなかったりします。
これによって、実は中途半端なところで項目が抜けるので、単純にタブ区切りの文字を分割して、n番目がxxxみたいな処理はできません。
また、実績が変なところに追加されるとそれはそれで大変になります。
ということで、そういったことを踏まえて、スプレッドシートでどう扱うのか、説明していきます。
ポイントとしては以下の3点になります。
- 改行でセパレート
- タブでセパレート
- 名前で参照して値を抽出する
また、以下のスプレッドシートの構成が前提になります
- 実績登録用フォームの内容の出力されるスプレッドシート(以後、実績登録スプレッドとする)
- 集計スプレッドシート
さて、手順としては以下の通りです。
- 実績登録スプレッドシートから集計スプレッドシートへ転記する
- 転記された内容を加工
- 必要な情報を抽出
0.はじめに
さて、まだ作りかけですが共有しておきますね。
https://drive.google.com/open?id=1mPRAVgTmljIcOpZaDW4YOVVfGm3fLUOC
実物見ながら読んでもらうとわかりやすいと思います。
実績を登録してみたいという方は以下のフォームから登録してみてください。
(改修中なので画像とかは大目に見てくださいｗ)
ちなみに、これ、登録するとURL共有になっているので、だれでもみえちゃいます。
見られたくない人は、コピーするかなんかして、試してください。
https://forms.gle/nT352CrQNDz7cyFE9
1.実績登録スプレッドシートから集計スプレッドシートへ転記する
スプレッドを参照するためには、
- スプレッドのID
- シートの名前
の2つの情報が必要になります。
スプレッドのIDは対象のスプレッドシートのURLを見るとわかります。
スプレッドシートのURLは以下のような形式になっています。
で、「IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII」のところがスプレッドシートのIDになります。
なので、この部分を覚えておきましょう。
次に、取り込みになりますが、importrange関数を使います。
これは、外部のスプレッドシートの内容を範囲指定してインポートするような関数です。
=importrange(B1,"フォームの回答 1!A1:N300")
この例の場合、B1セルに書かれたIDのスプレッドシートの「フォームの回答 1」シートのA1からN300の内容をインポートする動作になります。
アンケートフォームから入力された内容をスプレッドシートに記録するようにすると、シート名は「フォームの回答 1」になります。
(ほかに名前になるケースがもしあれば誰か教えて！)
これで第一段階終了です。
この実装は「FS参加者確認と実績まとめ」スプレッドシートの「BC実績」シートで行っています。
2.転記された内容を加工
さて、ここからが結構面倒くさいです。
Statsのテキストを2行に展開します。
行の展開は、まず改行で1行目と2行目と分けて処理します。
その為に、FIND関数で、開業の位置を探します。
改行はchar(10)で指定しています。
そして、MID関数で1文字目から改行の位置までを切り出します。
その結果をSPLIT関数で水平タブで分割します。
これによって、statsのヘッダ行をセルに展開します。
この処理が↓になります。
=SPLIT(MID(J4,1,FIND(char(10),J4)),char(9))
次に、statusのデータを展開します。
やってることはヘッダの展開と同じですが。MIDで抜き出すのが、改行の位置+1の位置から最後までを抜き出すところが違うだけです。
=SPLIT(MID(J4,FIND(char(10),J4)+1,LEN(J4)),char(9))
これで、情報の展開ができます。
ここまでできれば、あとは必要な情報を参照するだけです。
この実装は「FS参加者確認と実績まとめ」スプレッドシートの「展開」シートで行っています。
3.必要な情報を抽出
「FS参加者確認と実績まとめ」スプレッドシートの「Forms」シートを参照してもらうのが早いのですが、QUERY関数で「展開」シートからSQLでDBから情報を撮ってくるかの如く、情報を取得しています。
QUERY関数ホント便利。Excelにも実装してほしい！
そして、M列以降は2行目に書かれた文字と展開シートに展開したヘッダのマッチングでStatsの情報を取ってきます。
ここ、重要なところです。
こうしておかないと、NIAがヘッダの文字替えたりしたときに改修が大変になります。
あとはここに展開した情報をほかのシートでもやっているように、ランキングに利用したり、異常値の検出に使ったり、FevGames提出用にまとめたりすればいいんです。
最後に
GAS(Google Apps Script)を使って、FSなんかで集計に使っている人もいるんじゃないかと思います。
けれど、スプレッドだけでも出来ちゃうのでやっちゃいました。
この仕組みを理解すると、Googleスプレッドシートの便利な関数が覚えられます。
Ingressだけではなく、ほかの趣味やお仕事でも利用できます。
(実際にこれで得た知識はお仕事で役に立ちましたｗ)
もし、こんな改造した！とか、こここうするといい！などあれば、リアキャプするか、コメント欄にお願いします！