2014年3月27日木曜日

Google スプレッドシートを超簡易DBとして使う　その１

前回大失敗した、Blogger API v3を利用したボドゲ記事を横断的に表示するwebサービスの開発に再チャレンジしました。

今回は、Google DocsのスプレッドシートとブログをGoogle visualization APIを使って連携させる手法を試したところ、前回致命的だった『IEでうまく表示されない』という問題も一応の解決を見ました。

とりあえずの完成品はこちら。


◎基本仕様

  1. Google Apps Scriptを使用してブログの全記事を取得・編集して、スプレッドシートに超簡易DBを作成する。
  2. Google visualization APIを使用して記事のリンク一覧をブログ上にテーブル表示する。
  3. 個々のボドゲ記事をスプレッドシートから取得してブログに表示する。

簡単に説明するとこんな感じです。

では順に説明していきます。

1-1．Google スプレッドシートでDBのひな形を作成します。

Google ドライブ(https://drive.google.com/)でスプレッドシートを作成して、『レポート一覧』シートと『集計』シートを作成します。

『レポート一覧』シートはブログ記事を取得して、１つ１つのボドゲレビューを分割して保存しておくシートで『集計』シートはボドゲ（ラベル）ごとにマージした情報を保存するシートです。

『レポート一覧』シートは【No】・【ラベル名】・【投稿日時】・【タイトル】・【URL】・【HTML】の６項目で『集計』シートは【No】・【ラベル名】・【レポ数】・【最新記事】・【URL】・【ASIN】・【駿河屋】の７項目です。


1-2．Google Apps Scriptでブログ記事の取得・集計プログラムを作成します。

スプレッドシートの[ツール→スクリプトエディタ]でエディタが開くのでプログラムを書きます。
リファレンス(https://developers.google.com/apps-script/guides/sheets)がわかりにくいですが、まあ頑張れば読めないことはないです。ノリとしてはExcelのVBAを書いている感覚にちょっと近い。

んで、メインとなるのが『UrlFetchApp』というURLで指定したページにアクセスしてレスポンスを取得してくれるクラスで、おそらく今回のキモです。

使い方は超簡単。

response = UrlFetchApp.fetch(postURL);
content = response.getContentText();

postURLでアクセスしたいURLを指定してレスポンスに対してgetContentTextを実行すればページのHTMLが文字列で取得できます。

なので、
１．ブログ（http://boardge.blogspot.jp/）ヘアクセスして最新の記事のリンクを取得する。
２．記事へアクセスする
３．記事をボドゲレビューごとに分割して『レポート一覧』シートへ書き込む。
４．『前の投稿』のリンクを取得する。
５．全記事分２～４を繰り返す。

この手順でブログの全記事を『レポート一覧』シートへ登録することができます。
あとは『レポート一覧』シートから『集計』シートを作成します。ほとんどExcelのVBAです。

UrlFetchAppクラス・・・。これはすごい。
これだけ手軽に別ページからデータを取得できるとは、クロスドメインスクリプトうんぬんで悩みまくっていたのがアホらしくなります。

というわけで調子に乗って、UrlFetchAppを利用して、Amazonと駿河屋へアクセスして広告用のリンクを自動作成する処理も作成してみました。（これは気が向いたら別記事でアップするかも。しないかも。）

いろいろがんばった結果こんな感じになります。

レポート一覧　※HTML(F列)は見難くなるので非表示にしています

集計　※駿河屋広告リンク(G列)は見難くなるので非表示にしています

ソースコードはこんな感じです。


/**
 * ブログ記事取得
 * 処理：全ブログ記事を取得し【レポート一覧】シートへ書き込む
 */
function getBlogPost(){
  var response = UrlFetchApp.fetch("http://boardge.blogspot.jp/");
  var content = response.getContentText();
  var postURL = getStringSlice(content, "<h3 class='post-title entry-title' itemprop='name'>\n<a href='", "'");
  
  var repoSheet = SpreadsheetApp.getActive().getSheetByName("レポート一覧");
  
  //記事のクリア
  repoSheet.getRange(2, 1, repoSheet.getLastRow(), repoSheet.getLastColumn()).clear();
  var row = 2;
  while(postURL != ""){
    response = UrlFetchApp.fetch(postURL);
    content = response.getContentText();
    var title = getStringSlice(content, "<h3 class='post-title entry-title' itemprop='name'>\n", "\n</h3>");
    var postDate = getStringSlice(content, "<abbr class='published' itemprop='datePublished' title='", "'>").substring(0, 10);
    var reports = getStringSlice(content, "<div class='post-body entry-content'", "<div class='post-footer'>").split("<h4>");
    var labels = getStringSlice(content, "<span class='post-labels'>\nラベル:\n", "</span>").split("\n");
    
    for(var i = 0; i < reports.length; i++){
      for(var j = 0; j < labels.length; j++){
        var label = getStringSlice(labels[j], "rel='tag'>", "</a>");
        var labelTrim = htmlSpecialCharsDel(label);
        var h4EndIndex = reports[i].indexOf("</h4>");
        var labelIndex = reports[i].indexOf(label);
        if(label != "" && h4EndIndex != -1 && labelIndex != -1 && labelIndex < h4EndIndex){
          repoSheet.getRange(row, 1).setValue("=row() - 1");
          repoSheet.getRange(row, 2).setValue(labelTrim);
          repoSheet.getRange(row, 3).setValue(postDate);
          repoSheet.getRange(row, 4).setValue(title);
          repoSheet.getRange(row, 5).setValue(postURL);
          repoSheet.getRange(row, 6).setValue("<h4>" + reports[i]);
          row++;
        }
      }
    }
    //次記事のURL取得
    postURL = getStringSlice(content, "<a class='blog-pager-older-link' href='", "'");
  }
  repoSheet.getRange(2, 1, repoSheet.getLastRow(), repoSheet.getLastColumn()).sort([{column: 2, ascending: true}, {column: 3, ascending: true}]);
  repoSheet.hideColumns(6);
}
  
/**
 * 集計処理
 * 【レポート一覧】シートからラベルごとに集計を行う
 */
function summary(){
    
  var repoSheet = SpreadsheetApp.getActive().getSheetByName("レポート一覧")
  var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計")
  //集計表のクリア
  summarySheet.getRange(2, 1, summarySheet.getLastRow(), summarySheet.getLastColumn()).clear();
  var tmpLabel = repoSheet.getRange(2, 2).getValue();
  var repoCount = 0;
  row = 2;
  for(var i = 2; i <= repoSheet.getLastRow()+1; i++){
    label = repoSheet.getRange(i, 2).getValue();
    if(label == tmpLabel){
      repoCount++;
    } else {
      summarySheet.getRange(row, 1).setValue("=row() - 1");
      summarySheet.getRange(row, 2).setValue(tmpLabel);
      summarySheet.getRange(row, 3).setValue(repoCount);
      summarySheet.getRange(row, 4).setValue(repoSheet.getRange(i - 1, 4).getValue());
      summarySheet.getRange(row, 5).setValue(repoSheet.getRange(i - 1, 5).getValue());
      tmpLabel = label;
      repoCount = 1;
      row++;
    }
  }
}
/**
 * 広告リンク作成処理
 * 【集計】シートから広告リンクを作成する
 */
function setAdHtml(){
  var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計");
  for(var i = 194; i < summarySheet.getLastRow(); i++){
    label = summarySheet.getRange(i + 2, 2).getValue();
    summarySheet.getRange(i + 2, 6).setValue(getAsin(label + ", ボードゲーム"));
    summarySheet.getRange(i + 2, 7).setValue(getSurugaAd(label));
    Utilities.sleep(2000);
  }
}
function getStringSlice(content, startStr, endStr){
  var indexStart = content.indexOf(startStr);
  if(indexStart == -1){
    return "";
  } else {
    indexStart += startStr.length
    return content.slice(indexStart, content.indexOf(endStr, indexStart));
  }
}


それからGoogle Apps Scriptは設定した時間に自動的に処理をさせることができるので、今回の処理を自動実行させるよう設定しておけば、常に最新のブログ記事をスプレッドシートに取り込むことができます。

しかし、Google Apps Scriptの処理はものすごく遅い！
今回作成した処理程度でタイムアウトします。（※Amazonへのアクセスは１秒の間隔を開けずに連続するとエラーになるのでそれの対応もありますが。）

なので処理を複数バッチに分割するなどの仕掛けを入れないといけないのでまだバッチ化していません。あーめんどくさい。


ここまで出来たら、ブログ側でこのスプレッドシートの内容を表示すれば完成ですが、長くなってしまったので今回はここまで。
つづきはこちらです。
投稿者 時刻:
ラベル: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)