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

Question

前提・実現したいこと

以前ご教示いただいた内容から、下のように処理変更をしたいです。
(参考)　前回の質問リンク

<現>
・予定①～④のすべてがブランクの時は列Lに「対象外」のフラグを立て、
配列としてデータ取得の際、該当日付はスキップさせる。

<新>
上記に加え、以下処理も追加
・予定①～④が単体でブランクの時、列H～K(登録処理①～④)に「対象外」のフラグを立て、
配列としてデータ取得の際、該当予定件名の取得はスキップさせる。

※予定③、④は(登録なし)ブランクとなるケースがあることが判明。
予定(登録なし)ブランクとなるケースでカレンダーに「件名なし」で
登録されるのを回避したい。
予定①、②はブランクの想定はないが、オペレーションミスでブランクとなった
ケースを想定し、同処理とする。

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

下のとおりコード修正しました。
列H～列Lまでに「対象外」のフラグを立てる処理はできました。
が、予定①～④のいずれかが単体でブランクの時にもL列に「対象外」フラグが
立ってしまいます。

又、以下の処理も行われずGAS処理が終了となってしまいます。
・カレンダーへの予定登録処理
・スプレッドシート列H(登録処理①)～列K(登録処理④)への「登録済」追記

おそらくループの指定方法に誤りがあると思われるのですが、
どこがマズイのか、どのように修正すればよいのかが分かりません。
アドバイスをいただけないでしょうか?
(ループの範囲の理解がやはりまだ不十分のようです。申し訳ありません。。)

該当のソースコード

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

function createEvent() {
  //▼予定を追記するGoogleカレンダーIDを取得する　<<セルC9(行9,列3)
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mysheet1 = ss.getSheetByName("カレンダー転記用");
　const calId = mysheet1.getRange(9,3).getValue();
  const cal = CalendarApp.getCalendarById(calId);
  const targetRows = mysheet1.getRange('C13').getValue();
  console.log("targetRows " + targetRows);

  //予定①～予定④に予定なし分(土、日、祝日)はL列にフラグ立て
  const endRow = targetRows + 15
  console.log("endRow " + endRow);

  for (let Row = 16; Row < endRow + 1; Row++) {
    for (let col = 4; col < 8 + 1; col++) {
      const myRange1 = mysheet1.getRange(Row, col, 1, 1);
      const myRange2 = mysheet1.getRange(Row, 4, 1, 4);

      //▼予定①～④がブランクの時のフラグ立て
      if (myRange1.isBlank()) {
        mysheet1.getRange(Row, col + 4, 1, 1).setValue("対象外");
      }

      else if (myRange2.isBlank())
        mysheet1.getRange(Row, 12, 1, 1).setValue("対象外");
      //getRange(行目,列目,●行分,●列分)
    }
  }

  //▼データ取得範囲の指定
  //各種予定のデータ取得範囲の指定　
  //getRange(行目,列目,●行分,●列分)
  //予定配列>>セルB16(行16,列2)～L列まで　
  const eventRange = mysheet1.getRange(16, 2, targetRows, 11);
  console.log("eventRange " + eventRange.getA1Notation());

  //▼各予定データを配列(myEvent)として取得する
  const myEvent = eventRange.getValues();
  Logger.log(myEvent);

  //▼各日付ごとに処理する
  for (let i = 0; i < targetRows; i++) {
    let myDate = myEvent[i][0]; //「日付」は起点B16から下にi行、右に0    
    let ngFlag = myEvent[i][10];  //「対象外フラグ」は起点B16から下にi行、右に10　
    // 「対象外」であればスキップして次の行へ。
    if (ngFlag === "対象外") {
      continue;
    }
    //▼「①~④予定」を一列ずつ処理（column：0～3までループ）
    for (let column = 0; column < 4; column++) {
      let flag = myEvent[i][6 + column];  //「①～④登録済フラグ」は起点B16から下にi行、右に6+column
      // 「登録済」であればスキップして隣の列へ。
      if (flag === "登録済" || flag === "対象外") {
        continue;
      }
      //「予定名(title)」を取得
      let title = myEvent[i][2 + column];  //「①～④予定名」は起点0(B16)から下にi行、右に2+column

      //▼カレンダーへの予定登録 
      cal.createAllDayEvent(title, new Date(myDate));

      //▼スプレッドシートへの「登録済」フラグ記載
      mysheet1.getRange(16 + i, 8 + column, 1, 1).setValue("登録済");　//スプシH列に「登録済」と追記 
    }
  }
  //▼ダイアログMsgの表示
  Browser.msgBox("Googleカレンダーへの転記作業が完了しました。登録内容は「カレンダー転記用」シートで確認してください。", Browser.Buttons.OK)
}

