Google Apps Script 串刺し演算 シート範囲指定 実行時間を短縮したい

Question

前提・実現したいこと

スプレッドシート初心者でスクリプトを書くのも初めてですのでわかりにくい所あるかもしれませんがよろしくお願いします。

指定シートの指定範囲内の串刺し演算を行いたいです。
作ったソースコードでは時間内に処理ができずもっと簡潔にする必要があるのですが、その方法がわかりませんので、ご教授いただきたく思い投稿させていただきました。

シートが「集計」「マスタ」「テンプレ」「2018.7.29」「2018.7.28」・・・のように続いていきます。
日付の書かれた左から３枚目のシートから最後尾のシートまでの「q3:v11」「ｘ３：ｘ１２」「ｑ１５：ｖ２３」「ｘ１５：ｘ２４」の範囲を「集計」シートに串刺し演算で合計。ということをしたいと考えています。
日付シートは日々追加されていくのでそのタイミングで実行し集計していく形となります。

発生している問題・エラーメッセージ

エラーメッセージ

「スクリプトによるメソッド Range.getValue の使用頻度が高すぎます」

該当のソースコード

ソースコード
function calc_function() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  const erea1_start = "Q3";
  const erea1_end   = "V11";
  
  const erea2_start = "X3";
  const erea2_end   = "X12";
  
  const erea3_start = "Q15";
  const erea3_end   = "V23";
  
  const erea4_start = "X15";
  const erea4_end   = "X24";
  
  const erea_start = "Q3";
  const erea_end   = "X24";
  
  for(var i = 2; i < sheets.length; i++) {
    for(var col = sheets[i].getRange(erea_start).getColumn(); col <= sheets[i].getRange(erea_end).getColumn(); col++){
      for(var row = sheets[i].getRange(erea_start).getRow(); row <= sheets[i].getRange(erea_end).getRow(); row++){
        if( ((sheets[i].getRange(erea1_start).getColumn() <= col && col <= sheets[i].getRange(erea1_end).getColumn()) &&
             (sheets[i].getRange(erea1_start).getRow() <= row && row <= sheets[i].getRange(erea1_end).getRow())) ||
            ((sheets[i].getRange(erea2_start).getColumn() <= col && col <= sheets[i].getRange(erea2_end).getColumn()) &&
             (sheets[i].getRange(erea2_start).getRow() <= row && row <= sheets[i].getRange(erea2_end).getRow())) ||
            ((sheets[i].getRange(erea3_start).getColumn() <= col && col <= sheets[i].getRange(erea3_end).getColumn()) &&
             (sheets[i].getRange(erea3_start).getRow() <= row && row <= sheets[i].getRange(erea3_end).getRow())) ||
            ((sheets[i].getRange(erea4_start).getColumn() <= col && col <= sheets[i].getRange(erea4_end).getColumn()) &&
             (sheets[i].getRange(erea4_start).getRow() <= row && row <= sheets[i].getRange(erea4_end).getRow()))
          )
        {
          if(i == 2){
            sheets[0].getRange(row, col).setValue( 0 );
          }else{
            sheets[0].getRange(row, col).setValue( sheets[0].getRange(row, col).getValue() + sheets[i].getRange(row, col).getValue() );
          } 
        }
      }
    }
  }
    
  return 0;
}

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[macaron_xxx]

コードは```で囲まれた中に記載してください。現状は「ソースコード」となっている部分です。インデントもきっちりしてくださいね。

[hiroshiorz]

ご指摘ありがとうございます。　分かりづらいようでしたらまた、ご指摘お願いいたします。

Answer 1

これが結構早いと思う。（早さに自信はない）

function calc_function() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  const erea1_start = "Q3";
  const erea1_end   = "V11";

  const erea2_start = "X3";
  const erea2_end   = "X12";

  const erea3_start = "Q15";
  const erea3_end   = "V23";

  const erea4_start = "X15";
  const erea4_end   = "X24";

  var formula = "";
  for(var i = 3; i < sheets.length; i++) {
    if (formula === "") {
      formula += "=";
    } else {
      formula += "+";
    }
    formula += "'" + sheets[i].getSheetName() + "'!R[0]C[0]";
  }
  
  var erea1 = sheets[0].getRange(erea1_start + ":" + erea1_end);
  var erea2 = sheets[0].getRange(erea2_start + ":" + erea2_end);
  var erea3 = sheets[0].getRange(erea3_start + ":" + erea3_end);
  var erea4 = sheets[0].getRange(erea4_start + ":" + erea4_end);
  
  // 数式でもいい場合は、後ろのsetValuesがいらない。
  erea1.setFormulaR1C1(formula).setValues(erea1.getValues());
  erea2.setFormulaR1C1(formula).setValues(erea2.getValues());
  erea3.setFormulaR1C1(formula).setValues(erea3.getValues());
  erea4.setFormulaR1C1(formula).setValues(erea4.getValues());
}

Comments

[hiroshiorz]

ありがとうございます！！！
とても早いです！
ものの２〜３秒で集計できてしまいました。

今、７月の１０シート程しかありませんので過去の６月、５月とさかのぼって何ヶ月分までなら同じようなエラーが出ないか見ていきます！

そのまま使えるように書き直していただき本当にありがとうございました。

[macaron_xxx]

そもそも、当初発生しているエラーはFor文の中で何度もAPIが呼ばれていたために起こっていたエラーです。
今回のスクリプトは、シートが1つ増えても1回しかAPI呼び出しが増えません。
（formula += "'" + sheets[i].getSheetName() + "'!R[0]C[0]";の部分）

なので、相当数のシートでも耐えうると思います。

どちらかというと懸念するポイントとしては、FormulaのLimit(40,000)のほうです。
最大範囲でセル数54なので、54×シート数+[ここ以外の数式に含まれる数式のセル数]が40,000を超えるとOUTなので、2年に近づいてくるとどうか…というところでしょうか。

[macaron_xxx]

と思ったらMAXシート数が200なので、余裕のよっちゃんですね。

[hiroshiorz]

apiの呼び出しが時間がかかりエラーが出ているとわかっていてもそれを省略するすべがわからず困っていました。１回しかapiの呼び出しがないのはかなりの短縮でほんとうにありがたいです！

シート２００枚・・・
ありがたいです！！！