【GAS】スプレッドシート更新時のメール自動送信処理_不具合解消法

Question

前提

スプレッドシート内に全部で３つのシートがあります。
シート1：情報のやりとりをするシート
シート２:メールアドレスのテーブル
シート3：自動送信させるメールの件名、本文等が記載されているシート

複数人で「シート1」を通じて、情報のやりとりをしています。
情報のやりとりは、以下の2種があります。
(往路) 新規投稿(B列～G列に追記)
(復路) 新規投稿へのコメントバック(K列～M列に追記)

<シート1のイメージ>

実現したいこと

(往路)または(復路)のシート1の内容更新が行われたら、
GASで更新連絡メールを自動送信させたいです。
(往路)と(復路)で宛先、本文が異なるため、
処理をfunction1とfunction2の２つに分け、
それぞれに対しイベントトリガー設定をし、自動で処理が
走るようにしようと思っています。

<function1(往路)> I列がブランクの時のみ処理

• 1-1　スプレッドシートの情報をもとにメール作成、送信
• 1-2　シート1のI列に「済」フラグ追記
• 1-3　シート1のJ列にタイムスタンプ追記

<function2(復路)> I列に「済」があり、o列がブランクの時のみ処理

• 2-1　スプレッドシートの情報をもとにメール作成、送信
• 2-2　シート1のO列に「済」フラグ追記
• 2-3　シート1のP列にタイムスタンプ追記

<トリガー設定>
スプレッドシート_変更時

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

function1の処理は問題なく走ります。
が、処理2-1のメール宛先の元データのN列を、下のスプレッド
シート上の関数※で補充させている関係で、function1が走った
直後にN列が補充されてしまい、そのタイミングでfunction2の処理が走り、
処理2-2、2-3が行われるため、処理2-1処理の判定で「作成対象外」と
判定されてしまうためか、メール送信ができません。

※(セルN5)の関数
=ArrayFormula(IFERROR(VLOOKUP(D5:D,'メアドシート'!$B$5:$H$8,7,0),""))

どのように回避するのがよいか、アドバイスをいただけないでしょうか?
また、どうしてアドバイスいただいた回避策がよいのかのメリ、
(第三案の場合は、以下a)、b)のデメ)についても教えてください。

<現時点で想像している回避策>

a) (セルN5)の関数を修正する
M列(返信コメント)に記載がない場合は、N列の補充処理がされないよう、　
IFをさらに追加すればよいのでは? と、想像しているものの、どこに
どのようにIF追加すればよいか自力でたどりつけないので、
アドバイスが欲しいです。

b) function2にN列を追記する処理を追加する
E列の内容をキーに、シート２:メールアドレスのテーブルを参照し、
O列ブランクのN列のセルにメアド補充させたらよいのではないかと
推察していますが、どのようにコード記載すればよいかまでは
自力でたどりつけないので、アドバイス欲しいです。

該当のソースコード

以下に現コードを全文記載します。
8/12　下コードの冒頭にfunction1のコードを追記。

function createMailData1() {
  const ss = SpreadsheetApp.openById('～省略～');
  const mySheet1 = ss.getSheetByName('元シート');
  const mySheet2 = ss.getSheetByName('メアドシート');
  const mailSheet = ss.getSheetByName('メール文シート');
  const lastRow = mySheet1.getRange(mySheet1.getMaxRows(), 7).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log('lastRow ' + lastRow); //9が出たら正解
  const targetRows = lastRow - 5
  console.log('targetRows ' + targetRows); //4が出たら正解 

  //▼送信先のメアド
  //「元シート(mySheet1)」のB列～I列までの値を
  //getRange.getValuesで二次元配列(myRange)として取得
  //起点は6行目、3列目(セルB6)
  //getRange(行,列,●行分,▲列分)
  const myRange = mySheet1.getRange(6, 2, targetRows, 8).getValues();
  console.log('myRange ' + myRange);

  //for ループ
  //行インデックス0スタートで+1ずつ、lastRowまで繰り返し
  for (let i = 0; i < targetRows; i++) {
    let no = myRange[i][0]; //　連番(B列)
    let recipient = myRange[i][6]; //  メアド(H列)
    let myFlag1 = myRange[i][7]; // 投稿連絡フラグ(I列)
    console.log('no ' + no);
    console.log('recipient ' + recipient);
    console.log('myFlag1 ' + myFlag1);

    //投稿連絡フラグ欄(myFlag1)に「済」ありの時は処理スキップ
    if (myFlag1 === "済") {
      continue;
    }

    //▼メール件名
    //「メール文シート(mySheet2)」のセルC3から取得
    //getRange(行,列)
    const subject1 = mailSheet.getRange(3, 3).getValue();
    console.log('subject1 ' + subject1);

    //▼メール本文(body1)
    //「メール文シート(mySheet2)」のセルC4から取得
    const body1 = mailSheet.getRange(4, 3).getValue();
    console.log('body1 ' + body1);

    const options = {
      name:　'自動送信メール',
      noReply: true
      //from:'～省略～'
      };                 
    GmailApp.sendEmail(recipient,subject1,body1,options);

    //▼元シート(mySheet1)の「投稿連絡メール」欄(I列)に"済"フラグを追記
    //getRange(行,列,●行分,▲列分)
    const addFlag = mySheet1.getRange(i +6, 9, 1, 1).setValue("済");

    //▼元シート(mySheet1)の「投稿連絡送信日」欄(J列)に"済"日時を追記 
    const timeStamp =new Date();
    console.log(timeStamp);
    mySheet1.getRange(i +6, 10, 1, 1).setValue(timeStamp);
  }
}



