周回遅れオジサンが頑張る、Googleスプレッドシート+GoogleAppsScriptによるWebサイトスクレイピング

はじめに

突然ですが、私は対戦格闘ゲームが好きです。PS3のヴァンパイアリザレクションでよく対戦しておりますが、友達の誘いにより、このたびギルティギアを始めてみました。

何事も挑戦です。
ギルティを覚えるついでに、これまで未経験の、Googleスプレッドシートの便利な使い方を覚えちゃおう。
どちらも周回遅れのスタートだけど、そんな目論見の軌跡です。

Googleスプレッドシートで何をするの？

格闘ゲームをやりこんでる人は、皆さんフレーム表というものを把握しております。
これは、パンチが5フレームで発生(5/60秒)といった、キャラごとに技の性能を示した一覧表になります。
この表を見ることで、ヴァンパイアリザレクションの上達や理解がスムーズに出来た経験があり、ギルティギアでもフレーム表をみて勉強していこう、そう考えておりました。

Darkstalkers Frame Data （ヴァンパイアセイヴァー、ヴァンパイアハンター 、ヴァンパイア データ）

GUILTY GEAR Xrd 公式攻略サイト フレームデータ

このフレーム表ですが、Googleスプレッドシートに貼り付けて、フィルタやソートが出来たら調べが楽なんじゃないか、そんなことを思ったのがはじまりです。

Googleスプレッドシート、苦労の連続編

苦労した項目をつらつらと書きます。

関数「IMPORTHTML」ではUTF8以外のページを読み込めない

関数「IMPORTHTML」を使って簡単に読み込めるのでは？なんて思いましたが、IMPORTHTMLはUTF8専用でした。取込み対象サイトはEUC-jpなんて古臭いコードを使っています。

IMPORTHTML

これが原因で、GoogleAppsScriptを使ってページを読み込む羽目になりました。

HTMLが汚いページは簡単には読み込めない

GoogleAppScriptに切り替えたので、下記リファレンスを参考にコードを書いてみました。
https://developers.google.com/apps-script/reference/xml-service/

  var url = "http://www.4gamer.net/guide/ggxrd/031723018/";
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();

しかし、「 XmlService.parse(xml);」の部分でエラーが発生して上手くいかない。
エラーは元ページのタグの誤りを指摘しており、確かにタグが閉じられていない。

<link rel="alternate" type="application/rss+xml" title="4Gamer.net" href="http://www.4gamer.net/rss/index.xml">

この問題を解決している方がおり、真似する形で対応しました。
http://istudyenglish.therightmistakes.com/google-apps-script-faq1

  var urltemp = UrlFetchApp.fetch(url);
  var txt = urltemp.getContentText("euc-jp");
  var doc = Xml.parse(txt,true); 
  var bodyHtml = doc.html.body.toXmlString();

※Xml.parseですが、現時点で動作するもののサポート切れの命令となっております。
また、この作りで読み込まれるページソースが元のソースと異なっており、後から述べる苦労へと繋がります。

XMLは読み込むのが面倒くさい

いわゆるDomのように、getElementById()等の命令はなく、ひたすらノードをたどる必要があります。
耐えられなかったので検索した所、下記のサンプルに出会うことが出来ました。
https://sites.google.com/site/scriptsexamples/learn-by-example/parsing-html
このサンプルの関数「getElementById、getElementsByClassName、getElementsByTagName」を使うことで、Dom同様の操作が可能となるため、かなりの省力化につながります。

そもそもどこから読んでいいかわからない

データはXMLですが、そもそもただのHPであるため、タグの何番目をとるか？といった機械的なルール作りに悩みました。
これは、GASはいわゆるマクロであるという基本に立ち返り、人の手を模すよう「ヘッダのリンクを見つける→リンク先を見つける」といったルーチンにしたところ、比較的簡単に内容が取得できました。

アイコンを文字に変換しようとしたら、元のソースと変わっていた

格闘ゲームのコマンドは矢印で示されますが、対象サイトは以下のようなアイコンを用いておりました。

<img src="/image/icon_cursor_6b.gif" class="icon_cursor" alt="6">

しかし、Xml.parseによって以下のように変換されてしまうので、注意が必要です。

