郵便番号一括追跡プログラムをGoogleAppsScriptで一から作った全行程まとめ(後編)

スポンサーリンク

第3章「スプレッドシートで作った関数を利用する」

ようやく「コア」になる部分が完成したので、次はこれを利用してスプレッドシートに展開していきます。

スプレッドシート上での、簡単な要件についてまとめます。

要件

  1. 伝票番号入力された行と同じ列に、検索した結果を表示する
  2. 任意のA列に入力された伝票番号を一括で検索していく

では、つくっていきます。

イメージしやすいように、概要(レイアウトイメージ)を先に考えます。
こんな感じです。

<レイアウト>

画面レイアウト説明用

つぎに、この考えたレイアウトになるように実装する関数を考えます。

  1. A列に伝票番号を入力するとして、B~F列にひっぱってきたを入力する
  2. 伝票番号の行とデータを入力する行を配列で保持する
  3. 配列を参照して、伝票番号を検索する関数をまわして結果を得る
  4. 得た結果を該当する列に入力していく

こんなところかな・・・

まずは、自分なりに分かりやすくするように作業目標と作業をわけました。

  • 作業目標=伝票番号と結果を返す行
  • 作業=それを受けて色々作業するソース

作業目標を配列でゲットするコードは、こんな感じで書きました。

function getSequenceOfInterest(startRow){

var ss = SpreadsheetApp.getActiveSheet();
//var startRow = 9;
var lastRow = ss.getMaxRows();
var dataRange = ss.getRange(startRow, 1, lastRow - startRow + 1, 1);
var data = dataRange.getValues();
var ary = new Array();//返り値
var i;

//IDとその行番号をセットで配列に入れる
for(i in data){
var ID = data[i][0].toString();

ary.push([ID,startRow]);
startRow++;
}

return ary;
}

GoogleAppsScriptでは、VBAと違ってセルの概念が希薄(ないのかな?)なので、配列を用意して伝票番号とセットで行情報を入れてます。
いちよ、引数にstartRowを取ってますがこの行から作業するよ!って意味で入れてます。汎用性が上がるかは謎・・・

つぎに、この配列を引数として作業の関数を回します。
内容は、配列を順繰りして伝票番号を検索して結果を配列で取得→該当の行に出力です。

