(GAS)シート間で転記で実行時間を短くする方法

実現したいこと

シート間で転記するものをgasで実行してみると実行時間が長くタイムアウトしてしまいます。
実行時間を短くしたいです。

前提

スプレッドシートには注文番号が入力された転記元シート(CSV売上)と注文番号が入力された転記先シート(アクティブシート)があります。
転記先シートにある注文番号を転記元シートから探して
見つけたら転記元シートの同じ注文番号のある行を転記先シートの同じ注文番号がある行の18列横に転記したいです。
自分なりになんとか作成はしたのですが、実行中にタイムアウトしてしまいます。
転記を効率化したく作り始めました。
非効率なコードを書いてしまっているのではと思い調べましたが、何が実行時間を遅くしているのか分からず、ご教授頂きたい次第でございます。
宜しくお願い致します。

該当のソースコード

function transfer2() {               //CSV売上シートから転記
  
  //使用するシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet(); //アクティブなスプレッドシートを取得
  const original_sheet = ss.getSheetByName('CSV売上'); //転記元のシートを取得
  const posted_sheet = ss.getActiveSheet(); //転記先のシート(アクティブシート)を取得
  const or_lastRow = original_sheet.getLastRow();  //転記元シートの最終行を取得
  const or_lastCol = original_sheet.getLastColumn();  //転記元シートの最終列を取得
  const po_lastRow = posted_sheet.getLastRow();  //転記先シートの最終行を取得
  const po_lastCol = posted_sheet.getLastColumn();  //転記先シートの最終列を取得


   //繰り返し
  for (let i=1; i<=po_lastRow; i++){  //変数iを定義
    let po_orderNo = posted_sheet.getRange(i,4).getValue(); //アクティブシートのD列(注文番号)を11行目〜最終行まで順番に見ていく

      for (let j=1; j<=or_lastRow; j++){  //変数jを定義
        let or_orderNo = original_sheet.getRange(j,2).getValue(); //転記元シートのB列(注文番号)を11行目〜最終行まで順番に見ていく

      //条件分岐
      if (po_orderNo == or_orderNo){  //アクティブシートD列と転記元シートB列が同じ値の場合
        let original_values = original_sheet.getRange(j,1,1,23).getValues(); //転記元の範囲(A〜W列)のデータを取得
        posted_sheet.getRange(i,18,1,23).setValues(original_values); //取得したデータをアクティブシートの18列横に転記
    }
  }

試したこと

転記元の範囲(A〜W列)のデータを取得をgetValueで取得していたものをgetValuesで取得するようにしたがまだタイムアウトしてしまう。

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

行動規範の内容に同意します

回答1件

ベストアンサー

高速化するにはスプレッドシートへのアクセスを極力減らすべきです。

例えばoriginal_sheetに関しては実行中に変化しないわけですから

 let original_datas = original_sheet.getRange(1,1,or_lastRow,or_lastCol).getValues();

とデータを一括で読み込んでしまって

      for (let j=0; j<original_datas.length; j++){  //変数jを定義
        let or_orderNo = original_datas[j][1]; //転記元シートのB列(注文番号)を11行目〜最終行まで順番に見ていく

といった感じでループ内では読み込んだ配列から値を取得するようにするとかです。
posted_sheetのほうも注文番号の部分は変化がなさそうですから、最初に一括で読み込んでしまってはどうでしょうか。

投稿2023/07/06 01:14

YAmaGNZ

総合スコア10555

nori11

2023/07/06 05:41

ありがとうございます！タイムアウトせずに処理を終わらすことができました！ getValuesで範囲取得した方が良いというのは調べていたのですが、data全てを一括で取得しその中で突き合わせるという頭はありませんでした汗明確なコードまでご提示頂き大変助かりました！表計算は多少使うのですが、このようなコーディング？というものはほとんど未経験でして大変勉強になりました。 GASからスプレッドシートへのアクセス数が原因とのことですので、売上項目(売上CS行からの転記数)が多くなっていけばいずれタイムアウトするのかも、、、転記するためにもアクセスをするのだろうと思い、ifで精査したものを一括データとして溜めておいて最後に一括転記ということも可能なのか、今後調べてみようと考えております。ありがとうございました！

行動規範の内容に同意します