function createMailData2() {
  const ss = SpreadsheetApp.openById('～省略～');
  const mySheet1 = ss.getSheetByName('元シート');
  const mySheet2 = ss.getSheetByName('メアドシート');
  const mailSheet = ss.getSheetByName('メール文シート');
  const lastRow = mySheet1.getRange(mySheet1.getMaxRows(), 7).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  console.log('lastRow ' + lastRow); //9が出たら正解
  const targetRows = lastRow - 5
  console.log('targetRows ' + targetRows); //4が出たら正解 

  //▼送信先のメアド
  //「元シート(mySheet1)」のB列～P列までの値を
  //getRange.getValuesで二次元配列(myRange2)として取得
  //getRange(行,列,●行分,▲列分)
  const myRange2 = mySheet1.getRange(6, 2, targetRows, 16).getValues();
  console.log('myRange2 ' + myRange2);

  //for ループ
  //行インデックス0スタートで+1ずつ、lastRowまで繰り返し
  for (let i = 0; i < targetRows; i++) {

    //二次元配列名[行インデックス][列インデックス]
    //起点[0][0]は6行目、3列目(セルB6)
    let no = myRange2[i][0]; //　連番(B列)
    let recipient = myRange2[i][12]; //  投稿者メアド(N列)
    let myFlag1 = myRange2[i][7]; // 投稿連絡フラグ(I列)
    let myFlag2 = myRange2[i][13]; // 返信連絡フラグ(O列)
    console.log('no ' + no);
    console.log('recipient ' + recipient);
    console.log('myFlag1 ' + myFlag1);
    console.log('myFlag2 ' + myFlag2);

    //<条件1>
    //返信連絡フラグ(myFlag2)がブランクの時は処理スキップ
    if (myFlag2 === "済") {
      continue;

      //<条件2>    
      //投稿連絡フラグ((myFlag1)が「済」の時はfalse、それ以外はtrue
    } else if (!(myFlag1 === "済")){
      continue;
    }

    //▼メール件名
    //「メール文シート(mySheet2)」のセルC7から取得
    //getRange(行,列)
    const subject2 = mailSheet.getRange(7, 3).getValue();
    console.log('subject2 ' + subject2);

    //▼メール本文(body2)
    //「メール文シート(mySheet2)」のセルC8から取得
    const body2 = mailSheet.getRange(8, 3).getValue();
    console.log('body2 ' + body2);

    const options = {
      name: '自動送信メール',
      noReply: true
    };

    GmailApp.createDraft(recipient, subject2, body2, options);
    //★あとでsendEmailに修正★
    //GmailApp.sendEmail(recipient, subject2, body2, options);

    //▼お褒めシート(mySheet1)の「返信連絡フラグ欄(O列)」に"済"フラグを追記
    //getRange(行,列,●行分,▲列分)
    const addFlag = mySheet1.getRange(i + 6, 15, 1, 1).setValue("済");

    //▼お褒めシート(mySheet1)の「返信連絡送信日」欄(P列)に"済"日時を追記 
    const timeStamp = new Date();
    console.log(timeStamp);
    mySheet1.getRange(i + 6, 16, 1, 1).setValue(timeStamp);
  }
}

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

次回から、類似のケースは自力解決できるようになりたいので、
回答は遅くなってもかまいませんので、非エンジニア、ビギナーでも
わかるレベルでの回答、解説をいただけると助かります。

Comments

[YAmaGNZ]

復路のメールを送る条件を整理してみては？
「N列が変更された時」というのはメール送信条件となるのですか？

[退会済みユーザー]

①トリガーの設定について
fucntion1 と function2の両方に、「ファイル変更時」にそれぞれの関数が実行されるようにトリガーを設定している、という理解でよろしいでしょうか。

②「function1(往路)」でのメールを送信する条件が不明です。function1のコードも質問文に追記してください。

[donguriko]

YAmaGNZさま、qnoirさま
コメントありがとうございます。

以下qnoirさまからのコメントに回報します。
①ご認識いただいているとおりです。

②大変失礼いたしました。
　該当のソースコード欄の冒頭にfunction1のコードを追記させて
　いただきました。よろしくお願いいたします。

Answer 1

a) (セルN5)の関数を修正する：
M列(返信コメント)に記載がない場合は、N列の補充処理がされないよう、下記のような数式に修正してみてはいかがでしょうか。

=ArrayFormula(IF(ISBLANK(M5:M),"",IFERROR(VLOOKUP(D5:D,'メアドシート'!$B$5:$H$8,7,0),"")))

Comments

[donguriko]

qnoirさま
お忙しい中、お手数をおかけいたしました。
ISBLANKの足し方のレクチャーありがとうございます。
関数が沢山続くと、()や　カンマが足りているのか、段々わからなくなり、
頭が混乱してしまいます。。。
ご教示いただいた通り修正したら、想定した通りの動きとなりました。
アドバイス本当にありがとうございました!!
大変助かりました。