ちょっと長い前書き

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だけではなく、ほかの趣味やお仕事でも利用できます。
(実際にこれで得た知識はお仕事で役に立ちましたｗ)

もし、こんな改造した！とか、こここうするといい！などあれば、リアキャプするか、コメント欄にお願いします！