WoT CWE管理シート自動化の旅々　3.『芋のように冗長な戦闘』/『マッチング情報取得の自動化』※修正版

※タイトルの本題は後者です。

2020/01/15 改訂: 今まで陣地1スタートになった場合、正しい敵クランが抽出できていなかったのを出来るように内容を書き換えました(シート「スクレイピング4」に関する内容)。また、マッチング情報が取得できないことがたまに存在したので、確実に取得できるように内容を修正しました(シート「スクレイピング2」に関する内容)。2話の分とともに変更してください。

ts-hartmann.hatenablog.com

これの続き

お約束
やりたいこと
マッチング情報取得までの手順
- 手順1. GAS側の記述
- 手順2. シート側の記述
最後に

お約束

　美しい表には棘がある(使いづらさ的な意味)、と謂います。美しさは時として、管理するものに牙を剥くこともあるのです……
　ところで話は変わりますが、色とりどりの関数に負けず劣らず、美しく書いているコードは誰のでしょう？そう、私のです！　

　お約束なのでお許しください。実際は汚く冗長なコードです(例外あり)。

やりたいこと

　戦闘予定は組んだけど、戦闘相手事前に知っておきたい。でもGMを確認するのは面倒だから見たくない。せっかくシートで戦闘予定表使ってるんだし、シートに戦闘相手とかの情報を表示させられないかなあ…願望を言えばこんな形です。

　下の画像で貼ってある書式の戦闘予定表を使っていることが大前提です。使っていない場合は、書式を画像のように直すか、GASのコード自体を自分で書き直してください。

f:id:TS_hartmann:20201210210809p:plain — 一応以前公開しているので良かったら落としてみてください。この戦闘予定表の例を使います。

f:id:TS_hartmann:20201213072417p:plain — こんな感じでシートから直接マッチング情報とか確認できたら便利ですよねえ…

　絶対この話、この記事だけじゃ終わらない。

マッチング情報取得までの手順

手順1. GAS側の記述

function Scraping2() {

  //※WOT APIを使用※プロビトーナメント情報
 
  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName = "スクレイピング2(戦)";
  var cell1 = spreadSheet.getSheetByName(sheetName).getRange("B3:B100").getValues();
  var result1 = cell1.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr1 = Array.prototype.concat.apply([],result1);//2次元配列を1次元配列にする
  var cell2 = spreadSheet.getSheetByName(sheetName).getRange("D3:D100").getValues();
  var result2 = cell2.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr2 = Array.prototype.concat.apply([],result2);//2次元配列を1次元配列にする
  var count = result2.length; //配列変数result2の配列数を調べる
  
 
  　//URL、データ取得のループ処理
  　var ary = [];
    for ( var i = 0; i <= count; i++) {
    
  　var URL = 'https://api.worldoftanks.asia/wot/globalmap/provinces/?application_id=#APPLICATIONID&front_id='+Arr1[i]+'&province_id='+Arr2[i]+'&language=en';//Grobal Map for world of Tanks　プロヴィンスのトナメ情報
                
  　var response = UrlFetchApp.fetch(URL);//スクレイピング
    var response2 = response.toString();
    
    ary.push(response2)
    }
  
 
  //logファイルにログを書き出す 

  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName2 = "スクレイピング3(戦)";
  　
  
   
  var count2 = ary.length; //配列変数aryの配列数を調べる
  
  Logger.log(ary);//デバック用
  
  spreadSheet.getSheetByName(sheetName2).getRange("A1:D100").clearContent();//セル内のコンテンツ削除※値や数式
  
  for (var k = 0; k < count2; k++) {
  spreadSheet.getSheetByName(sheetName2).getRange(k + 1, 1).setValue(ary[k]);　//配列変数aryのi+1番目に格納されている値をセルAi+1に入力
  }
  
  Scraping3(); 
  
}

　スプレッドシートのidには使用しているスプレッドシートのidを、#MYAPPLICATIONにはapplication_idを代入して置き換えてください。　

手順2. シート側の記述

f:id:TS_hartmann:20201210214621p:plain — スクレイピング2(戦)はこんなシートです

　とりあえず、「スクレイピング2(戦)」と「スクレイピング3(戦)」のシートを作成してください。　それぞれのシートの役割はこんな感じです。

「スクレイピング2(戦)」: 戦闘予定表に書いてあるプロヴィンス名のプロヴィンスidを表示する。
「スクレイピング3(戦)」: WargamingAPIからプロヴィンスのマッチング情報をスクレイピングした内容が出力されてあります。

　スクレイピング2(戦)には

B2セルに=iferror(offset('Province参照'!$A$2,match($C3,'Province参照'!$C$2:$C$1000,0)-1,1))
C2セルに='戦闘予定(CWE用)'!H11
D2セルに=IFERROR(VLOOKUP($C3,'Province参照'!$C$2:$E$1000,3,FALSE))

と書いてあります。貼り付けた後に下にオートフィルしてください(100行くらい)。 ※追記:2021/01/15: D2セルに入る関数を変更しました。
　スクレイピング3(戦)は何も書かなくていいです。

