GASの処理速度が遅すぎて困っています。

Question

実現したいこと

ここに実現したいことを箇条書きで書いてください。

• 処理速度を上げたい

前提

Google Apps Scriptsで日付を参照し貼り付け処理を行おうとしています。
動きはするのですが処理速度が遅い状態となっています。

下記がプログラムです。
下記の”pasteDataToMatchingDateHelper("シート1", "W5", 9, 7, 9);”に 2443msかかっていたいました。” function pasteDataToMatchingDateHelper～targetRowRange.setValues([sourceValues]);”に51186msかかっていました。
全体として303309msの時間を要しました。

function pasteDataToMatchingDate55() {
pasteDataToMatchingDateHelper("シート1", "W5", 9, 7, 9);
pasteDataToMatchingDateHelper("シート1", "AL5", 24, 7, 24);
pasteDataToMatchingDateHelper("シート1", "BP5", 54, 7, 54);
pasteDataToMatchingDateHelper("シート1", "CE5", 69, 7, 69);
pasteDataToMatchingDateHelper("シート1", "DI5", 99, 7, 99);
}

function pasteDataToMatchingDateHelper(sheetName, dateCellRef, startCol, startRow, targetCol) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName(sheetName);
var sourceRange = sourceSheet.getRange(startRow, startCol, 1, 15);
var dateCell = sourceSheet.getRange(dateCellRef);
var dateValue = dateCell.getValue();
var targetSheet = ss.getActiveSheet();
var targetRange = targetSheet.getRange("G12:G");
var targetValues = targetRange.getValues();
var targetDates = targetValues.flat();
var matchingIndex = targetDates.findIndex(function(date) {
return date instanceof Date && date.getTime() == dateValue.getTime();
});
if (matchingIndex >= 0) {
var matchingRow = matchingIndex + 12;
var sourceValues = sourceRange.getValues()[0];
var targetRowRange = targetSheet.getRange(matchingRow, targetCol, 1, sourceValues.length);
targetRowRange.setValues([sourceValues]);
}
}

Comments

[YAmaGNZ]

現在どれくらいの時間がかかっていて、どの処理にどれくらいの時間がかかっているのか確認されましたか？

[hasa1458]

pasteDataToMatchingDateHelper("シート1", "W5", 9, 7, 9);に 2443msかかっていたいました。
function pasteDataToMatchingDateHelper～targetRowRange.setValues([sourceValues]);に51186msかかっていました。
全体として303309msの時間を要しました。

[YAmaGNZ]

＞pasteDataToMatchingDateHelper("シート1", "W5", 9, 7, 9);に 2443ms
＞function pasteDataToMatchingDateHelper～targetRowRange.setValues([sourceValues]);に51186ms

pasteDataToMatchingDateHelperが2443msなのにpasteDataToMatchingDateHelper内の実行に51186msとなっているのはどういうことなのでしょう？
5回実行するpasteDataToMatchingDateHelperの合計で51186msかかっていたということでしょうか？

また実行時間にはG12:Gにある日付の件数と探している日付が何番目にあるかで変化するので、そちらの数はいくつくらいなのでしょうか？
とりあえずG1000まで日付を用意し、全部最後の日付にマッチするよう実行してみましたがトータルで4742msとなりました。

[hasa1458]

>pasteDataToMatchingDateHelperが2443msなのにpasteDataToMatchingDateHelper内の実行に51186msとなっているのはどういうことなのでしょう？
5回実行するpasteDataToMatchingDateHelperの合計で51186msかかっていたということでしょうか？
はい、合計値が51186msとなっています。

>また実行時間にはG12:Gにある日付の件数と探している日付が何番目にあるかで変化するので、そちらの数はいくつくらいなのでしょうか？
1000の日付があります。一応補足ですが最後の日付ではなく("シート1", "W5", 9, 7, 9)のうち"w5"の日付と一致する列に貼り付けを行う仕様となっています。

[YAmaGNZ]

>1000の日付があります。一応補足ですが最後の日付ではなく("シート1", "W5", 9, 7, 9)のうち"w5"の日付と一致する列に貼り付けを行う仕様となっています。
一致するものを探すときに一番時間がかかるのが最終データになるのでなるべく時間のかかる方法で試してみました。

Answer 1

function pasteDataToMatchingDate55() {
  console.time('全体実行時間');

  pasteDataToMatchingDateHelper("シート1", "W5", 9, 7, 9);
  pasteDataToMatchingDateHelper("シート1", "AL5", 24, 7, 24);
  pasteDataToMatchingDateHelper("シート1", "BP5", 54, 7, 54);
  pasteDataToMatchingDateHelper("シート1", "CE5", 69, 7, 69);
  pasteDataToMatchingDateHelper("シート1", "DI5", 99, 7, 99);

  console.timeEnd('全体実行時間');
}

function pasteDataToMatchingDateHelper(sheetName, dateCellRef, startCol, startRow, targetCol) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sheetName);
  var sourceRange = sourceSheet.getRange(startRow, startCol, 1, 15);
  var dateCell = sourceSheet.getRange(dateCellRef);
  var dateValue = dateCell.getValue();
  var targetSheet = ss.getActiveSheet();
  var targetRange = targetSheet.getRange("G12:G");
  var targetValues = targetRange.getValues();
  var targetDates = targetValues.flat();

  console.log(`データ領域 = ${targetSheet.getDataRange().getA1Notation()}`);
  console.log(`sourceRange = ${sourceRange.getA1Notation()} : dateCell = ${dateCell.getA1Notation()} : targetDates = ${targetDates.length}`);

  console.time('日付検索時間');
  var matchingIndex = targetDates.findIndex(function(date) {
    return date instanceof Date && date.getTime() == dateValue.getTime();
  });
  console.timeEnd('日付検索時間');

  console.time('データコピー時間');
  if (matchingIndex >= 0) {
    var matchingRow = matchingIndex + 12;
    var sourceValues = sourceRange.getValues()[0];
    var targetRowRange = targetSheet.getRange(matchingRow, targetCol, 1, sourceValues.length);
    targetRowRange.setValues([sourceValues]);
  }
  console.timeEnd('データコピー時間');
}