試したこと

今回の処理変更に伴い、
予定①～④がブランクか否かの判定を横方向のループ処理で判定させる
のがよいのではないかと考え、行方向、列方向の二重ループ処理に変更すべく、
以下を試みました。

●変更1：17行目以降
(現)endRowまで1行ずつ下に処理していくループ処理
(新)上のループに加え、
列D～Gまで1列ずつ右に処理していくループ処理　を追加。

  for (let Row = 16; Row < endRow + 1; Row++) {
    for (let col = 4; col < 8 + 1; col++) {

●変更2：56行目以降
(現) flag==="登録済"　の時処理スキップ
(新) (flag === "登録済" || flag === "対象外") に変更

      if (flag === "登録済" || flag === "対象外") {
        continue;
      }

33行目以降の処理が行われていない原因は
括弧のくくり方(閉じ括弧の位置)が間違えているからですか?
列方向のループ処理を追加する位置が違うからですか?

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

回答は本日中でなくても大丈夫です。
お忙しいところ何度も質問してしまい本当に申し訳ありません。
次回から自力で解決できるよう、非エンジニア、ビギナーでも分かるレベルでの
アドバイス、解説をいただけると助かります。
お手数をおかけいたしまして申し訳ありません。

Comments

[退会済みユーザー]

L列に「対象外」という文字を入れる条件について確認なのですが、
①D列～G列４つとも空白の場合は、H～K列に「対象外」を入力し、L列にも「対象外」を入力する、という理解でよろしいでしょうか。
②たとえば、D列だけに予定が入っていて、E～G列３つが空欄の場合は、
H列は空欄、I列～K列に「対象外」を入力し、L列には「対象外」を入力しない
という理解でよろしいでしょうか。

[donguriko]

qnoirさま
確認が遅くなり申し訳ありません。以下、回答します。

①はい、重複感はありますが、４つとも空白の場合は、H～K列に「対象外」かつ、
　L列にも「対象外」と返す想定でいます。

②はい、ご認識のとおりの想定です。
　L列「対象外」の時は配列に日付取得しない。<<件名なしの予定が4個登録されるのを回避したい。
　H～K列「対象外」の時は、日付は取得するが、予定件名のみ取得しないとしたい。
　修正前のコードだと、予定③、④がブランクの場合、該当日に件名なしの予定が2個登録
　されてしまう。<<これを回避したいです。※

　※もし、上記フラグ立てでは、予定③、④がブランク時の件名ブランク登録が
　　回避できない場合は、可能であればどのようなやり方なら回避できそうかアドバイスを
　　いただけると幸いです。

[donguriko]

qnoirさま
返信が遅くなり申し訳ありません。
お忙しい中、回答ありがとうございます。

ループ処理の際の参照範囲の指定誤りが原因だったのですね。
又、処理が遅い部分についても改善策提示ありがとうございます。
私のコードだと、my Range2で同じ範囲を4回チェックすることに
なってしまっていたのですね。時間がかかる筈ですね。
いつも困った時に、助けていただき本当にありがとうございます。
今回の回答も今後に生かせるよう読み返し、お勉強させていただきます。
ありがとうございました。

★追伸･･･修正いただいたコードで格段に処理が早くなりました。本当にありがとうございました。

Answer 1

予定①～④のいずれかが単体でブランクの時にもL列に「対象外」フラグが
立ってしまいます。

これの原因ですが、15行目あたり（下記の色付きで強調した行）の「col < 8 + 1」が原因です。

  for (let Row = 16; Row < endRow + 1; Row++) {
+   for (let col = 4; col < 8 + 1; col++) {
      const myRange1 = mysheet1.getRange(Row, col, 1, 1);
      const myRange2 = mysheet1.getRange(Row, 4, 1, 4);

for (let col = 4; col < 8 + 1; col++)
とすると、colは4から8まで、すなわち D列目からH列までの「5列分」を処理することになります。

colが8のとき、myRange1はH列を指しているので、その後の

     //▼予定①～④がブランクの時のフラグ立て
      if (myRange1.isBlank()) {
        mysheet1.getRange(Row, col + 4, 1, 1).setValue("対象外");
      }

の部分で、myRange1（H列）が空欄の場合、col+4 = 8+4 = 12 = L列に「対象外」が入力されることになります。
D列（すなわちタスク①の列）に何らかの文字が入力されている場合、
H列には「対象外」という値が入力されないため、H列は空欄となります。
結果、L列に「対象外」が入力されてしまいます。

ここは
col < 8 + 1
ではなく
col < 8
として、D列からG列までの「4列分」を処理するようにするべきです。

  for (let Row = 16; Row < endRow + 1; Row++) {
-   for (let col = 4; col < 8 + 1; col++) {  // D列からH列まで見てしまっている
+   for (let col = 4; col < 8; col++) {      // D列からG列までの「4列分」を処理する
      const myRange1 = mysheet1.getRange(Row, col, 1, 1);
      const myRange2 = mysheet1.getRange(Row, 4, 1, 4);

そうすれば、D列にタスクが記入されていればL列に「対象外」が入力されることはなくなり、カレンダー処理もスキップされません。

又、以下の処理も行われずGAS処理が終了となってしまいます。
・カレンダーへの予定登録処理
・スプレッドシート列H(登録処理①)～列K(登録処理④)への「登録済」追記

33行目以降の処理が行われていない原因は
括弧のくくり方(閉じ括弧の位置)が間違えているからですか?
列方向のループ処理を追加する位置が違うからですか?

修正前のコードで33行目以降の処理が行われなかった原因は、
col < 8** + 1**となっていたために、H列までで良いところG列まで1列余計に調べてしまい
（D列にタスクが入力されているほぼ全部の行について）L列に「対象外」が入力され、
カレンダーへの登録処理や「登録済」追記がスキップされてしまったからです。
括弧のくくり方は問題ないと思います。

---

・L列への「対象外入力」条件をどのようにして判定するか

上記を修正（col < 8 + 1→col < 8）すれば、一応カレンダーへの登録と、登録があった列の「登録済」追記は正常に行われると思います。

しかしながらL列には何も記入されません。

コメントによると、L列へ「対象外」を記入する条件は「D列～G列の4列いずれも空白の場合」とのこと。
ここで、元の

      //▼予定①～④がブランクの時のフラグ立て
      if (myRange1.isBlank()) {
        mysheet1.getRange(Row, col + 4, 1, 1).setValue("対象外");
      }

+     else if (myRange2.isBlank())
        mysheet1.getRange(Row, 12, 1, 1).setValue("対象外");
      //getRange(行目,列目,●行分,●列分)
    }

のelseは不要です。

正しく条件判定してL列に「対象外」を入力するには、 else を外す必要があります。

  for (let Row = 16; Row < endRow + 1; Row++) {
-   for (let col = 4; col < 8 + 1; col++) {
+   for (let col = 4; col < 8; col++) {
      const myRange1 = mysheet1.getRange(Row, col, 1, 1);
      const myRange2 = mysheet1.getRange(Row, 4, 1, 4);

      //▼予定①～④がブランクの時のフラグ立て
      if (myRange1.isBlank()) {
        mysheet1.getRange(Row, col + 4, 1, 1).setValue("対象外");
      }
-     else if (myRange2.isBlank())  // elseは不要
+     if (myRange2.isBlank())
        mysheet1.getRange(Row, 12, 1, 1).setValue("対象外");
      //getRange(行目,列目,●行分,●列分)
    }
  }

さらに、myRange2という同じ範囲を４回チェックしているのは無駄なので、
先にmyRange2を調べ、
4列全部空白ならばH列～L列の5列分一気に「対象外」を書き込んで
次の行に行くようにすることで、少し高速化されます。

  for (let Row = 16; Row < endRow + 1; Row++) {
+   // 先にD列～G列全部空白かどうか判定する。
+   const myRange2 = mysheet1.getRange(Row, 4, 1, 4);
+   if (myRange2.isBlank()) {
+     // 4列空白ならばH列～L列の5列に「対象外」を書き込んで次の行に行く
+     mysheet1.getRange(Row, 8, 1, 5).setValue("対象外");
+     continue;
+   }
    for (let col = 4; col < 8; col++) {
      const myRange1 = mysheet1.getRange(Row, col, 1, 1);
-     const myRange2 = mysheet1.getRange(Row, 4, 1, 4);

      //▼予定①～④がブランクの時のフラグ立て
      if (myRange1.isBlank()) {
        mysheet1.getRange(Row, col + 4, 1, 1).setValue("対象外");
      }
-     else if (myRange2.isBlank()) 
-     mysheet1.getRange(Row, 12, 1, 1).setValue("対象外");
-     //getRange(行目,列目,●行分,●列分)
    }
  }

---

上記を加味した修正後の全文は下記になります。

function createEvent3() {
  //▼予定を追記するGoogleカレンダーIDを取得する　<<セルC9(行9,列3)
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mysheet1 = ss.getSheetByName("カレンダー転記用");
　const calId = mysheet1.getRange(9,3).getValue();
  const cal = CalendarApp.getCalendarById(calId);
  const targetRows = mysheet1.getRange('C13').getValue();
  console.log("targetRows " + targetRows);

  //予定①～予定④に予定なし分(土、日、祝日)はL列にフラグ立て
  const endRow = targetRows + 15
  console.log("endRow " + endRow);

  for (let Row = 16; Row < endRow + 1; Row++) {
    // 先にD列～G列全部空白かどうか判定する。
    const myRange2 = mysheet1.getRange(Row, 4, 1, 4);
    if (myRange2.isBlank()) {
      // 4列空白ならばH列～L列の5列に「対象外」を書き込んで次の行に行く
      mysheet1.getRange(Row, 8, 1, 5).setValue("対象外");
      continue;
    }
    for (let col = 4; col < 8; col++) {
      const myRange1 = mysheet1.getRange(Row, col, 1, 1);
 
      //▼予定①～④がブランクの時のフラグ立て
      if (myRange1.isBlank()) {
        mysheet1.getRange(Row, col + 4, 1, 1).setValue("対象外");
      }
   }

  //▼データ取得範囲の指定
  //各種予定のデータ取得範囲の指定　
  //getRange(行目,列目,●行分,●列分)
  //予定配列>>セルB16(行16,列2)～L列まで　
  const eventRange = mysheet1.getRange(16, 2, targetRows, 11);
  console.log("eventRange " + eventRange.getA1Notation());

  //▼各予定データを配列(myEvent)として取得する
  const myEvent = eventRange.getValues();
  Logger.log(myEvent);

  //▼各日付ごとに処理する
  for (let i = 0; i < targetRows; i++) {
    let myDate = myEvent[i][0]; //「日付」は起点B16から下にi行、右に0    
    let ngFlag = myEvent[i][10];  //「対象外フラグ」は起点B16から下にi行、右に10　
    // 「対象外」であればスキップして次の行へ。
    if (ngFlag === "対象外") {
      continue;
    }
    //▼「①~④予定」を一列ずつ処理（column：0～3までループ）
    for (let column = 0; column < 4; column++) {
      let flag = myEvent[i][6 + column];  //「①～④登録済フラグ」は起点B16から下にi行、右に6+column
      // 「登録済」であればスキップして隣の列へ。
      if (flag === "登録済" || flag === "対象外") {
        continue;
      }
      //「予定名(title)」を取得
      let title = myEvent[i][2 + column];  //「①～④予定名」は起点0(B16)から下にi行、右に2+column

      //▼カレンダーへの予定登録 
      cal.createAllDayEvent(title, new Date(myDate));
      
      //▼スプレッドシートへの「登録済」フラグ記載
      mysheet1.getRange(16 + i, 8 + column, 1, 1).setValue("登録済");　//スプシH列に「登録済」と追記 
    }
  }
  //▼ダイアログMsgの表示
  Browser.msgBox("Googleカレンダーへの転記作業が完了しました。登録内容は「カレンダー転記用」シートで確認してください。", Browser.Buttons.OK)
}