【GAS】行方向、列方向 多重ループ処理について

Question

前提・実現したいこと

GASで以下の処理を行いたいです。

スプレッドシート内に「シート①」「シート②」がある。
「シート①」は以下の構成。
部署(C列)～所用時間(G列)の5列分×可変の行数分が1ブロック。
ブロックは①～⑦の全7ブロック。
まずブロック内で1行ずつ処理し、ブロック内の処理が終わったら、
次のブロックで同処理を繰り返し。

<処理1>
「シート①」の内容を「シート②」に転記。

<処理2>
シート②への転記が完了したら、各ブロックのシート①の転記処理列に
「転記済」を追記。

(シート①イメージ)

(シート②イメージ)

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

ブロック内での処理1、2は動いています。
が、ブロック①→ブロック②へのループ処理がうまくできません。
次ブロックにいく時は右方向に6列とばしで処理するようにしたいのですが、
ヨコ方向はそのままで、下方向に1行移動してしまっています。

(ブロック①の処理範囲)
myDetaRange C8:G11

(ブロック②の処理範囲)
myDetaRange C9:G12　　←　I8:M10 が正しい

(ブロック③の処理範囲)
myDetaRange C10:G13　←　O8:S8 が正しい

結果、下のエラーが立ち、処理が止まってしまいます。

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

該当のソースコード

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

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

  //▼「×××」分のデータ取得
  //件数(D列)にデータありの最終行(InMaxRow)の取得
  let i;
  for (i = 0; i <7; i += 6) {
    const InMaxRow = mySheet1.getRange(8, i+4).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    const targetRows = InMaxRow - 7;
    console.log("targetRows " + targetRows);

    //タテ方向(x)のループ、ヨコ方向(y)のループの二重ループで取得
    //getRange(行、列、▲行分、■列分) 「課」～「転記処理」までの5列分を1塊として二次元配列(values)として取得
    let x, y;
    for (y = 0; y < 4; y += 6) {
      for (x = 0; x <= targetRows; x++) {  
        const myDetaRange = mySheet1.getRange(8 +x, 3 +y, targetRows, 5);
        console.log("myDetaRange " + myDetaRange.getA1Notation());
        const values = myDetaRange.getValues();
        let Branch = values[x][y];
        let Num = values[x][y +1];
        let startTime = values[x][y +2];
        let endTime = values[x][y +3];
        let length = values[x][y +4];
        console.log(values);
        console.log("values[0].length " + values[0].length);
        console.log("values.length " + values.length);

        //▼二次元配列(values)で取得したデータを「マージ」シートに転記する
        //件数(E列)にデータありの最終行(OutMaxRow1)の取得
        const OutMaxRow1 = mySheet2.getRange(mySheet2.getMaxRows(), 5).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
        console.log("OutMaxRow1 " + OutMaxRow1);

        //getRange(行、列、▲行分、■列分) 
        mySheet2.getRange(OutMaxRow1 +1, 4, values.length, 5).setValues(values);

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

        //転記結果を「入力シート(mySheet1)」の「転記処理」欄に追記する
        //getRange(行、列、▲行分、■列分)       
        mySheet1.getRange(8 + x, 8 + y, 1, 1).setValue("転記済");
      }
    }
    //▼ダイアログMsgの表示
    Browser.msgBox("「マージシート」への転記処理が完了しました。\n「転記処理」列に「転記済」表示があれば問題なく転記処理ができています。", Browser.Buttons.OK);
  }
}

試したこと

配列とループ処理の理解が不十分で申し訳ありません。
お手数ですが
・考え方
・現コードの誤りの箇所と修正方法　
をご教示いただけないでしょうか?

①各ブロック内でのループ処理
MAX行数の判定列がD列、J列、P列、、、と6列とばしのため、
列指定して個々に定義ではなく、繰り返し処理とした方がよいかと
考えたのですが、ブロックごとにMAX行数が可変です。
const myDetaRange =　の記載よりも前の部分でgetRangeの「▲行分」の
定義が必要かと思うのですが、どう可変定義すればよいのか迷いました。

②ループ処理順とfor文の定義順
二次元配列のループ処理順についての下のサイトを参照しました。
参照したサイト

ブロック内は下方向にループ。
ブロック内処理が完了したら、次のループへ、としたい場合、
下リンクの「N字の順番」で処理のケースにあたりますか?
それとも、「Z字の順番」で処理のケースですか?

ブロック内は、「二次元配列内の一次元配列をループ」のケースかと
思っているのですが、ブロック間のループも追加となった場合
どれにあたるのかが分からなくなってしまいました。
C列～G列までの1塊を1列と同じとみなして「N字の順番」が正解ですか?

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

次回から、類似のケースは自力で解決できるようになりたいです。
回答は急ぎませんので、
お手数をおかけして申し訳ありませんが、非エンジニア、ビギナーでも
理解できるレベルでの解説をいただけると助かります。

Comments

[donguriko]

int32_tさま
お忙しい中、アドバイスありがとうございます。