<img alt="1" class="icon_cursor" src="/image/icon_cursor_1b.gif"/>

最終的にソースコードはこんな感じ

作成したフレーム表は下記になります。
https://docs.google.com/spreadsheets/d/1yBE0LCQJO2dsMgFjQlwgSHPOAnDOqc1lkY-GXAD690E/edit?usp=sharing

ソースコードは下記で、「MakeFrameSheets」を実行することでフレーム表が作成できます。

function MakeFrameSheets() {
  var url1 = "http://www.4gamer.net/guide/ggxrd/031723018/";//フレーム表その1
  var url2 = "http://www.4gamer.net/guide/ggxrd/031723052/";//フレーム表その2

  makeFrameSheetsByUrl(url1);
  makeFrameSheetsByUrl(url2);


  setAllSheetStyle();
}

function makeFrameSheetsByUrl(url) {
  var urltemp = UrlFetchApp.fetch(url);
  var txt = urltemp.getContentText("euc-jp");
  var doc = Xml.parse(txt,true); 
  var bodyHtml = doc.html.body.toXmlString();
  bodyHtml = convArrow(bodyHtml);

  doc = XmlService.parse(convArrow(bodyHtml));
  //キャラ一覧の取得
  var charLinkList = getCharLinks(doc.getRootElement());
  for (var i in charLinkList) {
    makeFrameSheet(doc.getRootElement(),charLinkList[i]);
  }
}
//矢印画像を文字に修正
function convArrow(txt) {
  var ret = txt;
  //XML変換後のアイコンを修正
  ret = ret.split('<img alt="6" class="icon_cursor" src="/image/icon_cursor_6b.gif"/>').join('→');  
  ret = ret.split('<img alt="4" class="icon_cursor" src="/image/icon_cursor_4b.gif"/>').join('←');
  ret = ret.split('<img alt="3" class="icon_cursor" src="/image/icon_cursor_3b.gif"/>').join('＼');
  ret = ret.split('<img alt="2" class="icon_cursor" src="/image/icon_cursor_2b.gif"/>').join('↓');
  ret = ret.split('<img alt="1" class="icon_cursor" src="/image/icon_cursor_1b.gif"/>').join('←');  
  return ret;
}

//フレーム表を作成します
function makeFrameSheet(rootElement,charLinkElement) {
  var charName = charLinkElement.getValue();  //キャラ名取得

  //フレーム表取得処理
  var tableName = charLinkElement.getAttribute("href").getValue().substr(1);
  var elmFrameDiv = getElementById(rootElement,tableName);
  var elmFrameTbl = getElementsByTagName(elmFrameDiv,"table");
  var elmTrList = getElementsByTagName(elmFrameTbl[0],"tr");

  //行のループ
  var s = remakeSheet(charName);//シートをキャラ名で(再)作成
  var row = 1;
  for ( var i in elmTrList) {
    elmTrList[i]
    var elmTdList = elmTrList[i].getChildren();
    var col = 1;
    for (var t in elmTdList ) {
      var value = elmTdList[t].getValue();
      s.getRange(row, col).setValue(value);
      col++;
    }
    row++;
  }
}


//シートを再作成
function remakeSheet(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var first = ss.getSheetByName(sheetName);
  if (first != null ) {
     ss.deleteSheet(first);
  }
  var newSheet = ss.insertSheet();
  newSheet.setName(sheetName);
  return newSheet;
}


//キャラのリンクを取得します
function getCharLinks(element) {
  //キャラメニュー取得
  var elmLinkHeader = getElementsByClassName(element,"horizontal_menu");
  var elmLinkList = getElementsByTagName(elmLinkHeader[0],"a");
  var data = [];
  for(var i in elmLinkList) {
    if(elmLinkList[i].getAttribute("href").getValue().indexOf("#table_result_") == 0 ) {
      data.push(elmLinkList[i]);
    }
  }
  return data;
}

//全シートにスタイルを設定
function setAllSheetStyle() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i in sheets) {
    if (sheets[i].getSheetName() == "メモ" ) { continue;}

    setAllRangeLeft(sheets[i]);
    stripeRange(sheets[i]);
    setHeaderFooter(sheets[i]);
    resetFilter(sheets[i]);
    resizeAllCol(sheets[i]);
  }

}


