ぼどレポ: Google Apps Scriptによるバッチ処理の開発【ソースコードあり】

久々の技術文書です。

今回のテーマはGASを使ったバッチ処理。

◎やりたいこと

当ブログメインコンテンツ（？）のボドゲレポートは、Google スプレッドシートをつかって記事取得と集計を行いGoogle visualization APIを使って表示しています。（詳しくはこちら　→　Google スプレッドシートを超簡易DBとして使う　その１）

この記事取得と集計を自動で行うことが目標です。

実はこの処理、すんごい時間がかかるのでたまーに気の向いた時にしかやってませんでした。これだとブログ記事を書いてもリアルタイムではレポート一覧に反映されないので、なんだかなーっと思っていたけどこの度ようやく重い腰を上げて自動処理化してみた次第です。

◎処理の流れ

大まかには下記フローの通り。

ブログ記事取得処理(getBlogPost)メソッドだけトリガに登録されてます。

下記処理は別々のメソッドに分かれています。つまり全体が１つのバッチ処理ではなく小さなバッチ処理を次々に連続して呼び出しています。
・ブログ記事取得処理
・記事集計処理

・広告リンク作成処理

・公開用シートコピー処理

◎開発のポイント

各処理をメソッドに分けて処理の終わりで次のメソッドのバッチ処理をスケジューリングする。
Google apps script　のタイムアウト時間（6分）を超えるような処理をバッチ化する場合は、タイムアウトする前に自分自身のメソッドを次の処理としてスケジューリングする。
処理速度を上げる。
処理ログを出力する。

以下順番に説明していきます。

◎ソースコード

１．バッチのスケジューリング

※メソッドA（getBlogPost）の完了後メソッドB（setSummary）の処理を実行する場合

function getBlogPost(){
    //処理いろいろ（略）

    setBatchLog(arguments.callee.name, "集計処理トリガ登録開始");
    ScriptApp.newTrigger("setSummary")
    .timeBased()
    .after(2 * 60 * 1000)//2分後に実行
    .create();
    setBatchLog(arguments.callee.name, "集計処理トリガ登録完了");
}

メソッドB側で次のようにスケジューリングされたトリガを削除します。

