hartmannのゲームあれこれ

適当にやってるゲームのあれこれを偶に書きます

WoT CWE管理シート自動化の旅々　7.『戦闘が刻む倍率』/『現在時刻の表示』

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々

　まだCWE前だけど、書ける内容があったのでとりあえず3話分書こうと思っています。 　大体のCWEルールは理解しているという前提で書きます。

ts-hartmann.hatenablog.com これの続き

はじめに

こっち側の役人「味気ないとは思わんかね？GM殿」
GM「味気ないとはどういうことですか？」
こっち側の役人「表のこっち側とあっち側は本当に仲が悪いし、顔も合わせたくない。だが、この表はあまりに味気ない」
GM「はぁ…」
こっち側の役人「我々は向こう側に負けたくないのだよ。この味気ない表もこっち側の方が優れていると自慢したいのだが、何かいい案はないものかねGM殿」
GM「んー？まあないこともないわ。お役人さん、キーボードを持ってない？」
こっち側の役人「ん！？ああ…あるが… それでどうするつもりだね」
GM「こうするのよ」

やりたいこと

　戦闘予定表の@とか？とか(それぞれの戦闘を表すマーク)が何倍戦闘を表すのかシートで見れるようにしたい。そんなの「各自で判断しろよ」とかいう突っ込みはなしですよ。それを言ったら全ての自動化に刺さってしまいますので。

f:id:TS_hartmann:20201218132728p:plain
こんな感じ

　戦闘倍率の表示自体はスプレッドシートの関数のみで動いているため、先に述べている「この連載におけるシート自動化の定義」には当てはまりません。それなのになぜこの内容を入れるかと言うと、この次の記事で書くコードに関わってくるのです。

　正直この処理もGASで出来そうですが、プログラミングを学び始める前にスプレッドシート内の関数で全て終わらせてしまっていたのです(情報を保存する必要もないし)。

戦闘倍率を表示させる手順

手順1. GAS側の記述

　今回はスプレッドシート内の関数のみで仕上げるのでGAS側の記述は必要ありません。

手順2. スプレッドシート側の記述

　シートの内容が欲張りすぎる……というか私が作ったシートな筈なのにどこがどうなっているのかわかりません。終わりです。いいや、やっちゃえ○産！！

docs.google.com

　2021/01/03追記:　順次、この連載で紹介したシートを、ここに纏めておくつもりです(そのままでは使えませんので各自で改変よろしくお願いいたします)。

　これの「名声獲得倍率」というシートを前回までのスプレッドシートにコピーしてください(説明放棄)。参照しているシートがないというエラーは、参照シートを作っても関数を再計算させないとエラーが直りません。

　一応説明すると、表の右側に(最初の画像でx5.5とか)書いてある場所は戦線名と戦闘形式を判定して、何倍戦闘か表示させるようになっています。
　5行目～7行目とA列～B列は、時間判別で今戦闘を行っているプロヴィンスの行・時間の列に色付けを行うものです。後述する現在時間を表示するスクリプトを仕込んでおかないと動作しません。

　次の記事では戦闘予定表に@や?を書く作業を自動化していきますので、このシートの「最大参加クラン数」の列が次回必要になるのです。

現在時刻の表示

　さっき述べた現在時間を表示するスクリプトを書いていきます。スプレッドシート内にはnow関数があってこの関数で現在時間を表示させることが出来ますが、now関数を使うとスプレッドシートが非常に重たくなるのです。なので動作を早くするために、ここはGAS使って書いてしまおうというわけです。
　他の所でも書かれているコードですが、一応ここでも貼っておきます。

手順1. GAS側の記述
function getDateTime() {
  
//現在時刻を取得
  var now = new Date();
  
//「スクリプトデータ」内のA2セルに時刻を入力
  var ssA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('スクリプトデータ');
  ssA.getRange('A2').setValue(now);
  
//書式設定
  ssA.getRange("A2").setNumberFormat("mm/dd hh:mm");
  
}

　トリガーを1分おきにしてこのコードを動かせばOKです。

手順2. スプレッドシート側の記述

　白紙のシート「スクリプトデータ」を作るだけです。A2セルに現在時刻が表示されるので、A1セルにわかりやすいような説明を書いておくと良いと思います。

いつもの

