【GAS】繰り返し処理で スプレッドシート呼び出し回数を節約する２つのメソッド

概要

GASを、ちょっとしたお役立ちツールくらいとしてしか使っていないので実感することが少ないけど、スプレッドシートを呼び出す回数が少ないほど処理速度が上がるらしい。

同じ結果になるなら呼び出し回数を節約する書き方をしてみようと言う事で、A列を読み込み、その2倍,3倍を計算し、それぞれB列,C列に書き込むスクリプトを例に、スプレッドシートを呼び出す回数を減らす書き方を考える。

こんな感じの実行結果になるやつ。

まずは手計算するような順番で書く

取りあえず何のひねりも無しに書いて、スプレッドシートの呼び出し回数（sheetオブジェクトの実行回数）を数えてみる。

function KUMONshiki() { 
   
  var sheet = SpreadsheetApp.getActiveSheet(); 
   
  //最終行をget 
  var lastRow = sheet.getLastRow(); 
   
  var input, double, triple; 
   
  for(i=1; i<=lastRow; i++){ 
     
    //セルを読み込み（for内１回目） 
    input = sheet.getRange(i, 1).getValue(); 
     
    //２倍を書き込み（for内２回目） 
    double = input * 2; 
    sheet.getRange(i, 2).setValue(double); 
     
    //３倍を書き込み（for内３回目） 
    triple = input * 3; 
    sheet.getRange(i, 3).setValue(triple); 
     
  } 

}

個人的にとても安心な見た目(*´・ω・)

スプレッドシートを呼び出した回数は、最終行getで１回、for文の（読み込み１回＋書き込み２回）×６回で ⇒ 計１９回

節約メソッド①「getValues」

セルを１つずつ読み込むのではなく、範囲で読み込む事で呼び出し回数を節約する。

function getValues() { 
   
  //シートのデータを一気にgetValues 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var data = sheet.getDataRange().getValues(); 
   
  //最終行をget 
  var lastRow = sheet.getLastRow(); 
   
  var input, double, triple; 
   
  for(i=1; i<=lastRow; i++){ 
     
    //getValuesから必要なデータを呼び出し 
    input = data[i-1][0]; 
     
    //２倍を書き込み（for内１回目） 
    double = input * 2; 
    sheet.getRange(i, 2).setValue(double); 
     
    //３倍を書き込み（for内２回目） 
    triple = input * 3; 
    sheet.getRange(i, 3).setValue(triple); 
     
  } 
   
}

getValues、最終行get、for文の書き込み２回×６回で ⇒ 計１３回

範囲で一気に読み込んでいるので、for文の中でA列を扱うのにスプレッドシートを呼び出さなくて良くなった^^)b

(i, 1)と[i][1]の示す要素が違う為、i の取扱に注意が必要。この辺が苦手(ToT)

節約メソッド②「push」&「setValues」

表題を裏切り３つ目のメソッドが・・・表題は「方法」的な意味って事で^^;

読み込みをまとめたので、今度は書き込みをまとめようって事。pushメソッドを使い計算結果を１つの配列に格納、setValuesメソッドで一気に書き込む。

function push() {  
   
  //シートのデータを一気にgetValues 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var data = sheet.getDataRange().getValues(); 
   
  //最終行をget 
  var lastRow = sheet.getLastRow(); 
   
  var input, double, triple; 
  var output = []; 
   
  for(i=0; i<lastRow; i++){ 
     
    //getValuesから必要なデータを呼び出し 
    input = data[i][0]; 
     
    double = input * 2; 
    triple = input * 3; 
     
    //予め作っておいた２次元配列へpush 
    output.push([double, triple]); 
         
  } 
   
  //計算結果を格納した２次元配列を一気にsetValues 
  sheet.getRange(1, 2, 6, 3).setValues(output); 
   
}

getValues、最終行get、 setValuesで ⇒ 計３回

[i][1]的な記述だけになったので、for文の式も修正。

１９回が３回まで減らせることを考えると、こういう書き方を癖にするべきなんだろうなと思う。

更に省略

メソッドではなく、工夫でget.lastRowも省略する。

今回の計算は、getValuesした要素全てが対象だから、for_of文で対応可能。もしくは= sheet.getLastRow();を= data.length;としても代用可能。

乱暴だけど、for文の条件式の終わりを大きな数字にしておいて、if文を使って「空欄だったらbreak」させることでも省略できる。

最終行の取得を省略することで、最初１９回だったスプレッドシートの呼び出し回数が、２回まで省略できた∩(´∀｀)∩ﾜｧｲ♪

検討

スクリプトの最初と最後にLogger.logを挟んで実行して、開始時間と終了時間を表示させたところ、１９回呼び出しのスクリプトは約1.7秒、２回呼び出しのスクリプトは約0.4秒で完了していた。

データ量が増えると、かなりの時間差が発生しそう・・・気を付けます(m｡_ _)/ ﾊﾝｾｲ

おしまい