●iで何をループさせたいか
1つ目のループでブロック間ループをさせたいです。
iは「ブロックの開始col」とします。
混乱しないようにblockcolに書替えてコード修正しました。
3col目スタートで6列で1塊×7ブロックなので、
for (blockcol = 3; blockcol < 6*7 +3; blockcol += 6) {
なのですね。

●ブロック内のループ
getValuesした段階で、getRangeで指定した範囲の複数データをまとめて
とれるので、ヨコ方向のループ(y)は不要なのですね。
そして、タテ軸、ヨコ軸を逆に記載しており大変失礼いたしました。
自分でも混乱してしまうので、タテ方向を「r」としてコード修正しました。
<修正後>
//▼部署ブロック内のループ
//「部署」～「所用時間」までの5列分をgetRange(行、列、▲行分、■列分) で
// 二次元配列(Values)として取得
// 処理する行番号をrとする。
for (r = 0; r <= targetRows; r++) {
const myDetaRange = mySheet1.getRange(8, blockcol, targetRows, 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);
console.log("values[0].length " + values[0].length); //5が出れば正解
console.log("values.length " + values.length);　//targetRowsと同じ値

※上記2点を修正しましたが、ブロック①→ブロック②への移行がうまく動かないです。
　私の誤修正でしたら申し訳ありません。

●再質問：ループ処理
二次元配列として取得したいデータのイメージは、
ヨコ方向は部署～所用時間までの5列で
ブロック①分
ブロック②分
ブロック③分　とタテにつながったデータとして取得し、
タテにつながった状態のデータを「マージ」シートに
書き出ししたいです。
(以下、とんちんかんな質問でしたら申し訳ありません)

a)この場合、配列内の
ブロック②分の「部署」の[行][列]インデックス?は[①のtargetRows+1][0]
ブロック③分の「部署」の[行][列]インデックスは[②のtargetRows+1][0]
としないといけない、という理解であってますか?

b)上記a)が正しい場合、
　今の私のコードだと、
　ブロック①だと(r = 0; r <= targetRows; r++) { でよいが、
　ブロックが動くと、rは0スタートではなく、
　①のtargetRows+1スタートだということを
　1個目のループ、ブロック間ループの部分でもりこまないといけない、
　今コードではブロック間ループの部分でそこが書けていないから
　正しく動かないという理解であっていますか?

c)知識不足で申し訳ありません。
　上記b)が正しい場合、ブロック間ループの部分をどう修正すればいいか
見当がつかないので、アドバイスをいただけないでしょうか?