元こっち側の役人「みんな表の向こう側よりも自分たちが優れていると思い込んでいたが……」
元あっち側の役人「戦闘が刻んだ5倍の名声倍率によって、それを否定されたということだ」
元こっち側の役人「結局、このシートは必要不可欠だったんだよ。何から何まで同じだったのだ」
元あっち側の役人「これからは1つのシートとして平穏にやっていくだろう」
私「はあ」
元こっち側の役人「じゃあGM殿失礼するよ」
私「あのっ、統一する前のシートってどっちの方に倍率が多く刻まれていたんですか？(目をキラキラさせながら)」
元こっち側の役人「決まっているだろう」
元あっち側の役人「どっちも同じだよ」
私「同じ……？」
元こっち側の役人「もともと1つのシートだし」
元あっち側の役人「どっちも表の向こうに負けまいと思って発展してきたからなあ」
私「どうして誰も相手側が同じような発展を遂げていることに気づかなかったのですか？」
元あっち側の役人「自分たちが優れていると思い込むため、表の向こうをみたくなかったんだよ。なあ」
元こっち側の役人「お互いにな」

　きっとこれでよかったのですねえ……
　表は一般兵や指揮官のためだけにあるのではなく、GMをやる人たちのものなのですから。まっ、1倍戦闘を見られなかったのは残念ですけど……
　GMは将来シートが一つになると思ってこれを作ったのでしょうか？いえ、まさかですね。

続き

ts-hartmann.hatenablog.com

WoT CWE管理シート自動化の旅々　6.『正直者のシート』

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々

ts-hartmann.hatenablog.com

これの続き

はじめに

私「正直者のシート…ですか……見たところ普通のシートですが」
私「とりあえず嘘が書けないかどうか試してみましょう」
私「この表は美しくありませんと書いてみましょう」
私「ｺﾞﾎﾝ(咳払い)」
私「この表は美しくあり…あｒ…あ…この表はとても美しい！」
私「おーっ！手が勝手に動いて真実を！嘘が書けないというのは本当のことだったのですね……」

やりたいこと

　CWE中、一々Fame Lookup開いて各クラメンのpoint確認するのめんどい……そうだ！だったらボタン押せば勝手にシートに書いてくれるようにしちゃえばいいじゃない！！ ……との考えです。

　元々、私のクランでも戦闘が建っているときにpointを確認するのは困難であったので1日1回、一日の戦闘がすべて終わった夜中2時以降に確認するだけ……っていうのが常でした。
　それが、シートを見るだけで確認できるようになれば便利ですよねえ……

Fame Lookupをスクレイピングする手順