function setSummary(){
    setBatchLog(arguments.callee.name, "既存バッチ削除開始");
    var triggers = ScriptApp.getProjectTriggers();
    for(var i = 0; i < triggers.length; i++){
      if(triggers[i].getHandlerFunction() == arguments.callee.name){
        setBatchLog(arguments.callee.name, "削除バッチID：" + triggers[i].getUniqueId());
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
    setBatchLog(arguments.callee.name, "既存バッチ削除終了");

    //処理いろいろ(略)
}

２．タイムアウトしないようにバッチ処理を行う

まあ特筆するようなことはないですね。開始時間から5分経過していたら、次のトリガをスケジューリングしてfor文を抜けます。finishFlagは全処理が完了したか否かのフラグです。

function setAdHtml(){
    for(var i = startRow; i < summaryData.length; i++){

      //処理いろいろ(略)

      if(new Date() - startTime >= 5 * 60 *1000){
          //5分経過していた場合タイムアウトするので次のトリガを登録（リミットは6分）
          ScriptApp.newTrigger(arguments.callee.name)
          .timeBased()
          .after(2 * 60 * 1000)//2分後に実行
          .create();
          finishFlag = false;
          break;
      }
    }
}

３．処理速度を上げる

Range.getValueメソッドは結構重い処理らしくfor文の中などでうっかり使用するとこんな感じで怒られます。

    //スクリプトによるメソッド Range.getValue の使用頻度が高すぎます。Collapse
    //File: コード Line: 100
    //このスクリプトは、コストがかかると考えられるメソッドを使用しています。
    //起動のたびにリモート サーバーに時間のかかる呼び出しが生成され、スクリプトの
    //実行時間に重大な影響を与える可能性があります。スクリプトのパフォーマンスが
    //問題である場合は、別のメソッド（Range.getValues() など）の使用を検討してください。

おとなしくRange.getValues　を使いましょう。

・修正前

var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計");
summarySheet.getRange(i, j).getValue() //データにアクセスする時

・修正後

var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計");
var summaryData = summarySheet.getDataRange().getValues();
summaryData[i][j] //データにアクセスする時

たしかgetRange(i, j)の場合のindexは１からでsummaryData[i][j]の場合は０からだったかな。よく覚えてないけど。修正する時はこのへん注意です。

４．処理ログを出力する。

まあ呼び出すと処理ログを別シートに書き出してくれるだけのメソッドです。
methodNameを無理やり出力するところがスゴい気に入らない。『arguments.caller』みたいなことは書けないみたいですね。

function setBatchLog(methodName, logText) {
  var batchLogSheetId = PropertiesService.getScriptProperties().getProperty("BATCH_LOG_SHEET");
  var batchLogSheet = SpreadsheetApp.openById(batchLogSheetId).getSheetByName("稼動ログ");
  var lastDataRow = batchLogSheet.getDataRange().getLastRow();
  var nowDate = new Date();
  var logData = [];
  logData[0] = "=row() - 1";
  logData[1] = Utilities.formatDate(nowDate, "Asia/Tokyo", "yyyy-MM-dd");
  logData[2] = Utilities.formatDate(nowDate, "Asia/Tokyo", "HH:mm:ss");
  logData[3] = "[" + methodName + "]";
  logData[4] = logText;
  batchLogSheet.getRange(lastDataRow + 1, 1, 1, logData.length).setValues([logData]);
}

出力サンプル↓

やっぱコンソール画面は黒地に緑ですね！（無意味）

あとはtry catchでエラーが発生した時にエラーメールを飛ばすようにしました。

  try {
    //処理いろいろ(略)
  } catch(e) {
    MailApp.sendEmail({
      to: "XXXXXXXX@gmail.com",
      subject: arguments.callee.name + "エラー",
      htmlBody: "message:" + e.message + "\r\n" +
      "fileName:" + e.fileName + "\r\n" +
      "lineNumber:" + e.lineNumber + "\r\n" +
      "stack:" + e.stack
    });
  }

◎ソースコード（全体）

/**
* ブログ記事取得
* 処理：全ブログ記事を取得し【レポート一覧】シートへ書き込む
*/
function getBlogPost(){
  try {
    setBatchLog(arguments.callee.name, "***************************************");
    setBatchLog(arguments.callee.name, "レポート取得開始");
    setBatchLog(arguments.callee.name, "***************************************");
    var startTime = new Date();
    
    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("レポート一覧");
    
    //記事のクリア
    setBatchLog(arguments.callee.name, "---------------------------------------");
    setBatchLog(arguments.callee.name, "レポート一覧シートクリア処理開始");
    var clearRange = repoSheet.getDataRange();
    setBatchLog(arguments.callee.name, "削除データエリア：" + clearRange.getA1Notation());
    clearRange.clear();
    setBatchLog(arguments.callee.name, "レポート一覧シートクリア処理終了");
    setBatchLog(arguments.callee.name, "---------------------------------------");
    
    var repoData = [];
    var row = 0;
    while(postURL != ""){
      Utilities.sleep(1000);
      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);
          labelTrim = labelTrim.replace(/(^[\s　]+)|([\s　]+$)/g, "");
          
          var h4EndIndex = reports[i].indexOf("</h4>");
          var labelIndex = reports[i].indexOf(label);
          if(label != "" && h4EndIndex != -1 && labelIndex != -1 && labelIndex < h4EndIndex){
            repoData[row] = [8];
            repoData[row][0] = "=row() - 1";
            repoData[row][1] = labelTrim;
            repoData[row][2] = postDate;
            repoData[row][3] = title;
            repoData[row][4] = postURL;
            repoData[row][5] = "<h4>" + reports[i];
            repoData[row][6] = getStringSlice(reports[i], "時間&#65306;", "<");
            repoData[row][7] = getStringSlice(reports[i], "要素&#65306;", "<");
            row++;
          }
        }
      }
      
      //次記事のURL取得
      postURL = getStringSlice(content, "<a class='blog-pager-older-link' href='", "'");
    }
    var header = [["No", "ラベル名", "投稿日時", "タイトル", "URL", "HTML", "プレイ時間", "要素"]];
    repoSheet.getRange(1, 1, row + 1, header[0].length).setValues(header.concat(repoData.sort()));
    repoSheet.hideColumns(6);
    
    //  Browser.msgBox("記事取得完了");
    setBatchLog(arguments.callee.name, "---------------------------------------");
    setBatchLog(arguments.callee.name, "レポート件数：" + row + "件");
    setBatchLog(arguments.callee.name, "---------------------------------------");
    
    setBatchLog(arguments.callee.name, "集計処理トリガ登録開始");
    
    ScriptApp.newTrigger("setSummary")
    .timeBased()
    .after(2 * 60 * 1000)//2分後に実行
    .create();
    
    setBatchLog(arguments.callee.name, "集計処理トリガ登録完了");
    
    setBatchLog(arguments.callee.name, "レポート取得終了");
    setBatchLog(arguments.callee.name, "処理時間：" + (new  Date() - startTime) / 1000 + "秒");
  } catch(e) {
    setBatchLog(arguments.callee.name, "予期しないエラーが発生しました");
    setBatchLog(arguments.callee.name, "message:" + e.message);
    setBatchLog(arguments.callee.name, "fileName:" + e.fileName);
    setBatchLog(arguments.callee.name, "lineNumber:" + e.lineNumber);
    setBatchLog(arguments.callee.name, "stack:" + e.stack);
    MailApp.sendEmail({
      to: "XXXXXXXX@gmail.com",
      subject: arguments.callee.name + "エラー",
      htmlBody: "message:" + e.message + "\r\n" +
      "fileName:" + e.fileName + "\r\n" +
      "lineNumber:" + e.lineNumber + "\r\n" +
      "stack:" + e.stack
    });
  }
}