こちらを実行したらどのようなログが出力されますか？

---

当方で適当にシート（行は1001行、列はDIまで、1001行目は12行目～1000行目の合計行）を作成し、上記のコードを動作させたところ

お知らせ 実行開始
情報 データ領域 = A1:DI1001
情報 sourceRange = I7:W7 : dateCell = W5 : targetDates = 990
デバッグ 日付検索時間: 3ms
デバッグ データコピー時間: 118ms
デバッグ 単体実行時間: 1104ms
情報 データ領域 = A1:DI1001
情報 sourceRange = X7:AL7 : dateCell = AL5 : targetDates = 990
デバッグ 日付検索時間: 2ms
デバッグ データコピー時間: 139ms
デバッグ 単体実行時間: 1110ms
情報 データ領域 = A1:DI1001
情報 sourceRange = BB7:BP7 : dateCell = BP5 : targetDates = 990
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 100ms
デバッグ 単体実行時間: 1299ms
情報 データ領域 = A1:DI1001
情報 sourceRange = BQ7:CE7 : dateCell = CE5 : targetDates = 990
デバッグ 日付検索時間: 2ms
デバッグ データコピー時間: 102ms
デバッグ 単体実行時間: 970ms
情報 データ領域 = A1:DI1001
情報 sourceRange = CU7:DI7 : dateCell = DI5 : targetDates = 990
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 64ms
デバッグ 単体実行時間: 839ms
デバッグ 全体実行時間: 5335ms
お知らせ 実行完了

といった感じで
pasteDataToMatchingDateHelperの単体での実行時間は大体800ms～1300ms、処理全体で約5500msといった感じになっています。
シートが2000行の場合は
var targetRange = targetSheet.getRange("G12:G");
このデータ取得に時間がかかるみたいでpasteDataToMatchingDateHelperの単体での実行時間は2500msくらいになりました。

---

追記

G12:Gに関しては一定でコード内でも変化しないみたいですし、下記のように開始時に1回取得して関数に引数としてデータを渡すようにしてはどうでしょうか？

function pasteDataToMatchingDate55() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getActiveSheet();
  var targetRange = targetSheet.getRange("G12:G");
  var targetValues = targetRange.getValues();
  var targetDates = targetValues.flat();

  pasteDataToMatchingDateHelper("シート1", "W5", 9, 7, 9, targetSheet, targetDates);
  pasteDataToMatchingDateHelper("シート1", "AL5", 24, 7, 24, targetSheet, targetDates);
  pasteDataToMatchingDateHelper("シート1", "BP5", 54, 7, 54, targetSheet, targetDates);
  pasteDataToMatchingDateHelper("シート1", "CE5", 69, 7, 69, targetSheet, targetDates);
  pasteDataToMatchingDateHelper("シート1", "DI5", 99, 7, 99, targetSheet, targetDates);

}

function pasteDataToMatchingDateHelper(sheetName, dateCellRef, startCol, startRow, targetCol, targetSheet, targetDates) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sheetName);
  var sourceRange = sourceSheet.getRange(startRow, startCol, 1, 15);
  var dateCell = sourceSheet.getRange(dateCellRef);
  var dateValue = dateCell.getValue();

  var matchingIndex = targetDates.findIndex(function(date) {
    return date instanceof Date && date.getTime() == dateValue.getTime();
  });

Comments

[hasa1458]

提供していただいたコードを実行したところ
お知らせ 実行開始
情報 データ領域 = A1:AD315
情報 sourceRange = I7:W7 : dateCell = W5 : targetDates = 989
デバッグ 日付検索時間: 0ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = X7:AL7 : dateCell = AL5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = AM7:BA7 : dateCell = BA5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = BB7:BP7 : dateCell = BP5 : targetDates = 989
デバッグ 日付検索時間: 0ms
デバッグ データコピー時間: 0ms
情報 データ領域 = A1:AD315
情報 sourceRange = BQ7:CE7 : dateCell = CE5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = CF7:CT7 : dateCell = CT5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = CU7:DI7 : dateCell = DI5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = DJ7:DX7 : dateCell = DX5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 0ms
情報 データ領域 = A1:AD315
情報 sourceRange = DY7:EM7 : dateCell = EM5 : targetDates = 989
デバッグ 日付検索時間: 1ms
デバッグ データコピー時間: 1ms
情報 データ領域 = A1:AD315
情報 sourceRange = EN7:FB7 : dateCell = FB5 : targetDates = 989
デバッグ 日付検索時間: 0ms
デバッグ データコピー時間: 1ms
デバッグ 全体実行時間: 4556ms
お知らせ 実行完了
といった感じになりました。
やはり
var targetRange = targetSheet.getRange("G12:G");
の取得に時間がかかってしまうのですね。

[YAmaGNZ]

データコピー時間が1msということは一致している日付がなかったのでしょうか？

＞デバッグ 全体実行時間: 4556ms
提示されたコードに実行時間のログを追加しただけなのですが、全体で4500msほどしかかかっていないようですが・・・
実行結果は貴方のコードと同じですか？

またpasteDataToMatchingDateHelperが10回呼ばれているようですがどういうことですか？
提示されたコードとは違うものを実行しているのですか？
質問にあった51186msや303309msとはいったい何の時間なのでしょうか？