f:id:TS_hartmann:20201210220318p:plain — 上のコードを実行するとスクレイピング3(戦)にこのような感じでデータが出てきます。

と言っても、この記事を書いているときにはGM凍結中なので何も表示がありませんが、本来は開始陣地・開始時間・対戦相手のクランidがデータに格納されています。

　ここまで出来たらやることはデータの切り抜きだけです。データの切り抜き用に「スクレイピング4(戦)」というシートを作ります。

　スクレイピング4(戦)には

A2セルに='スクレイピング2(戦)'!C3
B2セルに=IFERROR(REGEXEXTRACT(SUBSTITUTE(REGEXEXTRACT('スクレイピング3(戦)'!$A2,"(clan.+?)+?clan_id"&CHAR(34)&":"&$N$2&""),CHAR(34),""),"(.+?):{loose_elo_delta"),IF(NOT(D2=""),"seed","--"))
C2セルに=IFERROR(SUBSTITUTE(REGEXEXTRACT('スクレイピング3(戦)'!$A1,""&CHAR(34)&"start_at"&CHAR(34)&":(.+?),"),CHAR(34),""))
D2セルに=IF(C2="","--",TEXT(MROUND((TEXT(TEXT(SUBSTITUTE(C2,CONCATENATE(REGEXEXTRACT(C2,"(.+?)T"),"T"),""),"[hh]:mm")+'Province参照'!$F$1,"[hh]:mm")),"0:15"),"[hh]:mm"))
E2セルに=iferror(address(match(A2,'戦闘予定(CWE用)'!$H$1:$H$109,0),match(D2,'戦闘予定(CWE用)'!$A$9:$AZ$9,0),4),"--")
F2セルに=IFERROR(IF(B2="clan_a",SUBSTITUTE(REGEXEXTRACT(SUBSTITUTE($P2,CHAR(34),""),"clan_b:{loose_elo_delta:.+?,clan_id:(.+?),"),CHAR(34),"--"),IF(B2="clan_b",SUBSTITUTE(REGEXEXTRACT('スクレイピング3(戦)'!$A1,"clan_b.+?clan_id"&CHAR(34)&":"&$R$2&".+?clan_a.+?clan_id.:(.+?),"),CHAR(34),"--"),"--")))
H2セルに=IFERROR(IF(B2="seed","404 not found",REGEXEXTRACT(SUBSTITUTE(G2,CHAR(34),""),"tag:(.+)")))
I2セルに=IF(B2="clan_a","side 1 start",IF(B2="clan_b","side 2 start",IF(AND(NOT(E2=""),H2="404 not found"),"seed",IF(H2="","","?"))))
K2セルに=IFERROR(VLOOKUP($A2,'Province参照'!$C:$D,2,FALSE))
L2セルに=IFERROR(VLOOKUP(VLOOKUP($A2,{'Province参照'!C:C,'Province参照'!B:B},2,FALSE),'Province参照'!$Q:$R,2,FALSE))
M2セルに=IFERROR(REGEXEXTRACT('戦闘予定(CWE用)'!I11,"$(.+?)$"))
N2 セルに=IFERROR(IF(AND(TEXT(D2-$R$1,"[mm]")<=TEXT("0:15","[mm]"),TEXT(D2-$R$1,"[mm]")>TEXT("0:00","[mm]")),"○","×"),"×")
P2セルに=IFERROR(RIGHT(SUBSTITUTE('スクレイピング3(戦)'!A1,CHAR(34),""),LEN(SUBSTITUTE('スクレイピング3(戦)'!A1,CHAR(34),""))-FIND($R$2,SUBSTITUTE('スクレイピング3(戦)'!A1,CHAR(34),""))+170))

と、それぞれ入力してください。貼り付けた後にオートフィルしてください(100行くらい)。 ※2021/01/15追記: 今後の話のために、L~P列の中に書く関数を記述しました。特にP列は必須です。
　また、N2セルに、自クランのクランidを書いてください。

f:id:TS_hartmann:20210115115747p:plain — GMが開いてるときはclanidとかも抜き出せているはずです。

　はい、ここまで来たらマッチング情報取得のスクレイピングは完了でございます。GMは15分ずれでの戦闘があるので15分おきのトリガーを設定したりするとちゃんと毎試合分取れます。

　ただ、このままだと敵クラン名が分からないし、情報が更新される度にデータが上書きされてしまう……データの保存のために一工夫するのですが、それはまた次回ということで…

最後に

　「実に綺麗な表ですね……」「？」

　私はその時思い出したのです。この自動化の為の作業量を。勝手にやった自動化は、他のシート管理官を却って絶望させてしまったのです。そして管理官は……
　人の為にと思ってした自動化が正しいとは限らない。というシート管理できる人が大変に減った話だったのです。あれは。
　余計な自動化は、時には作業量を増やすことにもなるのです。あれから他のシート管理官がどうなったかは私は知りません。いえ、知りたくもありません。

2021/01/12追記: 「スクレイピング4(戦)」のデータの切り抜きが上手くいっていない(1スタートを引いたときに間違った敵クラン名が表示される)ことが分かったので、後日内容を修正します。

ts-hartmann.hatenablog.com 続き