JavaScripts

【Google Apps Script】「Range.getValueの使用頻度が高すぎます」問題を解決する方法

GAS
     
       

Google Apps Script(GAS)でソースを書いていると見る警告を今回ご紹介させていただけたらと思います。主にGoogle Apps Script(GAS)の標準で提供しているgetRange().getValue()を頻繁に利用していると使いすぎですと警告がでてきます。

上記のエラーが出てきた時の具体的に対処方法について簡単にご紹介をさせていただきます。また今からGoogle Apps Script(GAS)を勉強し始めようと思っている人も記述途中で表示されると書き直す必要がでてくるために、先にお目通しいただけますと幸いです。

どうやったら警告されるのか

Range.getValue の使用頻度が高すぎます。と表示される場合、主にはfor文を記述している内部処理にgetRange().getValue()を組み込んでいると表示されるの間違いなしです。

なんで表示されるのかというとGoogle Apps Script(GAS)の提供しているgetRangeは内部処理でスプレッドシートのAPIを叩いて取得しにいっているようで叩きすぎると怒られます。

じゃそんなメソッド提供すんなやって聞こえてきそうですが、そういうもんなんだと一旦咀嚼してもらい対処法をご紹介させていただきます。

まずは警告を簡単に表示させるスクリプトを下記に記載します。

function testFunc() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = spreadsheet.getActiveSheet();
  var values;
  for (var i=1; i<100; i++) {
    for (var j=1; j<100; j++) {
       values = ss.getRange(i,j).getValue();
    }
  }
}

この記述で簡単に表示することができます。

それでは具体的な対処方法を下記で紹介してきます。

対処法

上記で紹介したソースコードリファクタリングします。

データ格納方法

function testFunc() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = spreadsheet.getActiveSheet();
  var values = ss.getDataRange().getValues(); 
}

処理はたった3行で先程の処理を内包させることができます。getDataRange().getValues();を行っていただくことで現在シートに記載されている内容をすべて2次元配列としてvaluesに一気に格納することができます。

実際に格納されていたデータをソースコード上で利用したい場合は下記のコードで呼び出すことができます。

格納データの呼び出し方法

function testFunc() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = spreadsheet.getActiveSheet();
  var values = ss.getDataRange().getValues(); 

  //データの呼び出し
   Logger.log(values[1][1]);
}

Logger.logはGoogle apps scriptのデバッグ等ログを吐き出して確認したいときのメソッドになります。cmd+Enter(windowsの場合、ctrl+Enter)でログの確認をすることができます。

valuesは全データが2次元配列で格納されているので他言語同様に格納先を0から指定してもらえれば配置されているデータを取得することができます。

とても簡単です。valuesは[列][行]の順で格納されているので中身を変更して確かめてみてください。

次にデータをセルにセットする方法を紹介してきます。

セルにデータをセットする方法

function testFunc() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = spreadsheet.getActiveSheet();
  var values = ss.getDataRange().getValues(); 

  //データの呼び出し
   Logger.log(values[1][1]);

  //データセット
  ss.getRange(1,1,2,2).setValues([["テストColumn"],["テストColumn2"]]);
}

データのセットには結局getRangeを使ってしまいます。が、2次元配列をsetValuesで渡すことができるため、呼び出し回数としてはかなり圧縮できます。

またソースをみていただくと分かる通り、getRangeで範囲指定が可能なため、for文回してインクリメント格納みたいことをせず、2次元配列を作成して最後の処理に上記ソースを1行追加すればスマートになります。

【参考】汎用クラス

下記のgithubでも公開させていただきましたが、上記のスプレッドシート操作についてクラス化したため、利用してみてください。

https://github.com/nullpointlab/gasClass

またGoogle Apps Script(GAS)のクラス化については前回記事にしているため、よろしければ一読ください。

上記のtest()メソッドは呼び出した時にどのような挙動をするのかをイメージしていただくため、適当に記述してます。

function test() {
  var c = new sheetOperation('****sheetId***');
  Logger.log(c.values[3][5]);
  Logger.log(c.getLastColNum());
  Logger.log(c.getLastRowNum(0));
  Logger.log(c.getFstNum("column"));
  Logger.log(c.getFstNum("row"));
  var name = c.findString("検索文字");
  Logger.log(name[0]);
  Logger.log(name[1]);
  var arr = [["a","b"],["c","d"]]; 
  c.setDataLstColValues([["テストColumn"],["テストColumn2"]])
  c.setDataLstRowValues([["テストRow","テストRow2"]])
}




/**
* @auther nosuke
* @update 2019-07-07
*/
function sheetOperation(sheetId) {
  //コンストラクタ  
  this.ss = SpreadsheetApp.openById(sheetId);    
  this.values = this.ss.getDataRange().getValues();

  /**
  * @description 行列の始まりを取得します。 
  * @param {String} "column"か"row"を引数に入れてください。
  * @return {Integer}
  */
  this.getFstNum = function(matrix){
    for (var i = 0; i < this.values.length; i++) {
      for (var j = 0; j < this.values[i].length; j++) {
        if(!this.values[i][j]){
          continue;
        }else{
          return (matrix=="column")?i:j;
        }
      }
    }
  }
  
  /**
  * @description 列の終わりを取得します。 
  * @return {Integer}
  */  
  this.getLastColNum = function(){
    return this.values.length;
  }
  
  /**
  * @description 行の終わりを取得します。 
  * @return {Integer}
  */    
  this.getLastRowNum = function(column){
    return this.values[column].length;
  }
  
  /**
  * @description 対象シートから文字列を検索します。 
  * @param {String} 検索文字を引数に入れてください。
  * @return {Array} 行と列を配列で返却します。
  */    
  this.findString = function(str){
    for (var i = 0; i < this.values.length; i++) {
      for (var j = 0; j < this.values[i].length; j++) {
        if(this.values[i][j]==str){
          return [i,j];
        }
      }
    }
  }
  
  /**
  * @description 対象シートの指定セルにデータをセットします。 
  * @param {int} 開始行
  * @param {int} 開始列
  * @param {int} 終了行
  * @param {int} 終了列
  * @param {Array} 指定セル同様の配列を作成し、引数に入れてください。
  */  
  this.setDataValues = function(startRow,startCol,endRow,endCol,value){
    this.ss.getSheets()[0].getRange(startRow,startCol,endRow,endCol).setValues(value);
  }
  
  /**
  * @description 最終行に1列目からデータをセットします。
  * @param {Array} 2次元配列でセットしてください。
  */      
  this.setDataLstRowValues = function(value){
    this.ss.getSheets()[0].getRange(this.values[0].length,1,value.length,value[0].length).setValues(value);
  }
  
  /**
  * @description 対象シートから文字列を検索します。 
  * @param {String} 検索文字を引数に入れてください。
  */     
  this.setDataLstColValues = function(value){
    this.ss.getSheets()[0].getRange(1,this.values[0].length+1,value.length,value[0].length).setValues(value);
  }
  
}


まとめ

いかがだったでしょうか。今回の「Range.getValueの使用頻度が高すぎます」問題はぐぐるといろんな方がハマっているので皆様のお役に立てれば幸いです。

スポンサードリンク