【GAS】二次元配列のデータ取得方法とループ処理

Question

前提・実現したいこと

スプレッドシート内に「入力シート」「マージシート」の2種があります。
「入力シート」は、「部署」～「転記処理」までの6列分×MAX10行で
1ブロック。

GASで以下の２つの処理をしたいです。
①「入力シート」の内容を「マージシート」に転記
②上記①の処理が完了したら、「入力シート」に「転記済」を追記

①の処理の際、二次元配列でデータ取得したいです。
8行目スタートで、C列～G列までの1行分をまとめて取得。
ループ処理で1行ずつ下に移動させ、取得させる想定です。

添付イメージだと、
ループ処理はIndex0からIndex4まで計5回処理の想定。

<入力シートイメージ>

<マージシートイメージ>

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

下のエラーが出てしまいます。
1つ目の8行目のデータ取得はできているのですが、
２つ目の9行目のデータ取得が失敗しています。

TypeError: Cannot read property '0' of undefined
copyToMerge1	@ 入力シート→マージシートへの転記ver1.1.gs:24

該当のソースコード

以下、コード全文記載します。

function copyToMerge1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mySheet1 = ss.getSheetByName("入力シート");
  const mySheet2 = ss.getSheetByName("【●●】マージ");
  const mySheet3 = ss.getSheetByName("【■■】マージ");

  //【ブロック内のループ】
  //件数データありの最終行(InMaxRow)、転記処理対象の行数(targetRows)の取得
  const InMaxRow = mySheet1.getRange(18, 4).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  const MaxIndex = InMaxRow - 8;
  console.log("MaxIndex " + MaxIndex);　//Index最大値は4の想定

  //「部署」～「所要時間」までの5列分をgetRange(行、列、▲行分、■列分) で
  // 二次元配列(Values)として取得
  // ループ処理は0スタートでrがMaxIndex以下になるまで繰返し。
  for (r = 0; r <= MaxIndex; r++) {
    const myDetaRange = mySheet1.getRange(8 +r, 3, 1, 5);
    console.log("myDetaRange " + myDetaRange.getA1Notation());
    const values = myDetaRange.getValues();

    //二次元配列(Values)内の項目は「部署」「件数」「開始時刻」「終了時刻」「所用時間」の5項目
    let Branch = values[r][0];
    let Num = values[r][1];
    let startTime = values[r][2];
    let endTime = values[r][3];
    let length = values[r][4];
    console.log("values " + values);

    //【マージシートへの転記処理】
    //▼「×××マージ」シート(mySheet2)の件数(E列)にデータありの最終行(OutMaxRow1)の取得
    const OutMaxRow1 = mySheet2.getRange(mySheet2.getMaxRows(), 5).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
    console.log("OutMaxRow1 " + OutMaxRow1);　//10が出る想定

    //▼作業日(入力シートセルE3)の内容を「マージシート(mySheet2)」のB列に転記
    const myDate = mySheet1.getRange("E2").getValue();
    console.log("myDate " + myDate);
    mySheet2.getRange(OutMaxRow1 + 1 + r, 2, 1, 1).setValue(myDate); 1

    //▼二次元配列(Values)の内容(5項目)を「マージシート(mySheet2)」のD列～G列に転記
    //getRange(行、列、▲行分、■列分) 
    mySheet2.getRange(OutMaxRow1 + 1 + r, 4, 1, 5).setValues(values);

    //▼マージシートへの転記結果を「入力シート(mySheet1)」の「転記処理」欄に追記する
    //getRange(行、列、▲行分、■列分)       
    mySheet1.getRange(8 + r, 3 + 5, 1, 1).setValue("転記済");
  }

  //▼ダイアログMsgの表示
  Browser.msgBox("「マージシート」への転記処理が完了しました。\n「転記処理」列に「転記済」表示があれば問題なく転記処理がされています。", Browser.Buttons.OK);
}

試したこと

ログを確認すると、2回目のループ処理のデータ取得範囲myDetaRangeは、
myDetaRange C9:G9　となっていますが、
そこで処理が止まり、以降の配列データの取得ができていないようです。

以前に別件照会した、同エラーが出た際の対処→別件照会①も参照し、
forループの範囲指定を確認しましたが、今回のケースではIndex最大値の[4]
を超えていないのではないかと思っています。

<<教えてほしいこと>>
前回ご回報いただいた別件照会①の2/2の「さら問い」の配列の作り方と
ループ処理の関係が理解しきれていないようです。→別件照会①
現コードのどこに誤りがあるのか、どのように修正すればいいのかが
わかりません。

