[GAS][スプレッドシート]処理速度を向上するには: 逆引きGoogle Apps Script

▼最適化前のスクリプト

// 最適化前
function notOptimized() {
  var start         = new Date();
  var ss            = SpreadsheetApp.getActiveSpreadsheet();
  var sheet         = ss.getActiveSheet();
  var duplicatedIds = '';
  for(var i=2; i<=sheet.getLastRow(); i++){
    for(var j=2; j<=sheet.getLastRow(); j++){
      if(i==j) continue;
      if(sheet.getRange('B'+i).getValue() == sheet.getRange('B'+j).getValue()){
        duplicatedIds += j + ' ';
      }
    }
  }
  var end  = new Date();
  var span = end - start;
  Logger.log('処理時間：' + span + 'ミリ秒');   
  Browser.msgBox('重複しているのは、行'+duplicatedIds+'です');
}

▼最適化後のスクリプト

function optimized(){
  var start         = new Date();
  var ss            = SpreadsheetApp.getActiveSpreadsheet();
  var sheet         = ss.getActiveSheet();
  var duplicatedIds = '';
  var values        = sheet.getDataRange().getValues();
  var lastRow       = sheet.getLastRow();
  for(var i=2; i<=lastRow; i++){
    for(var j=2; j<=lastRow; j++){
      if(i==j) continue;
      if(values[i-1][1] == values[j-1][1]){
        duplicatedIds += j + ' ';
      }
    }
  }
  var end  = new Date();
  var span = end - start;
  Logger.log('処理時間：' + span + 'ミリ秒'); 
  Browser.msgBox('重複しているのは、行'+duplicatedIds+'です'); 
}

異なるのは、Spreadsheet APIのリクエスト回数。最適化前は処理開始から終了まで約14万回リクエストしているのに対し、最適化後はシートオブジェクトの取得をあわせてもたったの5回である。

最適化前は、ループのたびにgetLastRow()メソッドやgetRange()、getValue()メソッドを呼び出しているが、これは内部でAPIリクエストを行っていることになる。つまり、getLastRow()でシートの最終行を取得するために+1リクエスト、getRangeで範囲を抽出するために+1リクエスト、抽出した範囲から値を取得するために+1リクエストと、これだけで計3回リクエストしていることになる。

for(var i=2; i<=sheet.getLastRow(); i++){
    for(var j=2; j<=sheet.getLastRow(); j++){
      if(i==j) continue;
      if(sheet.getRange('B'+i).getValue() == sheet.getRange('B'+j).getValue()){

行7～行14では1回のループで6回のAPIリクエストを行っていることが分かるが、トータルで49×49×6=14,406回もAPIリクエストしている計算になる。

そのため、通常は行25や行26のように、あらかじめ必要な値を変数に格納しておき、あとで使いまわすことになる。行25では、抽出したRangeに対してgetValues()メソッドを実行することにより、値を二次元配列で受け取っている。行26では、最終行をあらかじめ取得している。

  var values        = sheet.getDataRange().getValues();
  var lastRow       = sheet.getLastRow();

あとは、行27-行34のように、配列にアクセスして文字列を比較するだけなので、高速に処理することができる。

 for(var i=2; i<=lastRow; i++){
    for(var j=2; j<=lastRow; j++){
      if(i==j) continue;
      if(values[i-1][1] == values[j-1][1]){
        duplicatedIds += j + ' ';
      }
    }
  }

まとめ

今回のスプレッドシートに関わらず、なるべくAPIリクエストを少なくすることを常に意識し、コードを組み立てて頂きたい。これは、ほとんどの場合、API呼び出し時間 > JavaScriptの内部処理時間になるためだ。

受け取るデータに関係なく、接続に一定の時間を要するため、getLastRow()のように、たとえレスポンスデータはごくごく小さなものでも、あらかじめ取得して使いまわすべきである。

関連記事

この投稿は 2012 年 4 月 10 日 火曜日 10:34 PM に Google Apps Script カテゴリーに公開されました。