//全てを左寄せ
function setAllRangeLeft(sheet) {
  var range= sheet.getRange(1, 1, sheet.getLastRow(),sheet.getLastColumn());
  range.setHorizontalAlignment("left");
}

//ヘッダ・フッタの設定
function setHeaderFooter(sheet) {
    sheet.setFrozenColumns(1);//列ヘッダ固定
    sheet.setFrozenRows(1);//行ヘッダ固定
    sheet.getRange(1, 1,1, sheet.getLastColumn()).setBackground("#BDBDBD");
    sheet.deleteRow(sheet.getLastRow());//最終行が不要なので削除
}

//表を縞縞にする
function stripeRange(sheet) {
  var range = sheet.getRange(1, 1, sheet.getLastRow(),sheet.getLastColumn());
  var erc = "#FFFFFF";
  var orc = "#F3F3F3";
  var evenRow = [], oddRow = [], colorArray = [];
  var colIndex = range.getWidth();
  var rowIndex = range.getHeight();
  while (colIndex--) {
    evenRow[colIndex] = erc;
    oddRow[colIndex] = orc;
  }
  while (rowIndex--) colorArray[rowIndex] = rowIndex % 2 ? evenRow : oddRow;
  range.setBackgrounds(colorArray);
}

//全カラムをリサイズ
function resizeAllCol(sheet)
{ 
  SpreadsheetApp.flush();
  var settings = {
    "sheetId": sheet.getSheetId(),
    "dimension": "COLUMNS",
    "startIndex": 0,
    "endIndex": sheet.getLastColumn()
  };
  var requests = [{
    "autoResizeDimensions": {
      "dimensions": settings
    }}];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, sheet.getParent().getId());
} 




//ヘッダにフィルタを設定
//下記URLを参考に
//https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters
//拡張サービス「Google Sheets API」を使用
function resetFilter(sheet) {
  var filterSettings = {
    "range": {
      "sheetId": sheet.getSheetId(),
      "startRowIndex": 0,
      "endRowIndex": sheet.getLastRow(),
      "startColumnIndex": 0,
      "endColumnIndex": sheet.getLastColumn()
    }
  };
  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, sheet.getParent().getId());
}

//下記URLよりコピー
//https://sites.google.com/site/scriptsexamples/learn-by-example/parsing-html
function getElementById(element, idToFind) {  
  var descendants = element.getDescendants();  
  for(i in descendants) {
    var elt = descendants[i].asElement();
    if( elt !=null) {
      var id = elt.getAttribute('id');
      if( id !=null && id.getValue()== idToFind) return elt;    
    }
  }
}

//下記URLよりコピー
//https://sites.google.com/site/scriptsexamples/learn-by-example/parsing-html
function getElementsByClassName(element, classToFind) {  
  var data = [];
  var descendants = element.getDescendants();
  descendants.push(element);  
  for(i in descendants) {
    var elt = descendants[i].asElement();
    if(elt != null) {
      var classes = elt.getAttribute('class');
      if(classes != null) {
        classes = classes.getValue();
        if(classes == classToFind) data.push(elt);
        else {
          classes = classes.split(' ');
          for(j in classes) {
            if(classes[j] == classToFind) {
              data.push(elt);
              break;
            }
          }
        }
      }
    }
  }
  return data;
}

//下記URLよりコピー
//https://sites.google.com/site/scriptsexamples/learn-by-example/parsing-html
function getElementsByTagName(element, tagName) {  
  var data = [];
  var descendants = element.getDescendants();  
  for(i in descendants) {
    var elt = descendants[i].asElement();     
    if( elt !=null && elt.getName()== tagName) data.push(elt);
  }
  return data;
}

宿題事項

正しいAutoFitはどうすべきかがわからない。

列幅をいわゆるAutoFitする命令の動作結果が、手動でAutoFitした時と異なる、具体的には文字に従って列幅が広がるものの、完全にはそろわないため、列幅を手動で整えました。

Xml.parse以外でタグの誤ったサイトを読み込む方法を知りたい

Xml.parseはサポート切れの命令であるため、正しい対策を知りたい。