配列(Values)のデータ取得範囲myDetaRangeの指定の際、
第3引数? を1行分としている点が誤りですか?
1行ずつループ処理させたい場合は、r行分まとめて範囲指定
が正しいですか??

もし、データ取得範囲myDetaRangeを1行とする場合なら、
都度配列(Values)に取得したデータを投入していかないと
配列(Values)内のIndexが0、1、2、、、とならない、
ということですか?

何度もご教示いただいているのに習得しきれておらず本当に申し訳ありません。
次こそは、自力解決できるようになりたいので、お手数をおかけして
大変申し訳ありませんが、回答は急ぎませんのでビギナーでも理解できる
レベルでの解説をいただけないでしょうか?

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

本件、別照会で1度照会しています。→別件照会②

今回のコードでは、ループはブロック内のループのみ記載ですが、
前回照会ではブロック間とブロック内の２つのループをさせる想定の
コードでした。
前回回答内容をもとにコードの自力修正を試みましたが、私の知識が
足りず自力解決できませんでした。

そのため、コードを分解し、まずはブロック内で正しく処理が走るよう
１つずつ修正を試みようと思っている次第です。
今回の問題が解決できたら、次ステップとして、
ブロック②、③、、、とブロック間で連続して処理が走るように
拡張させていきたいと思っています。

(参考) 回答を受けて修正したコード（追記）

回答を受け、コードを以下のとおり修正しましたので参考掲載します。
無事処理が走りました。
アドバイスありがとうございました。大変助かりました。

function copyToMerge1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mySheet1 = ss.getSheetByName("キイロ入力シート");
  const mySheet2 = ss.getSheetByName("【●●】マージ");
  const mySheet3 = ss.getSheetByName("【■■】マージ");

//★ブロック間のループ
//「部署」～「所要時間」までの6列×転記処理対象最終行(MaxRows)分
//で1ブロックとする。
//ブロックの開始col(blockCol)はC列(3col)とし、6colとばしで7ブロック目(39col目)まで
  for (blockCol = 3; blockCol <= 6*6 +3; blockCol +=6) {

  //★ブロック内のデータ取得
  //件数データありの最終行(InMaxRow)、転記処理対象最終行(MaxRows)の取得
  const InMaxRow = mySheet1.getRange(18, blockCol+1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  const MaxRows = InMaxRow - 7;
  console.log("MaxRows " + MaxRows);　//5の想定

  //「部署」～「所要時間」までの5列分×MaxRows行分をgetRange(行、列、▲行分、■列分) で
  // 二次元配列(Values)として1回でまとめて取得
    const myDetaRange = mySheet1.getRange(8, blockCol, MaxRows, 5);
    console.log("myDetaRange " + myDetaRange.getA1Notation());
    const values = myDetaRange.getValues();
    console.log("values " + values);

    //★マージシートへの転記処理
    //▼「×××マージ」シート(mySheet2)の件数(E列)にデータありの最終行(OutMaxRow1)の取得
    const OutMaxRow1 = mySheet2.getRange(mySheet2.getMaxRows(), 5).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
    console.log("OutMaxRow1 " + OutMaxRow1);　//10が出る想定

    //▼作業日(入力シートセルE3)の内容を「マージシート(mySheet2)」のB列に転記
    const myDate = mySheet1.getRange("E2").getValue();
    console.log("myDate " + myDate);
    mySheet2.getRange(OutMaxRow1+1, 2,　MaxRows, 1).setValue(myDate); 1

    //▼二次元配列(Values)の内容(5項目)を「マージシート(mySheet2)」のD列～G列に転記
    //getRange(行、列、▲行分、■列分) 
    mySheet2.getRange(OutMaxRow1 +1, 4, MaxRows, 5).setValues(values);
  
    //▼マージシートへの転記結果を「入力シート(mySheet1)」の「転記処理」欄に追記する
    //getRange(行、列、▲行分、■列分)       
    mySheet1.getRange(8, blockCol+5, MaxRows, 1).setValue("転記済");
   } 

  //▼ダイアログMsgの表示
  Browser.msgBox("「●●マージシート」への転記処理が完了しました。\\n入力シートの「転記処理」列に「転記済」表示があれば問題なく転記処理がされています。", Browser.Buttons.OK);
}

Answer 1

・エラーの原因