手順1. GAS側の記述
function Scraping8() {

  //FameLookupを取得

  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName = "名声の街道";
  var cell = spreadSheet.getSheetByName(sheetName).getRange("L4").getValue();
  

  
   //JavaScriptを使ったデータのスクレイピング※phantomjscloudを使用
 
  const URL = cell; //Fame Lookup(TANOC)
  var key = 'phantomjscloudのApikey';
  
  
  var option = 
      {url:URL,
       renderType:"HTML",
       outputAsJson:true};
  var payload = JSON.stringify(option);
  payload = encodeURIComponent(payload);
  var url = "https://phantomjscloud.com/api/browser/v2/"+ key +"/?request=" + payload;                
  var response = UrlFetchApp.fetch(url);
 
  var json = JSON.parse(response.getContentText()); 
  var source = json["content"]["data"];
  
  
  
  var str = source;
  var result = str.substr(16000);//n文字以降の文字のみ表示
  

   //var ary2 = data.toString();
   var rep = result.replace(/"/g,"");
   var mat = rep.split(/<td class=text-muted row-counter-target>/); 
   var count3 = mat.length; //配列変数aryの配列数を調べる
   Logger.log(mat);
   
   var sheetName2 = "Fame Lookup";
   
   spreadSheet.getSheetByName(sheetName2).getRange("A1:A120").clearContent();//セル内のコンテンツ削除※値や数式
   
   for (var i = 0; i < count3; i++) {
   spreadSheet.getSheetByName(sheetName2).getRange(i + 1, 1).setValue(mat[i]);　//配列変数aryのi+1番目に格納されている値をセルAi+1に入力
   }
}

　今回スクレイピングする対象は「Fame Lookup」のページなのですが、このページはjavascriptで書かれています。そのために1話で紹介したphantomjscloudを使ってスクレイピングをするのです。

　使い方が分からないという方は1話を見直してみてください。使い方を紹介している他の人のブログが貼ってあるので。 ts-hartmann.hatenablog.com

　使い方わかってるよーという方は続きをどうぞ……
　スプレッドシートのidには使用しているスプレッドシートのidを、phantomjscloudのApikeyには自分のphantomjscloudアカウントに表示されているApikeyを書いてください。

手順2. スプレッドシート側の記述

　まず、「Fame Lookup」「名声の街道」の2つのシートを用意します。それぞれのシートの役割はこんな感じです。

  1. 「Fame Lookup」: Fame Lookupからデータをスクレイピングした内容が出力されています。
  2. 「名声の街道」: 「Fame Lookup」に書かれてある内容を見やすい形に加工。


　そして、「名声の街道」のそれぞれのセルに次のように記入します。

  1. B3セル:=IFERROR(REGEXEXTRACT('Fame Lookup'!$A2,"target=_blank>(.+?)</a>"))
  2. C3セル:=IFERROR(VALUE(REGEXEXTRACT('Fame Lookup'!$A2,"<td class=bold sorting_1>(.+?)</td>")))
  3. D3セル:=IFERROR(VALUE(REGEXEXTRACT('Fame Lookup'!$A2,"</td><td>(.+?)</td>")))
  4. E3セル:=IFERROR(VALUE(REGEXEXTRACT('Fame Lookup'!$A2,"<td class=bold>(.+?)</td>")))


　その後、それぞれ下にオートフィルしてください(100行くらい)。

　次は、押したら上にあるコードを起動してくれるボタンを作ります。ボタンの作り方はちらっと2話で紹介しているのですが、とりあえずこちらの解説記事を見てください。

www.atmarkit.co.jp

　こちらの通りにボタンを作成して、「Scraping8」を割り当てればボタンを押すだけで名声の街道を表示してくれるシートに早変わりです。

f:id:TS_hartmann:20201213140604p:plain
「名声の街道」シートはこのような表示になります。他日本人クランの前回のCWE成績も書かれていますが……そこは見逃してください……

　「名声の街道」のL4セルに書かれているURLのページがスクレイピングされますので、各自で使用する場合はFame Lookupの自クランのページのURLをL4セルに貼り付けてください。元データの更新が1時間おきなのでその点だけ注意してください。

　基本的にはこれで作業は終わりとなります。作ったやつにさらなる加工を施したり、別の用途で扱うのは皆さん各自の良心に任せます。

　あまり短時間に連続して稼働させて各サイトのサーバーに負荷をかけるのは止めましょうね……

最後に

　シート様は自分の非を認め、嘘が付けないようなシートにしてしまったことを素直にクラメンに詫びました(シートの保護削除)。このシートが編集できるようになるかどうかは分かりませんが、はんどさんも指揮官に復帰したことですし、何とかなるのでしょう。多分。

　とりあえずこれで一回終わりとなります(多分)。続きがあるにしろ、しばらく明けてからになるでしょう(おそらく)。実験環境(通常CW)を経験していないコードなんて公開していいものか迷っているので。

ts-hartmann.hatenablog.com

続き

WoT CWE管理シート自動化の旅々　5.『私立マクロテリア』

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々

ts-hartmann.hatenablog.com

これの続き

 

はじめに

私「Google Apps script……？」
私「Excelのマクロみたいなものでしょうか……(参考書を読む)」
私「っ！」
私「マクロと同じことが出来るってだけじゃなくて、なんかより便利な気がしますねえ……」

 　私はその本を眺めながら4か月前に起きていた出来事を思い出していました。それはこれに比べてはるかにデータ量があって、その名をCWE管理用スプレッドシートと謂いました。

やりたいこと

　前回の続きです。敵クラン名を出力できたので、今までに出力した情報を戦闘予定表にメモとして記入します。どこのセルにメモを挿入すればいいのかは、以前作成した「スクレイピング4(戦)」でもう求めているので、あとはそのセルにメモを挿入するコードを書けばいいのです。

メモ書きを挿入するまで

手順1. GASの記述

　 　とりあえずコードを出しましょう。基本的には前回のコードに連続して動作するコードとなります。前回のコードの続きに書いてください。

function Memo() {

  //戦闘情報をメモとして挿入

  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName = "スクレイピング4(戦)";
  var cell = spreadSheet.getSheetByName(sheetName).getRange("E2:E100").getValues();//表示セル
  var result = cell.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr = Array.prototype.concat.apply([],result);//2次元配列を1次元配列にする
  var count = Arr.length; //配列変数Arrの配列数を調べる
  
  var cell2 = spreadSheet.getSheetByName(sheetName).getRange("I2:I100").getValues();//開始陣地
  var result2 = cell2.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr2 = Array.prototype.concat.apply([],result2);//2次元配列を1次元配列にする
  
  var cell3 = spreadSheet.getSheetByName(sheetName).getRange("H2:H100").getValues();//相手クラン名
  var result3 = cell3.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr3 = Array.prototype.concat.apply([],result3);//2次元配列を1次元配列にする
  
  var cell4 = spreadSheet.getSheetByName(sheetName).getRange("D2:D100").getValues();//開始時間
  var result4 = cell4.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr4 = Array.prototype.concat.apply([],result4);//2次元配列を1次元配列にする
  
  
  var cell6 = spreadSheet.getSheetByName(sheetName).getRange("A2:A100").getValues();//プロヴィンス名
  var result6 = cell6.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr6 = Array.prototype.concat.apply([],result6);//2次元配列を1次元配列にする
  
  var cell7 = spreadSheet.getSheetByName(sheetName).getRange("K2:K100").getValues();//マップ名
  var result7 = cell7.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr7 = Array.prototype.concat.apply([],result7);//2次元配列を1次元配列にする
  
  var cell8 = spreadSheet.getSheetByName(sheetName).getRange("L2:L100").getValues();//戦線名
  var result8 = cell8.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr8 = Array.prototype.concat.apply([],result8);//2次元配列を1次元配列にする
  
  var sheetName2 = "戦闘予定(CWE用)";
  var sheetName3 = "対戦相手判別用";
  
  for ( var i = 0; i < count; i++) {
  
      if(Arr[i] !== "--"){
         if(spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).getValue() === "?" || spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).getValue() === "@"){
         spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).clearNote();
         spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).setNote( "Start time:    " + Arr4[i] + String.fromCharCode(10) + String.fromCharCode(10) + "front name:    " + Arr8[i] + String.fromCharCode(10) + "Province name:    " + Arr6[i] + String.fromCharCode(10) + "Map name:    " + Arr7[i] + String.fromCharCode(10) + String.fromCharCode(10) + "Start side:    " + Arr2[i] + String.fromCharCode(10) + "Enemy clan name:    " + Arr3[i]);
        
         if(Arr2[i] === "seed"){
            spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).setValue("→");
            var memo = spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).getNote(); //該当するセルのメモを参照
          
            spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).clearNote();　//該当するセルのメモを消去
            spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).setNote( memo + String.fromCharCode(10) + String.fromCharCode(10) + "Commander:    " + "?" + String.fromCharCode(10) + "Result:    " + "?");　//該当するセルのメモを記入
         
        }else{
              if(Arr2[i] !== "?"){
                spreadSheet.getSheetByName(sheetName2).getRange(Arr[i]).setValue("@");
                }
             }
         }
        spreadSheet.getSheetByName(sheetName3).getRange(Arr[i]).setValue(Arr3[i]);
        }
      }
 
  
  

   Logger.log(Arr2);
   
}