Answer 1

  for (i = 0; i <7; i += 6) {
    const InMaxRow = mySheet1.getRange(8, i+4).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

ここで、i で何をループしているのかがあいまいなのがよろしくないです。
ブロック番号のつもりであるなら、

for (let blockIndex = 0; blockIndex < 7; ++blockIndex) {
  ...getRange(8, blockIndex * 6 + 4)...

ブロック開始カラムであるなら、

for (let blockCol = 3; blockCol < 7*6 + 3; blockCol += 6) {
  ...getRange(8, blockCol + 1) ...

---

    for (y = 0; y < 4; y += 6) {
      for (x = 0; x <= targetRows; x++) {  
        const myDetaRange = mySheet1.getRange(8 +x, 3 +y, targetRows, 5);

• xを横軸、yを縦軸として扱うのが常識的ですが、このコードでは逆になっていて混乱します。
• getRange()で複数のカラムの値を取れるので、y のループは不要です。
• ここの getRange() でブロックの開始カラムを考慮していません。getRange(8 + x, blockIndex * 6 + 3) か getRange(8 + x, blockCol) ですね。

続くコードの出力シートのカラム値も、ブロック番号を考慮する必要があります。

Comments

[donguriko]

int32_tさま
お忙しい中、アドバイスありがとうございます。
ご回報内容につき、「さら問い」させていただきたい件があり
コメント記載をさせていただきました。
間違えて、上の欄の質問への追記・修正依頼欄に下と同じ内容を
記載してしまいました。
下に同じ内容を再掲載させていただきます。
int32_tさまからアドバイスいただきました内容を一発で
自分のものにできず申し訳ありません。
-------------------------------------------------------------------------------------------
●iで何をループさせたいか
1つ目のループでブロック間ループをさせたいです。
iは「ブロックの開始col」とします。
混乱しないようにblockcolに書替えてコード修正しました。
3col目スタートで6列で1塊×7ブロックなので、
for (blockcol = 3; blockcol < 6*7 +3; blockcol += 6) {
なのですね。

●ブロック内のループ
getValuesした段階で、getRangeで指定した範囲の複数データをまとめて
とれるので、ヨコ方向のループ(y)は不要なのですね。
そして、タテ軸、ヨコ軸を逆に記載しており大変失礼いたしました。
自分でも混乱してしまうので、タテ方向を「r」として以下のとおりコード修正しました。

★(修正した理由)
　ブロック①の場合だとtargetRowsは4。
　ブロック①分の行インデックスは0～3までの繰り返しとして欲しいので、4「未満」。
　<誤> r <= targetRows <正>　r < targetRows　

<修正後>
//▼ブロック内のループ
//「部署」～「所用時間」までの5列分をgetRange(行、列、▲行分、■列分) で
// 二次元配列(Values)として取得
// 処理する行番号をrとする。↓　★(2/5修正) r <= targetRows　⇒　r <targetRowsに修正
for (r = 0; r < targetRows; r++) {
const myDetaRange = mySheet1.getRange(8, blockcol, targetRows, 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);
console.log("values[0].length " + values[0].length); //5が出れば正解
console.log("values.length " + values.length);　//targetRowsと同じ値

●さら問い①
ご教示いただいたブロック内ループの getRange(8 + x, blockCol) 部の考え方が
理解しきれませんでした。
なぜ、getRange(8, blockcol, targetRows, 5);ではなく、
8+x　になるのかが、よく分かりませんでした。
お忙しいところ大変申し訳ありませんが、もう少し補足解説を
いただけないでしょうか?

(背景)
スプレッドシートからgetRangeでデータを取得する際は、複数行まとめて
データ取得可能。またどのブロックを処理する場合でも、データ取得開始行
はいつも8行目スタートで終了行はtargetRowsだと思っています。
開始行固定なのにどうして可変とする8+x　なのですか?

ブロック①のようにスプレッドシート上に複数行記載があった場合、
「部署」「件数」「開始時刻」「終了時刻」「所用時間」の5項目の
行インデックスは1行目と2行目では異なるので[x(r)]とするのは
理解できるのですが、なぜgetRangeで(複数行分まとめて取得ではなく)
行部分を可変とし、1行分ずつ取得しないといけない(した方がいい)のかが
よく分かりません。
1行分ずつ取得して、配列に格納しないと行インデックスを正しく
つけられないからですか?

また、ご回報内容だとgetRangeの( )内の▲行分、■列分の指定の
記載がないようですが、これは元の私の記載どおりで問題ないため
記載省略していますか?
それとも、指定の必要がないから　記載していないですか?
行番号、列番号のみの指定だと特定セルの指定になってしまうのではないかと
思っているのですが、あえて後者としている場合は、なぜ記載不要なのかも
補足説明いただけないでしょうか?
次回から自力で解決できるようになりたいので、基礎の基礎の質問で
申し訳ありませんが、お時間がある時でよいのでご教示いただけないでしょうか?

●さら問い② ブロック③のtargetRowsの取得
(別の質問を立てた方がよいか迷いました。別質問の方がよければご指摘ください)
希望：ブロック③の時は8行目だけデータ取得して欲しい。
下のコードで、8行目のブロック開始colの1つ右の列を参照し、データあり最終行を
取得させているつもり。
const InMaxRow = mySheet1.getRange(8, blockcol +1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

実態：ログで確認するとブロック①は4、ブロック②は3、と正しく判定できているが、
　　　ブロック③は判定がされず、入力シートへの「転記済」の表示が8行目～18行目まで
　　　記載されてしまいます。
　　　なぜブロック③の場合だけ正しく取得できないのか、原因が分からず困っています。
　　　件数0がマズイのかと思い、件数1にして開始時刻、終了時刻ブランクで試しましたが
　　　うまくいきませんでした。
　　　現コードだと、2行以上(8行目、9行目)記載がある場合しか、正しく最終行を取得
　　　できないからですか?

[int32_t]

> ご教示いただいたブロック内ループの getRange(8 + x, blockCol) 部

これは私の書き損じですね。第3引数と第4引数を書き忘れてました。
質問文のコードのようにtargetRowsでループするのなら getRange(8 + x, blockCol, 1, 5) だと思います。
ループせずに2次元配列を入手するなら、getRange(8, blockCol, 8 + targetRows , 5) でしょう。

> ●さら問い② ブロック③のtargetRowsの取得

私以外の人が答えられるように、別の質問にしていただけると嬉しいです。

[donguriko]

int32_tさま
ご返信ありがとうございます。

<さら問い①>
●ブロック内ループの getRange(8 + x, blockCol) 部
回報ありがとうございます。
第3引数と第4引数の記載必要とのこと、了解です。
当方にもっと知識があれば全部厳密に記載いただかなくても、int32_tさま
の言いたいことが伝わっていたところをかえって申し訳ありません。。。

●getRange(8 + x, blockCol, 1, 5) かgetRange(8, blockCol, 8 + targetRows , 5) か?
1行ずつループして取得するか、複数行分まとめて取得するか次第とのこと、
了解です。
質問させていただいた当初、頭の中が整理できていませんでした(混乱気味だった)ので、
再度どちらでデータ取得するのかを再整理させていただき、コードも再考させて
いただきます。
お忙しい中、ご指摘、アドバイスありがとうございました。
勉強になりました。

<さら問い②>
さら問い①の部分を含め、コードを見直した上で自力解決できないようであれば、
新たな質問を立てさせていただきます。
お忙しい中、お時間割いていただきありがとうございました。(感謝)