19行目～21行目あたり

  const myDetaRange = mySheet1.getRange(8 +r, 3, 1, 5);
  console.log("myDetaRange " + myDetaRange.getA1Notation());
  const values = myDetaRange.getValues();

上の引用部分の１行目で、myDetaRange は、5列1行のデータになっています。
（getRange()の第３引数が「１」であるため）

myDetaRange は1行しか指定されていないため、
const values = myDetaRange.getValues();
とすると、valuesは values[0][] までしかデータが入っていないことになります。


その後（23行目以降で）

    //二次元配列(Values)内の項目は「部署」「件数」「開始時刻」「終了時刻」「所用時間」の5項目
    let Branch = values[r][0];
    let Num = values[r][1];
    let startTime = values[r][2];
    let endTime = values[r][3];
    let length = values[r][4];

としていますが、上述の通り、valuesはループ毎に毎回1行分しかデータが入っていない（＝ values[0][] までしかデータがない）のですから、
forループが1回まわって、r が 1になったとき
let Branch = values[1][0];
を実行したところで、存在しないインデックス（１）にアクセスすることになり、エラーが発生しています。

---

・修正案

ループごとにvaluesは1行分ずつしか取得していないのですから、
下記のように、values[0][～]　とします。

    //二次元配列(Values)内の項目は「部署」「件数」「開始時刻」「終了時刻」「所用時間」の5項目
    let Branch = values[0][0];   // [r] -> [0] に修正。以下4行同じ
    let Num = values[0][1];
    let startTime = values[0][2];
    let endTime = values[0][3];
    let length = values[0][4];

　
また、33行目以降で、　ループごとにマージシートの最終行（OutMaxRow1）を取得しているにもかかわらず
ループカウンタ r を用いてさらに行を加算しているので
このままだとループが回る度にマージシートの転記先の前に空白行ができてしまいます。
したがって、下記のように、マージシートに転記する部分では、r の加算は不要です。

（31行目以降）

    //【マージシートへの転記処理】
    //▼「×××マージ」シート(mySheet2)の件数(E列)にデータありの最終行(OutMaxRow1)の取得
    const OutMaxRow1 = mySheet2.getRange(mySheet2.getMaxRows(), 5).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
    console.log("OutMaxRow1 " + OutMaxRow1);　//10が出る想定

    //▼作業日(入力シートセルE3)の内容を「マージシート(mySheet2)」のB列に転記
    const myDate = mySheet1.getRange("E2").getValue();
    console.log("myDate " + myDate);
-   mySheet2.getRange(OutMaxRow1 + 1 + r, 2, 1, 1).setValue(myDate); 1
+   mySheet2.getRange(OutMaxRow1 + 1 , 2, 1, 1).setValue(myDate); 1

    //▼二次元配列(Values)の内容(5項目)を「マージシート(mySheet2)」のD列～G列に転記
    //getRange(行、列、▲行分、■列分) 
-   mySheet2.getRange(OutMaxRow1 + 1 + r, 4, 1, 5).setValues(values);
+   mySheet2.getRange(OutMaxRow1 + 1 , 4, 1, 5).setValues(values);

---

コメントの質問に対する回答

＜さら問い１＞

23行目以降でValues[0][～]となっているので、
ループ2回目がまわる時、配列Valuesの中にはループ1回目の
データが残っている状態かと思います。
データが残っていても2回目のループ処理で、1回目ループの
データは上書きされるので残っていても問題なし、という
理解であっていますか?

→ 厳密には、valuesはローカル変数であり、(少なくとも理論的には）ループの度に生成されています。
２回目のループにおけるvaluesは、１回目のループのvaluesとは、名前が一緒ですが別物です。
したがって２回目のループで、"valuesの中に１回目のデータが残っているが上書きされる"ということではありません。
「前と違う、新しいvaluesという名前に、次の行の配列データが結び付けられる」と言った方が適切かもしれません。
いずれにしても１回前に取得したデータは影響しないため、問題はないという結論に変わりはありません。

<さら問い2>

今回ブロック内の処理をする際、
a)5列1行ずつデータを取得し、配列を作る方法
b)複数行分をまとめて一度で配列を作る方法
どちらの方法がおススメでしょうか?　

b) の方がおすすめですね。
理由：少しでも処理を速くするためです。
getRange ／ getValue[s] ／ setValue[s] のようなAPIの実行には時間がかかります。
（ここでの「API」とは、SpreadsheetApp. ～ で呼び出す関数全般と考えてもらってよいです）
一方、一度配列として読み込んだデータを加工する処理（push等を含む）は、APIの実行に比べ、たいてい格段に短く済みます。