　これ書いているうちに私にも何と書いてあるのかわからなくなりました。えーなんですかね？多分これコピペするだけでいいと思います。戦闘予定(CWE用)の所は実際予定表を組んでいるシートの名前に置き換えてください。
　いつも通りスプレッドシートのidは使用しているスプレッドシートのidをお願いします。

　コードを実行して、3話で貼った記事の画像みたいにメモ書きが挿入されていたとしたら成功です。

f:id:TS_hartmann:20201213072417p:plain
これみたいな感じ

　3話~5話までに書かれた3つのコードは連続して稼働するコードです。トリガーの設定は3話に紹介した最初のコードのみで大丈夫です。

手順2. シート側の記述

　「戦闘予定表」のシートをコピーして、名前を「対戦相手判別用」にしたシートを用意してください。書式とかはそのままで、何か書き換える必要とかはないです。

2020/12/19追記;P11:BC109の範囲のセル中身を消しておいてください。また、C11セルに=QUERY('戦闘予定(CWE用)'!C11:E109)・J11セルに='戦闘予定(CWE用)'!J11と入力してください(後者はJ11:O109の範囲にコピー)。

f:id:TS_hartmann:20201219110709p:plain
間違えて表の左側を消すようなことはしないでください

　何故このシートを用意するのかは……また今度の話にしましょうか……
　まあ、別の処理をする時の布石です。さらに言えば3話に言った「情報保存のための一工夫」がこれなのでした。
f:id:TS_hartmann:20201213082926p:plain
「対戦相手判別用」には相手クランの名前が挿入されている

ほんへ

　シートを管理しているGMがいます。GMはどこかワクワク楽しそうにしています。次に投げるマップはどんなマップでしょう。次に戦う敵はどんなクランでしょう、と期待に胸を膨らませているのです。
　そのGMは一体誰か？そう、私なのでした。

ts-hartmann.hatenablog.com

続き

　

WoT CWE管理シート自動化の旅々　4.『出力無き結果のコード』

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々

ts-hartmann.hatenablog.com

これの続き

いつもの

　これは…自動化のお話です。とあるセルの関数様がエラーを吐きました。関数がエラーを出したのは、重い処理だからでした。それは多すぎる関数のせいだったのです。
　どうでもいいですが、なぜエラーは「吐く」と言うのでしょうねぇ…？嗚咽のように予期せず突然吐き出してしまうからなのでしょうか？知りませんけど。
　2人はひそかに自動化を進め、やがて関数は減りました。そう、いわゆる努力の結晶です。

やりたいこと

　基本的には前回の記事の続きです。前回の記事ではマッチング情報の取得までは終わらせてあるのでその続き、クランidから敵クラン名を検索するところをやります。