/**
* 集計処理
* 【レポート一覧】シートからラベルごとに集計を行う
*/
function setSummary(){
  try {
    setBatchLog(arguments.callee.name, "***************************************");
    setBatchLog(arguments.callee.name, "集計処理開始");
    setBatchLog(arguments.callee.name, "***************************************");
    var startTime = new Date();
    
    setBatchLog(arguments.callee.name, "既存バッチ削除開始");
    var triggers = ScriptApp.getProjectTriggers();
    for(var i = 0; i < triggers.length; i++){
      if(triggers[i].getHandlerFunction() == arguments.callee.name){
        setBatchLog(arguments.callee.name, "削除バッチID：" + triggers[i].getUniqueId());
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
    setBatchLog(arguments.callee.name, "既存バッチ削除終了");
    
    var repoSheet = SpreadsheetApp.getActive().getSheetByName("レポート一覧")
    var repoData = repoSheet.getDataRange().getValues();
    var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計")
    var summaryData = [];
    
    //集計表のクリア
    setBatchLog(arguments.callee.name, "---------------------------------------");
    setBatchLog(arguments.callee.name, "集計シートクリア処理開始");
    var clearRange = summarySheet.getDataRange();
    setBatchLog(arguments.callee.name, "削除データエリア：" + clearRange.getA1Notation());
    clearRange.clear();
    setBatchLog(arguments.callee.name, "集計シートクリア処理終了");
    setBatchLog(arguments.callee.name, "---------------------------------------");
    
    var tmpLabel = repoData[1][1];
    var label = repoData[1][1];
    var timeArray = [];
    var playerNumArray = [];
    var categoryArray = [];
    var repoCount = 0;
    
    var row = 0;
    for(var i = 1; i < repoData.length; i++){
      label = repoData[i][1];
      if(label == tmpLabel){
        repoCount++;
        
      } else {
        playerNumArray = trimList(playerNumArray);
        timeArray = trimList(timeArray);
        categoryArray = trimList(categoryArray);
        
        summaryData[row] = [13];
        summaryData[row][0] = "=row() - 1";
        summaryData[row][1] = tmpLabel;
        summaryData[row][2] = repoCount + " レポ";
        summaryData[row][3] = repoData[i - 1][2];//最新投稿日時
        summaryData[row][4] = playerNumArray.join("・");//プレイ人数
        summaryData[row][5] = timeArray.join("・");//時間
        summaryData[row][6] = categoryArray.join("・");//要素
        summaryData[row][7] = "";//ASIN
        summaryData[row][8] = "";//駿河屋
        summaryData[row][9] = getFilterURL(playerNumArray, "n");//人数URL
        summaryData[row][10] = getFilterURL(timeArray, "t");//時間URL
        summaryData[row][11] = getFilterURL(categoryArray, "c");//要素URL
        summaryData[row][12] = "=row() - 1";
        
        tmpLabel = label;
        repoCount = 1;
        
        playerNumArray = [];
        timeArray = [];
        categoryArray = [];
        row++;
      }
      
      label = repoData[i][1];
      playerNumArray.push(repoData[i][6].match(/\d+人/));
      timeArray.push(repoData[i][6].match(/\d+分/));
      categoryArray.push(repoData[i][7].split("&#12539;"));//"・"で分割
    }
    
    playerNumArray = trimList(playerNumArray);
    timeArray = trimList(timeArray);
    categoryArray = trimList(categoryArray);
    
    summaryData[row] = [13];
    summaryData[row][0] = "=row() - 1";
    summaryData[row][1] = tmpLabel;
    summaryData[row][2] = repoCount + " レポ";
    summaryData[row][3] = repoData[repoData.length - 1][2];//最新投稿日時
    summaryData[row][4] = playerNumArray.join("・");//プレイ人数
    summaryData[row][5] = timeArray.join("・");//時間
    summaryData[row][6] = categoryArray.join("・");//要素
    summaryData[row][7] = "";//ASIN
    summaryData[row][8] = "";//駿河屋
    summaryData[row][9] = getFilterURL(playerNumArray, "n");//人数URL
    summaryData[row][10] = getFilterURL(timeArray, "t");//時間URL
    summaryData[row][11] = getFilterURL(categoryArray, "c");//要素URL
    summaryData[row][12] = "=row() - 1";//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    
    var header = [[
      "No", 
      "ラベル名", 
      "レポ数", 
      "最新投稿日時", 
      "プレイ人数", 
      "時間", 
      "要素", 
      "ASIN", 
      "駿河屋", 
      "人数フィルタURL", 
      "時間フィルタURL", 
      "要素フィルタURL", 
      "ツイート文字数"]];
    
    summarySheet.getRange(1, 1, row + 2, header[0].length).setValues(header.concat(summaryData));
    
    setBatchLog(arguments.callee.name, "---------------------------------------");
    setBatchLog(arguments.callee.name, "ラベル件数：" + (row + 1) + "件");
    setBatchLog(arguments.callee.name, "---------------------------------------");
    
    setBatchLog(arguments.callee.name, "広告リンク作成トリガ登録開始");
    var triggers = ScriptApp.getProjectTriggers();
    for(var i = 0; i < triggers.length; i++){
      if(triggers[i].getHandlerFunction() == arguments.callee.name){
        setBatchLog(arguments.callee.name, "削除バッチID：" + triggers[i].getUniqueId());
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
    
    ScriptApp.newTrigger("setAdHtml")
    .timeBased()
    .after(2 * 60 * 1000)//2分後に実行
    .create();
    
    setBatchLog(arguments.callee.name, "広告リンク作成トリガ登録完了");
    setBatchLog(arguments.callee.name, "集計処理終了");
    setBatchLog(arguments.callee.name, "処理時間：" + (new  Date() - startTime) / 1000 + "秒");
    
  } catch(e) {
    setBatchLog(arguments.callee.name, "予期しないエラーが発生しました");
    setBatchLog(arguments.callee.name, "message:" + e.message);
    setBatchLog(arguments.callee.name, "fileName:" + e.fileName);
    setBatchLog(arguments.callee.name, "lineNumber:" + e.lineNumber);
    setBatchLog(arguments.callee.name, "stack:" + e.stack);
    MailApp.sendEmail({
      to: "XXXXXXXX@gmail.com",
      subject: arguments.callee.name + "エラー",
      htmlBody: "message:" + e.message + "\r\n" +
      "fileName:" + e.fileName + "\r\n" +
      "lineNumber:" + e.lineNumber + "\r\n" +
      "stack:" + e.stack
    });
  }
}

/**
* 広告リンク作成処理
* 【集計】シートから広告リンクを作成する
*/
function setAdHtml(){
  try {
    setBatchLog(arguments.callee.name, "***************************************");
    setBatchLog(arguments.callee.name, "広告リンク作成処理開始");
    setBatchLog(arguments.callee.name, "***************************************");
    var startTime = new Date();
    
    setBatchLog(arguments.callee.name, "既存バッチ削除開始");
    var triggers = ScriptApp.getProjectTriggers();
    for(var i = 0; i < triggers.length; i++){
      if(triggers[i].getHandlerFunction() == arguments.callee.name){
        setBatchLog(arguments.callee.name, "削除バッチID：" + triggers[i].getUniqueId());
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
    setBatchLog(arguments.callee.name, "既存バッチ削除終了");
    
    var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計");
    var summaryData = summarySheet.getDataRange().getValues();
    var label = "";
    var startRow = 0;
    var finishFlag = true;
    for(var j = summaryData.length - 1; j >= 0; j--){
      if(summaryData[j][7] != "" || summaryData[j][8] != ""){
        startRow = j + 1;
        setBatchLog(arguments.callee.name, "データ開始行：" + startRow);
        break;
      }
    }
    
    for(var i = startRow; i < summaryData.length; i++){
      label = summaryData[i][1];
      summaryData[i][7] = getAsin(label + ", ボードゲーム");
      summaryData[i][8] = getSurugaAd(label);
      
      if(new Date() - startTime >= 5 * 60 *1000){
        //5分経過していた場合タイムアウトするので次のトリガを登録（リミットは6分）
        ScriptApp.newTrigger(arguments.callee.name)
        .timeBased()
        .after(2 * 60 * 1000)//2分後に実行
        .create();
        setBatchLog(arguments.callee.name, "---------------------------------------");
        setBatchLog(arguments.callee.name, i + "/" + (summaryData.length - 1) + "件完了");
        setBatchLog(arguments.callee.name, "---------------------------------------");
        setBatchLog(arguments.callee.name, "次トリガ登録");
        finishFlag = false;
        break;
      }
      
      Utilities.sleep(2000);
    }
    
    summarySheet.getDataRange().setValues(summaryData);
    
    setBatchLog(arguments.callee.name, finishFlag);
    if(finishFlag){
      setBatchLog(arguments.callee.name, "広告リンク処理全件完了");
      
      setBatchLog(arguments.callee.name, "公開用シート作成トリガ登録開始");
      var triggers = ScriptApp.getProjectTriggers();
      for(var i = 0; i < triggers.length; i++){
        if(triggers[i].getHandlerFunction() == arguments.callee.name){
          setBatchLog(arguments.callee.name, "削除バッチID：" + triggers[i].getUniqueId());
          ScriptApp.deleteTrigger(triggers[i]);
        }
      }
      
      ScriptApp.newTrigger("setPublicSheet")
      .timeBased()
      .after(2 * 60 * 1000)//2分後に実行
      .create();
      
      setBatchLog(arguments.callee.name, "公開用シート作成トリガ登録完了");
      
    }
    setBatchLog(arguments.callee.name, "広告リンク作成処理終了");
    setBatchLog(arguments.callee.name, "処理時間：" + (new  Date() - startTime) / 1000 + "秒");
  } catch(e) {
    setBatchLog(arguments.callee.name, "予期しないエラーが発生しました");
    setBatchLog(arguments.callee.name, "message:" + e.message);
    setBatchLog(arguments.callee.name, "fileName:" + e.fileName);
    setBatchLog(arguments.callee.name, "lineNumber:" + e.lineNumber);
    setBatchLog(arguments.callee.name, "stack:" + e.stack);
    MailApp.sendEmail({
      to: "XXXXXXXX@gmail.com",
      subject: arguments.callee.name + "エラー",
      htmlBody: "message:" + e.message + "\r\n" +
      "fileName:" + e.fileName + "\r\n" +
      "lineNumber:" + e.lineNumber + "\r\n" +
      "stack:" + e.stack
    });
  }
}

function setPublicSheet(){
  try {
    setBatchLog(arguments.callee.name, "***************************************");
    setBatchLog(arguments.callee.name, "公開用シート作成開始");
    setBatchLog(arguments.callee.name, "***************************************");
    var startTime = new Date();
    
    setBatchLog(arguments.callee.name, "既存バッチ削除開始");
    var triggers = ScriptApp.getProjectTriggers();
    for(var i = 0; i < triggers.length; i++){
      if(triggers[i].getHandlerFunction() == arguments.callee.name){
        setBatchLog(arguments.callee.name, "削除バッチID：" + triggers[i].getUniqueId());
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
    setBatchLog(arguments.callee.name, "既存バッチ削除終了");

    var repoSheet = SpreadsheetApp.getActive().getSheetByName("レポート一覧");
    var repoData = repoSheet.getDataRange().getValues();
    var summarySheet = SpreadsheetApp.getActive().getSheetByName("集計");
    var summaryData = summarySheet.getDataRange().getValues();

    var repoSheetCopy = SpreadsheetApp.getActive().getSheetByName("レポート一覧_公開用");
    var summarySheetCopy = SpreadsheetApp.getActive().getSheetByName("集計_公開用");
    repoSheetCopy.clear();
    summarySheetCopy.clear();
    
    repoSheetCopy.getRange(1, 1, repoData.length, repoData[0].length).setValues(repoData);
    summarySheetCopy.getRange(1, 1, summaryData.length, summaryData[0].length).setValues(summaryData);
    
    repoSheetCopy.hideColumns(6);
    summarySheetCopy.hideColumns(8, 5);

    repoSheet.hideColumns(6);
    summarySheet.hideColumns(8, 5);

    setBatchLog(arguments.callee.name, "公開用シート作成完了");
    setBatchLog(arguments.callee.name, "処理時間：" + (new  Date() - startTime) / 1000 + "秒");
    MailApp.sendEmail({
      to: "XXXXXXXX@gmail.com",
      subject: "全記事取得バッチ正常完了",
      htmlBody: "ok"
    });
  } catch(e) {
    setBatchLog(arguments.callee.name, "予期しないエラーが発生しました");
    setBatchLog(arguments.callee.name, "message:" + e.message);
    setBatchLog(arguments.callee.name, "fileName:" + e.fileName);
    setBatchLog(arguments.callee.name, "lineNumber:" + e.lineNumber);
    setBatchLog(arguments.callee.name, "stack:" + e.stack);
    MailApp.sendEmail({
      to: "XXXXXXXX@gmail.com",
      subject: arguments.callee.name + "エラー",
      htmlBody: "message:" + e.message + "\r\n" +
      "fileName:" + e.fileName + "\r\n" +
      "lineNumber:" + e.lineNumber + "\r\n" +
      "stack:" + e.stack
    });
  }
}

★まとめ

バッチはいろいろと応用範囲が広いのでフレームワークっぽい感じのシステム作ってもおもしろいかも。

ぼどレポ

ページ

2014年11月5日水曜日

Google Apps Scriptによるバッチ処理の開発【ソースコードあり】