GAS 処理時間が長い

Question

実現したいこと

ﾀﾞﾝﾎﾞｰﾙシートの10更新ボタンを押したら、No.10の範囲（B:F）の９行目から下の値（B9:F25）を、送信履歴シートのD:Hに貼り付けたい
・”９行目から下の値（B9:F25）”は、その時によって行の増減があります
・D列の製品名は空白の場合もあります
・ﾀﾞﾝﾎﾞｰﾙシートには10だけでなく右隣の37のように同じ表が連なっています

まだGASは組めていないが、送信履歴シートの
Aに送信（ボタンを押した）日付
BにﾀﾞﾝﾎﾞｰﾙシートA1の値（今回はﾀﾞﾝﾎﾞｰﾙ）
CにﾀﾞﾝﾎﾞｰﾙシートのNoの値（今回は10）
も送信できるようにしたい

発生している問題・分からないこと

・処理に5分もかかってしまう

該当のソースコード

 function getCurrentRegion() {
  // アクティブなシートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var jo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ﾀﾞﾝﾎﾞｰﾙ');
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('送信履歴');
  var purasu1 = sheet1.getLastRow() + 1; //すでにセル内にあるデータを上書きしないように1を足す

console.time("Inefficient Loop3");

  // 基準セルを指定 (ここではA1セル)
  var startingCell = jo.getRange("b9"); //b9が基準 //B9
  var startRow = startingCell.getRow();//行
  var startColumn = startingCell.getColumn();//列

console.timeEnd("Inefficient Loop3");

  
console.time("Inefficient Loop");

  // 現在のシートのデータ範囲を取得

 var data = jo.getDataRange().getValues();//ﾀﾞﾝﾎﾞｰﾙのセル範囲の値（values）を取得

console.timeEnd("Inefficient Loop");


console.time("Inefficient Loop6");

  // 空白セルにぶつかるまで行を拡張する
  var lastRow = startRow;//行
  
console.timeEnd("Inefficient Loop6");



console.time("Inefficient Loop5");

  while (lastRow <= sheet1.getLastRow() && data[lastRow - 1][startColumn - 1] !== "") {
    lastRow++;}//b9の行と送信履歴の最後の行大きさ判断、データ範囲の最終行の一個したみて空白確認繰り返す


 console.timeEnd("Inefficient Loop5");


 console.time("Inefficient Loop2");

  //列は固定
    var lastColumn = startColumn+5;//b9から＋5列のところ

 console.timeEnd("Inefficient Loop2");

 console.time("Inefficient Loop8");
 
 var currentRegion = jo.getRange(startRow, startColumn, lastRow - startRow, lastColumn - startColumn).getValues();
//上の値をもらう

 console.timeEnd("Inefficient Loop8");


console.time("Inefficient Loop9");

 var lastRow2 = sheet1.getLastRow();//送信履歴の最初から最後の行

 console.timeEnd("Inefficient Loop9");

  console.time("Inefficient Loop3");

 sheet1.getRange(lastRow2+1, 4, currentRegion.length, currentRegion[0].length).setValues(currentRegion);//送信履歴の最終行にあげる

 console.timeEnd("Inefficient Loop3");


  // 結果をログに表示
  Logger.log(currentRegion);
}

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

動作は正常ですが、処理時間は変わらなかった

補足

特になし

Comments

[int32_t]

console.time*() で調査した形跡がありますが、どこが遅かったですか?

[aneig]

Loop : 35452ms
Loop5: 204701ms
Loop9: 11252ms
上記でかなり時間がかかってしまっています
その他は1桁です

Answer 1

例えば、次のようなコードをお試しいただき
希望の動作と異なるようならコメントください

function getCurrentRegion() {
  // 履歴データの基準となるセル
  const startCellName = 'B9';
  // const startCellName = 'G9';

  // 品名（ダンボール）のセル
  const itemCellName = 'A1';

  // 品名Noの行
  const itemNumberRow = 4;

  // 履歴データの列数
  const numColumns = 5;

  // 送信履歴データの記入開始列
  const logStartColumn = 4;

  // それぞれのシートを取得
  const itemSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ﾀﾞﾝﾎﾞｰﾙ');
  const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('送信履歴');

  // ダンボールシートの基準セルの行番号と列番号とダンボールNo
  const startCell = itemSheet.getRange(startCellName);
  const itemStartRow = startCell.getRow(); //行
  const itemStartColumn = startCell.getColumn();//列

  // 履歴見出しを作成
  const date = Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd');
  const item = itemSheet.getRange(itemCellName).getValue();
  const itemNumber = startCell.offset(itemNumberRow - itemStartRow, 0).getValue();
  const titles = [date, item, itemNumber];

  // ダンボールシートの基準セルからデータが連続する最下行までの行数
  const itemLastRow = startCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  const numRows = itemLastRow - itemStartRow + 1;

  // 送信履歴シートの最終行
  const logLastRow = logSheet.getLastRow();

  // 履歴データを取得
  const values = itemSheet
    .getRange(itemStartRow, itemStartColumn, numRows, numColumns)
    .getValues();

  // 履歴見出しを記入
  logSheet
    .getRange(logLastRow + 1, 1, 1, titles.length)
    .setValues([titles]);

  // 履歴データを記入
  logSheet
    .getRange(logLastRow + 1, logStartColumn, values.length, values[0].length)
    .setValues(values);

  // 結果をログに表示
  Logger.log(values);
}

Comments

[aneig]

希望の動作でとてもとても早く動きました、、、大変勉強になります
ありがとうございました泣

Answer 2

  while (lastRow <= sheet1.getLastRow() && data[lastRow - 1][startColumn - 1] !== "") {
    lastRow++;}

上記コードを、下記のように変更すると処理時間が改善するかもしれません。

  const sheetLastRow = sheet1.getLastRow();
  while (lastRow <= sheetLastRow && data[lastRow - 1][startColumn - 1] !== "") {
    lastRow++;
  }

シートへの読み書きは遅いので、可能なら避けるかまとめましょう。
他の部分を改善するのは難しそうです。

Comments

[aneig]

少しはやくなりました！
読み書き以外の方法も考えてみます、ありがとうございました。