　前回スクレイピングした内容にはクランのidはあるけども肝心のクラン名が書いてありません。これじゃ対戦相手わからん。ということで、クランidからクラン名を検索するコードを書いておきましょう。

clanidからクラン名を検索する手順

　とりあえずコードを先に貼っておきましょう。基本的には前回のコードに連続して動作するコードとなります。前回のコードの続きに書いてください。

function Scraping3() {

  //WOT APIを使用※敵クラン名検索
 
  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName = "スクレイピング4(戦)";
  var cell = spreadSheet.getSheetByName(sheetName).getRange("F2:F100").getValues();
  var result = cell.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var Arr = Array.prototype.concat.apply([],result);//2次元配列を1次元配列にする
  var count = Arr.length; //配列変数Arrの配列数を調べる
  
 
  　//URL、データ取得のループ処理※130行目まで 
  　var ary = [];
    for ( var i = 0; i <= count-1; i++) {
    
    var URL = 'https://api.worldoftanks.asia/wot/clans/info/?application_id=#APPLICATIONID&clan_id='+Arr[i];//Grobal Map for world of Tanks　プロヴィンスのクラン情報
                
    var response = UrlFetchApp.fetch(URL);//スクレイピング
    var response2 = response.toString();
    var mat = response2.match(/"tag":"(.+?)"/g);
    
    
    
    ary.push(mat);
    }
  
 
  //logファイルにログを書き出す 

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

　いらない処理や定義があるのはご愛嬌ということで……

　もちろんのことスプレッドシートのidは使用しているシートのidを、#MYAPPLICATIONIDにはWargamingAPIのapplication_idを入れてください。
　出力するシートは前回、マッチング情報を出力したシートと同じシートです。さすればマッチングした敵クラン名が出力されていることでしょう。

　今回は短いですが、この辺で。基本的には1つの記事には1つのコードを載せるという形を取ろうと思います(初っ端破っているけど)。

最後に

　こうしてすべて彼の計画通りに進んだ自動化のお話は幕を閉じました。出力無き結果のコードとなった処理自身の手によって。

※まだ続きます

ts-hartmann.hatenablog.com 続き

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

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々

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

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

　

ts-hartmann.hatenablog.com

これの続き

お約束

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

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

やりたいこと

　戦闘予定は組んだけど、戦闘相手事前に知っておきたい。でも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(戦)」のシートを作成してください。 　それぞれのシートの役割はこんな感じです。

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


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

  1. B2セルに=iferror(offset('Province参照'!$A$2,match($C3,'Province参照'!$C$2:$C$1000,0)-1,1))
  2. C2セルに='戦闘予定(CWE用)'!H11
  3. 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(戦)には

  1. A2セルに='スクレイピング2(戦)'!C3
  2. B2セルに=IFERROR(REGEXEXTRACT(SUBSTITUTE(REGEXEXTRACT('スクレイピング3(戦)'!$A2,"(clan.+?)+?clan_id"&CHAR(34)&":"&$N$2&""),CHAR(34),""),"(.+?):{loose_elo_delta"),IF(NOT(D2=""),"seed","--"))
  3. C2セルに=IFERROR(SUBSTITUTE(REGEXEXTRACT('スクレイピング3(戦)'!$A1,""&CHAR(34)&"start_at"&CHAR(34)&":(.+?),"),CHAR(34),""))
  4. 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"))
  5. E2セルに=iferror(address(match(A2,'戦闘予定(CWE用)'!$H$1:$H$109,0),match(D2,'戦闘予定(CWE用)'!$A$9:$AZ$9,0),4),"--")
  6. 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),"--"),"--")))
  7. H2セルに=IFERROR(IF(B2="seed","404 not found",REGEXEXTRACT(SUBSTITUTE(G2,CHAR(34),""),"tag:(.+)")))
  8. 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="","","?"))))
  9. K2セルに=IFERROR(VLOOKUP($A2,'Province参照'!$C:$D,2,FALSE))
  10. L2セルに=IFERROR(VLOOKUP(VLOOKUP($A2,{'Province参照'!C:C,'Province参照'!B:B},2,FALSE),'Province参照'!$Q:$R,2,FALSE))
  11. M2セルに=IFERROR(REGEXEXTRACT('戦闘予定(CWE用)'!I11,"\((.+?)\)"))
  12. N2 セルに=IFERROR(IF(AND(TEXT(D2-$R$1,"[mm]")<=TEXT("0:15","[mm]"),TEXT(D2-$R$1,"[mm]")>TEXT("0:00","[mm]")),"○","×"),"×")
  13. 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 続き

WoT CWE管理シート自動化の旅々　2.『プロヴィンス一覧の取得』※修正版

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々