getValuesを使うときは、なるべく広い範囲をまとめて配列として読み込む
↓
配列データ加工（pushやmap等で新しい配列を作る等）
↓
まとめてsetVauesで書き込み

というようにして、API呼び出し回数をなるべく減らします。

たとえば100行のデータを１行１行getValueで読み込んで、加工し、１行ずつsetValueで書き込むと、最大200回API呼び出しすることになりますが、
100行まとめてgetValuesで読み込み、配列データとして加工（pushやmap等で配列を作る処理含む）し、まとめてsetVauesで書き込めば、API呼び出しは２回で済みます。

Comments

[donguriko]

qnoirさま
お忙しい中、早々のご回答本当にありがとうございます。

myDetaRangeとして5列1行ずつデータ取得していく場合は、
ループ1回目もループ2回目も配列Valuesは常にデータは1行分
しかないので、行インデックスは常に[0]なのですね。

<さら問い1>
素人質問で申し訳ありません。
23行目以降でValues[0][～]となっているので、
ループ2回目がまわる時、配列Valuesの中にはループ1回目の
データが残っている状態かと思います。
データが残っていても2回目のループ処理で、1回目ループの
データは上書きされるので残っていても問題なし、という
理解であっていますか?

<さら問い2>
別解として、5列1行ずつでループをまわすのではなく、
開始行8行目固定とし、複数行分をまとめて取得し、一度に
5列×最終行までの複数行分の二次元配列を作る方法も
可能かと思います。

今回ブロック内の処理をする際、
a)5列1行ずつデータを取得し、配列を作る方法
b)複数行分をまとめて一度で配列を作る方法
どちらの方法がおススメでしょうか?　
もし、qnoirさまでしたらどちらにされますか?
また、どうしてなのかについても考え方のアドバイスを
いただけないでしょうか?

照会した当初、a)とb)が混乱気味でしたが、回報内容を読込みながら
頭を再整理していたら、もしかするとブロック内はb)とした方が、
実はよいのではないかという気になりました。
どのアプローチがよいのかの考え方の参考とさせていただきたいです。

以前にもアドバイスいただいたように、後々の修正メンテ等を考えると
コードは簡潔に済む方がよいので、やはりおススメはb)でしょうか?

[退会済みユーザー]

追加質問に対して回答欄に追記しました。
実は最初質問のコードを見た時にはまとめて複数行配列にした方が効率が良いかもと思ったのですが、
エラーとなっている原因を明らかにする説明を優先したので回答内の修正案は１行１行処理するやり方になっています。
以前に比べると、きちんとconsole.logで内容確認されていたり、コードの方向性もしっかりされている印章で、成長を感じます。まとめて書くやり方については、回答を出すこともできますが、ひとまず御自分で考えられてはいかがでしょうか。どうしてもわからなければもちろんヘルプします。

[donguriko]

qnoirさま
お忙しい中、さら問いへの回答ありがとうございます。
配列やループについての理解が追い付かず、何度も何度も質問してしまい
その度に本当に心苦しかったのですが、今回のqnoirさまからの優しいコメント、
すごくうれしいです!!
ここまで折れずにGAS独学がなんとか続けられているのは、どんなに初歩的な
質問でも毎回丁寧に解説をしてくださるqnoirさまのおかげです。
感謝しかないです。

b) の方がおすすめとのことなので、まずは自力でb)のコードを
考えてみます。
もし、又躓いたり、アドバイスをいただきたい時にはご相談させてください。
エラーの原因解消のレクチャーのため、a)方式におつきあいいただき感謝です。
おかげさまでa)とb)　両方のコードの書き方の勉強ができます。
いつも、親身になってアドバイスをくださり、本当にありがとうございます。

<相談>
もし、自力で書いたコードでなんとか想定どおりの処理が走った場合、
同じことで悩んでいる方のために、本質問に自力記載したコードを
追記しておいた方がよいでしょうか?
追記した方がよい場合は、記載する欄は下の「解決方法」欄で
よいですか?

[退会済みユーザー]

後から見た人の参考になると思うので、追記することをお勧めします。
記載する欄は、質問欄の続きがよいかと思います（コメント欄だとソースコードをフォーマットして貼り付けることができないため）。
記載するか否か、どこに記載するかは最終的にはお任せします。