function inquiryAndInput(ary){

  var trackingID;
  var trackingResult;
  var targetRow;
  var quantity = ary.length;
  var lastExecuteCount = 0;

  for(lastExecuteCount; lastExecuteCount < quantity; lastExecuteCount++){

      //配列の[0]にIDが[1]に列番号が格納されている
      trackingID = ary[lastExecuteCount][0];
      targetRow  = ary[lastExecuteCount][1];

      //milibはライブラリの接頭語。IDを入れると追跡詳細が配列で返る
      trackingResult = mylib.CallTrackingDetails(trackingID);

      //追跡詳細(配列)を横のセルに一括で入力する
      var ss = SpreadsheetApp.openById("このスプレッドシートシートのID")
      var targetRange = ss.getRange("B" + targetRow + ":F" + targetRow);
      targetRange.setValues([trackingResult]);
  }

やってることは簡単で、上の関数でゲットしてきた配列をもとに関数CallTrackingDetailsを回します。
関数CallTrackingDetailsは今回はライブラリにしているので接頭語にmylibが付いてます。

関数をまわしてゲットしてきた配列は、setValuesで入れてます。
最初は1つずつ入れてましたが調べてみるとこっちのほうが処理が早いと分かったので。

最初は、これで終わりだと思ってましたがそうではありませんでした・・・・

GoogleAppsScriptを回す上で避けて通れないことがあります。
それは

起動時間の壁

なにかスクリプトを作るたびに悩ましい問題を提供してくれるものです。具体的にいうと「6分?以上連続した動作はできない」というGoogle側の制限となります。
考えてみると当たり前の話ですけど、GoogleAppsScriptはサーバ言語で全部Google側でスクリプトを動かしてくれてます。
世界中の人がこれを利用していて、負荷を全てGoogleが持っているわけです。
これではGoogleが大変!ということで「1アカウントについて連続して6分以上の負荷をかけないでね!」とGoogle様からの警告なのです・・・・

6分は360秒。

この伝票番号を検索して結果を記載するスクリプトは、大体の時間で一個処理するのに1秒ちょっとかかります。
ですので、実際使用してみると250個あたりで上限に達してしまいエラーがでてしまいます。

250個も一括処理できるならいいじゃん!

って最初は思ってましたけど、実際の作業は1000~とあるので細かく切り分けて何度も実行するのはメンドクサイ上にミスの元です。
起動限界の突破を目標としてさらにインターネットを彷徨います。

ここで参考にしたのは、このサイト様。

よく使うTime Triggerのテンプレ様

http://qiita.com/soundTricker/items/fd997cdf0825c6b2a146

です。

まず、このサイト様に辿りつくまでが長かった・・・・
ぶっちゃけ最初は「どうしたらいいかわからん!」って感じだったので。

「起動時間が上限に達しました(意訳)」のエラーがでたので、とりあえずそれをググって・・調べて調べて・・・
どうやらトリガーなるものをうまく利用すると上限を突破できる情報をゲットしてそれを調べる・・

他にもTriggerを使った起動限界突破を扱ったサイトは何個かあったのですが、このサイト様が自分なりに一番しっくりきた感じです。

でも正直最初はさっぱり理解できなかった。

なんとなく書いてあることはわかるんです・・・「一旦処理を中断してまた再起動する」ってことくらいは。
理解を難しくしていたのは、「スクリプトプロパティ」の存在とGoogleAppsScriptのバージョンアップでした。

一旦中断して再起動するってことは、ゲームの中断みたいに「どこかに現在の進捗を保存する」ってことです。
どこに保存するか?というと「スクリプトプロパティ」です。しかしこの「ScriptProperties」というオブジェクトがGoogleAppsScriptのアップデートによって非推奨となってしまっているのです。

あんまり良く理解していうない上にバージョンが違っていて益々理解することが難しくなっていました。

結論から言うと、オブジェクトの問題はScriptPropertiesではなく、PropertiesService.getScriptProperties()と書き換えるだけでした。
この機能についても最初は理解に苦慮したのですが、よくよくみてみるとキーバリュー型と似たような感じで対になるものをセットで保存しておく簡易DBみたいなものでした。

じっくり読み解いていけばわかるものです。テンプレを作ってくれた方には感謝しかありません。

ScriptPropertiesをなんとなく理解した次に、テンプレのTriggerUtilについて理解しました。

引用します。

http://qiita.com/soundTricker/items/fd997cdf0825c6b2a146

(function(global){
  var TriggerUtil = {

    DEFAULT_TRIGGER_KEY : "triggerId",
    DEFAULT_INTERVAL_MINUTES : 1,
    DEFAULT_TIMEOUT_MENUTES: 5,

    /**
     * タイムアウトに近いか?
     * @param {Moment} startMoment 処理開始時の時間(Momentオブジェクト)
     */
    isNearlyTimeOut : function(startMoment) {
      return Moment.moment().diff(startMoment , "m") >= this.DEFAULT_TIMEOUT_MENUTES;
    },

    /**
     * 渡されたTriggerKeyで保存されているTriggerを削除
     * @param {string} triggerKey (非必須)ScriptPropertiesに保存されているTriggerIdのKey名  渡されない場合はDEFAULT_TRIGGER_KEYの値になる。
     */
    deleteTrigger : function(triggerKey) {
      triggerKey = triggerKey || this.DEFAULT_TRIGGER_KEY;
      var triggerId = ScriptProperties.getProperty(triggerKey);

      if(!triggerId) {
        return;
      }

      ScriptApp.getScriptTriggers().filter(function(trigger){
        //var trigger = ScriptApp.getScriptTriggers()[0];
        return trigger.getUniqueId() == triggerId;
      })
      .forEach(function(trigger) {
        ScriptApp.deleteTrigger(trigger);
      });

      ScriptProperties.deleteProperty(triggerKey);
    },

    /**
     * 渡されたfunctionNameを再起動
     * @param {object} option (オプション) triggerKeyとintervalMinutesを設定する。
     */
    resumeTrigger : function(functionName , option) {
      if(!functionName) {
        throw new Error("Given functionName");
      }

      var options = option || {};

      var triggerKey = options.triggerKey || this.DEFAULT_TRIGGER_KEY;
      var intervalMinutes = options.intervalMinutes || this.DEFAULT_INTERVAL_MINUTES;

      var triggerId =
          ScriptApp.newTrigger(functionName).timeBased().at(Moment.moment().add("m" ,intervalMinutes ).toDate()).create().getUniqueId();

      ScriptProperties.setProperty(triggerKey, triggerId);

    }

  }
  global.TriggerUtil = TriggerUtil;

})(this);

何度もみました。
まずは、

  1. isNearlyTimeOut
  2. deleteTrigger
  3. resumeTrigger

の3つのメソッドについて細かく見ていきました。
すると、各メソッドがどんな動きをするかどうか見えてきます。

isNearlyTimeOutは、起動時間が設定時間を超えたかどうかを判定するんだな。
deleteTriggerは前のトリガーを消すためにあるんだな。
resumeTriggerは、新しいトリガーを設定するためにあるんだな。

という感じでとらえていきました。

ざっくり理解したあとは、有難くコピペで使わさせて頂いて6分の壁を超えるべく自分のコードと組み合わせていきます。

組み合わせる際に気を付けたのは、以下のとおり

このTriggerUtilはトリガーへの保存時に「LastExcuteCount:最終実行回数」としてスクリプトに保存している。
つまり中断セーブをするときに進捗管理として「回数」を採用していることになるので、この「回数」で管理できるように組む必要があります。

今回の伝票一括追跡プログラムでは、「何個目まで処理した」を回数として組み処理してみました。
こんな感じです。

function inquiryAndInput(ary){

  var startMoment = Moment.moment();
  TriggerUtil.deleteTrigger();

  var lastExecuteCount = parseInt(PropertiesService.getScriptProperties().getProperty("lastExecuteCount") || "0");
  Logger.log("今回の開始は" + lastExecuteCount + "目から開始");
  //lastExecuteCountを使ってデータの取得とか
  var trackingID;
  var trackingResult;
  var targetRow;
  var quantity = ary.length;

  for(lastExecuteCount; lastExecuteCount < quantity; lastExecuteCount++){     var errorCount = 0;      //配列の[0]にIDが[1]に列番号が格納されている       trackingID = ary[lastExecuteCount][0];       targetRow  = ary[lastExecuteCount][1];     while(true) {       try {         //milibはライブラリの接頭語。IDを入れると追跡詳細が配列で返る           trackingResult = mylib.CallTrackingDetails(trackingID);         //追跡詳細(配列)を横のセルに一括で入力する           var ss = SpreadsheetApp.openById("シートのID")           var targetRange = ss.getRange("B" + targetRow + ":F" + targetRow);           targetRange.setValues([trackingResult]);         break;       } catch(e) {         Logger.log(e);         errorCount++;         if(errorCount > 5) {
          saveLastExecute(lastExecuteCount);
          //レジューム処理
          Logger.warning("なんかよく失敗しているのでちょっと経ってから起動します。");
          TriggerUtil.resumeTrigger('main');
          MailApp.sendEmail(Session.getEffectiveUser().getEmail(), "エラー回数が閾値を超えたので少し経ってから再起動します。" , Logger.getLog());
        }
      }
    }

      if(TriggerUtil.isNearlyTimeOut(startMoment)) {
      saveLastExecute(lastExecuteCount);
      break;
    }
      if(lastExecuteCount == quantity) {
        reset();
        MailApp.sendEmail(Session.getEffectiveUser().getEmail(), "作業が完了したので、ご報告します。" , Logger.getLog());
      }
  }
    //レジューム処理
      Logger.log("5分経過したので処理を再起動します。");
      TriggerUtil.resumeTrigger('main');
      MailApp.sendEmail(Session.getEffectiveUser().getEmail(),"一括処理を5分後に再起動します。"  , Logger.getLog());

}

function saveLastExecute(lastExecuteCount) {
  PropertiesService.getScriptProperties().setProperty("lastExecuteCount" , lastExecuteCount);
  Logger.log("今回の終了は" + lastExecuteCount + "目で終了");
}

function main(){
  var targetAry = getSequenceOfInterest(9);
  inquiryAndInput(targetAry);
}

これで、どうにか300件以上も動く伝票一括追跡プログラムが出来上がりました。

最終的に実装した機能は、以下の通りです。

  1. 伝票番号をもとに、荷物が今現在最終的にどうなっているのかを表示できる
  2. 複数の件数を一括で処理できる
  3. 処理時間が6分を超えるような処理でも自動的に中断しながら完遂できる
  4. 状況に応じてメールで連絡する

最後に

突っ込みどころ満載のソースだと思います。

今後も、自分自身で作りあげられるように精進します!

でも、誰かの参考になれば嬉しい。

以上っす。

スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>