改訂2021/01/13: WG APIの糞仕様によりこのままだと後の処理が上手くいかなくなるため、解決するために「スクレイピング4(戦)」シートのE列に領地idを表示させるように変更しました。
ts-hartmann.hatenablog.com これの続き

やっと本題

　このシートには、多数の自動化方法があります。ひとくくりに自動化と言っても、簡単な計算を行うだけのいわゆるSUM関数などと呼ばれる関数から、スクレイピング、さらに複雑な処理とされている予定表の自動記入に分けられます。この自動化の証であるスクリプトを完成させ、その完成度の高さに満足し、その美しさと書式の輝きに処理さえも思わず重くなってしまうほどのシートを作ったのは誰でしょうか？そう、私です！

　実際はそこまで重い処理でもありませんし(当社比)、簡単なコードなのですが。

　今回はタイトルにもある通り、プロヴィンス情報の一覧をWargamingAPIからスクレイピングしてシートに出力していこうと思います。

プロヴィンス一覧をスクレイピングするための手順1. GAS側の記述

　プロヴィンス一覧を取得するためには、まず戦線の情報を取得する必要があります。戦線の情報取得は何もパラメーターを入れなくても出力されるのに対して、プロヴィンス一覧はfront_idのパラメーターを入れなくてはいけないからです。

　スクリプト書くのが上手い人は一括でこの処理をやってしまうのでしょうが、今回はfront_idを取得→取得した結果をシートに出力→シートに出力されたそれぞれのfront_idを取得→各frontのprovincesを取得という回りくどい順序で手順を組んでいこうと思います。

　まずは、以下にコードを記述します。

function Scraping5() {


  //WoT Grobalmapの戦線を検索※WoTAPIを使用
 
  const URL1 = 'https://api.worldoftanks.asia/wot/globalmap/fronts/?application_id=#MYAPPLICATIONID&language=en';

  var response = UrlFetchApp.fetch(URL1);
 
  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName = "front1";
  var str = response;
  var str2 = str.toString();
  var rep = str2.replace(/"/g,"");
  var mat = rep.split("front_name:");

  var count = mat.length; //配列変数matの配列数を調べる
  
  Logger.log(count);
  
  spreadSheet.getSheetByName(sheetName).getRange("A1:D100").clearContent();//セル内のコンテンツ削除※値や数式
  
  for (var i = 0; i < count; i++) {
  spreadSheet.getSheetByName(sheetName).getRange(i + 1, 1).setValue(mat[i]);　//配列変数matのi+1番目に格納されている値をセルAi+1に入力
  }
  
  Scraping6()

}

function Scraping6() {

  //各戦線のプロヴィンス情報を取得

  var id = "スプレッドシートのid";  
  var spreadSheet = SpreadsheetApp.openById(id);  
  var sheetName = "front2";
  var cell = spreadSheet.getSheetByName(sheetName).getRange("A2:A10").getValues();
  var result = cell.filter(function(value){return (value[0].length > 0)});//空白要素の配列を削除
  var count = result.length; //配列変数resultの配列数を調べる
  
  var mata = ["1","2","3","4","5"];
  var count2 = mata.length;
  var data = [];
  for ( var i = 0; i < count2; i++) {
  
  //URL、データ取得のループ処理※51行目まで 
   var ary = [];
   for ( var k = 0; k < count; k++) {
   var URL2 = 'https://api.worldoftanks.asia/wot/globalmap/provinces/?application_id=#MYAPPLICATIONID&front_id='+result[k]+'&language=en&&page_no='+mata[i];//Grobal Map for world of Tanks　プロヴィンスのトナメ情報
   var response = UrlFetchApp.fetch(URL2);
   var response2 = response.toString();
   var rep = response2.replace(/"/g,"");
   
   ary.push(rep);
   }
   data.push(ary)
  }
   var ary2 = data.toString();
   var mat = ary2.split("active_battles:"); 
   var count3 = mat.length; //配列変数matの配列数を調べる
   Logger.log(count3);
   
   var sheetName2 = "Province1";
   
   spreadSheet.getSheetByName(sheetName2).getRange("A1:D1000").clearContent();//セル内のコンテンツ削除※値や数式
   
   for (var i = 0; i < count3; i++) {
   spreadSheet.getSheetByName(sheetName2).getRange(i + 1, 1).setValue(mat[i]);　//配列変数matのi+1番目に格納されている値をセルAi+1に入力
   }
   
   
}

　変数名などがおかしいのはご愛嬌ということでよろしくお願いします。GAS側の記述はこのコードをコピペすればいいはずです。

　「#MYAPPLICATIONID」にはparameterに入っているapplication_idを(2か所)、「スプレッドシートのid」にはデータを出力するシートのidを(2か所)代入して置き換えてください。
　はい、GAS側の作業は終わりですね。

プロヴィンス一覧をスクレイピングするための手順2. スプレッドシート側の記述

1. 必要なシートの作成

　後は必要な情報を切り取る作業をスプレッドシート内で行います。
　「front1」「front2」「Province1」「Province参照」の計4つのシートを用意します。そして、「front2」のA2セルに=IFERROR(REGEXEXTRACT(front1!A2,"front_id:(\w+)}"))と記述して、下の行までオートフィルします(5,6行あればいいと思います)。2021/01\07追記: 使用する正規表現を.(任意の一文字)から\w(アルファベット・アンダーバー・数字)に変更しました。
　それぞれのシートの役割はこんな感じです。

  1. 「front1」: WargamingAPIから戦線の一覧が書かれたページをスクレイピングした内容が出力されてあります。
  2. 「front2」: 「front1」に書いてある内容から戦線名が書かれている所のみ抜き出す。
  3. 「Province1」: WargamingAPIから戦線に存在するプロヴィンスの一覧が書かれたページをスクレイピングした内容が出力されてあります。
  4. 「Province参照」: 「Province1」に書いてあるデータを使いやすい形に加工。


　はい、データ取得の手順は終わりです！！あとは、上のコードを実行するだけでプロヴィンス情報がずらっと出てくるはずです！！
　

f:id:TS_hartmann:20201210181717p:plain
「province1」を見るとこんな感じに出力されるはずです。この1行1行が1プロヴィンスの情報です。

　......これで終わりだと置いてけぼりになった気がするので最後まで書きますよ……

2. 情報の切り取り

　最後に、手順2-1で出力したデータを使えるように加工します。
　このデータの中から抜き出せるかつ、あると便利な情報というと、プライムタイム・戦線名・領地名・戦闘マップ名くらいのものだと思います。「Province参照」を作ったのはこの情報を抜き取ったものを表示させるためです。それぞれの情報を抜き出すときは、

  1. A2セル(プライムタイム):=IFERROR(IF(REGEXEXTRACT(Province1!A2,"prime_time:(.+?),")="","",TEXT(REGEXEXTRACT(Province1!A2,"prime_time:(.+?),")+$F$1,"[h]:mm")))
  2. B2セル(戦線名):=IFERROR(REGEXEXTRACT(Province1!$A2,"front_name:(.+?),"))
  3. C2セル(領地名):=IFERROR(REGEXEXTRACT(Province1!$A2,"province_name:(.+?),"))
  4. D2セル(戦闘マップ名):=IFERROR(REGEXEXTRACT(Province1!$A2,"arena_name:(.+?),"))
  5. E2セル(領地id):=IFERROR(REGEXEXTRACT(Province1!$A2,"province_id:(.+?),"))


と、それぞれ2行目辺りに記述して、下にオートフィルします(500行くらいあれば大丈夫だと思います)。なお2-1で抜き出したデータ内にあるプライムタイムはグリニッジ標準時で書かれていますので、日本標準時に直すために、F1のセルに9:00と記入します。

　これでプロヴィンス一覧情報のスクレイピング作業は終わりです。お疲れさまでした。

f:id:TS_hartmann:20210115114230p:plain
データがこんな感じに加工できているはずです。

　プロヴィンス名が英語表記ですが、戦車兵の皆さんは英語表記なんてへっちゃらな筈です。

おまけ

　 　覚えておくと便利ですよ。

www.atmarkit.co.jp

それではまた。次回がありましたらよろしくお願いします。

最後に

　私たちは最後の夜を過ごしました。きらきら輝くGMを眺めたり、GM担当官の考えを伝授したり。他クランの話を聞いたり、私のクランの話をしてあげたり。クラメンの姿はあまりに儚くて、か弱くて……まるで昔の私を見ているようでした。

　私「(明かりを消す)」

　あれから6か月が経ちました。私は11Bというクランからはるか遠く(？？？)離れたクランに所属しています。

　私「指揮官見習い試験合格者……」「っ！」

　そこには彼の苦悩の日々と、将来への希望が綴られていました。指揮のこと、ある担当官に出会い一人で指揮する技量と気迫をもらったこと。ようやく指揮官見習いになれたこと。そして…

　？？「1人前の指揮官になったら、お世話になったGM担当官に(クリスマス)ガチャ75連贈ります！」
　私「(ﾆｯｺﾘ)」

　GMをしながら気長に待っていますよ、はんどさん。

※彼は既にメイン指揮官です。

ts-hartmann.hatenablog.com 続き

WoT CWE管理シート自動化の旅々　1.『Wargaming公式　WargamingAPI』

World of Tanks World of Tanks-管理部 World of Tanks-管理部-CWE管理シート自動化の旅々


ts-hartmann.hatenablog.com

 この記事の続きの内容になっています。

 

本題のシート自動化に入る前に1.Wargaming API

　幼いころから私(Wargaming)は戦車を動かすのが好きでした。中でも一番好きだったのはG.W.Tiger(P)。空から弾を落とせると評判の自走砲です。

 

Wargaming「私もG.W.Tiger(P)みたいにいろんなところにスタンを入れたい！」

母「じゃあまずは戦場に出て、立派なArty乗りにならないとね。」

Wargaming「Arty乗りになったらスタンを入れさせてくれる？」

母「もちろんよ！ね？」

父「？ああ(困惑気味)。ｺﾞﾎﾝ。あくまでもダメ出せて勝利に貢献できるようになってからだぞ。」

Wargaming「本当ね？本当の本当ね？」

母「本当の本当よ。」

Wargaming「(顔を輝かせる)私Arty乗りになる！」

母「(ﾆｯｺﾘ)」

父「(困惑)」

Wargaming「(やる気を出す)」

 

　自分(Wargaming)で言うのもなんですが、私(Wargaming)は懸命に努力し毎日、毎日必死に勉強しました。そして、糞開発Wargaming社において2013年に開発され、情報取得がしやすいように作られたAPIがあります。そう、WargamingAPIです！※筆者はWargamingとは何の関係性もありません。ただの1プレイヤーです。

developers.wargaming.net

 

　情報の取得をしやすくなるようにAPIを作っているはずなのに、痒いところに手が届かない、そんなWargamingAPIですが、データ取得を自動化するにあたっては切り離せない存在です。とりあえずWoTのアカウントでログインしておきましょう。

 

　ログインしたら「MY APPLICATION」から自分のアプリケーションを作りましょう。名前は使用用途が分かれば適当でいいです。そして「Mobile」にチェック。これで準備は完了です。

 

　後は、「API REFERENCE」から欲しい情報を選択、各parameterをセットして「SEND REQUEST」をクリックすれば画像の右下のようにデータを表示させることが出来ます。なお、「Realm for the request」をAsiaにしておきましょう。あとAsiaの初期言語は何故かタイ語なのでlanguageをen(英語)にしておきましょう。※日本語非対応

f:id:TS_hartmann:20201210121526p:plain

GMの戦線一覧を表示させている画像。application_idは消させていただいています。

 

 

本題のシート自動化に入る前に2.スクレイピング

　シート自動化の話に入る前に覚えておいてほしいことがもうひとつあります。それはスクレイピングとそれをする時に書くコードです。

 

　それは「UrlFetchApp.fetch(URL)」です。URLの所にそれぞれのページのURLを書くと、そのページのデータを持ってきてくれるコードです。その取得したデータを整形して見やすいようにするところまでがスクレイピングなのですけども。

 

　ところで、このコードのURLの所に上のURL for requestのところにあるURLを入れたらどうなるのでしょうか？

 

　では、上のページのURL for requestに書いてあるURLをUrlFetchApp.fetch(URL)のURLに入れ、ログを出力するコードを書いてログを見てみましょう。

f:id:TS_hartmann:20201210125715p:plain

1枚目の画像に書いてあるURLをスクレイピングした結果。念の為コードも一緒に乗せておきます。

 　なんと1枚目の画像において帰ってきたデータがそのまま記載されているではありませんか！

 

　はい、この内容をスプレッドシートに記載し、そこから切り取りたい情報だけを切り取って活用するのが今回のシート自動化における定義が表すこと1つ目、スクレイピングとなります。

 

　WargamingAPIからは他にもいろいろな情報を持ってくることが出来るのですが、基本やることは上に書いてあることです。

 

WargamingAPIに欲しい情報がなかったら？

　もしもWargamingAPIに欲しい情報がなかった場合、GMから直接データを持ってくることになります。その際は、上で書いたとおりにやっても何も情報を持ってくることが出来ません。なぜならGMのページはJava Scriptで動作しており、Java Scriptで書かれたデータはそのままだとスクレイピングすることが出来ません。その時は、PhantomJsCloudというものを使ってスクレイピングすることによって、Java Scriptで動作するページからもスクレイピングすることが出来るようになります。

www.phantomjscloud.com

 　使い方とかは下のページを見てください。絶対私が説明するよりわかりやすいはずです。

tonari-it.com

 

 　今回はここまでにしましょう。多分おそらくきっとmaybe次からは本題のシート自動化のお話に入っていきます。

 

最後に

　こうして私はシートの自動化に着手しました。風の吹くまま気の向くままあちこちコードを書いて色々な経験をし、2か月が過ぎました。

　はいっ、それでは問題です。殺伐とした戦場地帯を征く、ため息をこぼしてしまうほどの環境の変わらないゲームは一体何でしょう？そう、10周年のWoTです！！

　　

 

ts-hartmann.hatenablog.com

 続き。