質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Gmail

GmailとはGoogleによって提供されているウェブメールのサービスのことです。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

解決済

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

donguriko
donguriko

総合スコア19

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Gmail

GmailとはGoogleによって提供されているウェブメールのサービスのことです。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

1回答

0リアクション

0クリップ

195閲覧

投稿2022/08/11 13:48

編集2022/08/12 01:36

前提

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

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

<シート1のイメージ>
イメージ説明

実現したいこと

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

<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列の内容をキーに、シート2:メールアドレスのテーブルを参照し、
O列ブランクのN列のセルにメアド補充させたらよいのではないかと
推察していますが、どのようにコード記載すればよいかまでは
自力でたどりつけないので、アドバイス欲しいです。

該当のソースコード

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

GAS

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/ツールのバージョンなど)

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

以下のような質問にはリアクションをつけましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

リアクションが多い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

YAmaGNZ

2022/08/11 23:01

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

2022/08/12 00:50 編集

①トリガーの設定について fucntion1 と function2の両方に、「ファイル変更時」にそれぞれの関数が実行されるようにトリガーを設定している、という理解でよろしいでしょうか。 ②「function1(往路)」でのメールを送信する条件が不明です。function1のコードも質問文に追記してください。
donguriko

2022/08/12 01:38

YAmaGNZさま、qnoirさま コメントありがとうございます。 以下qnoirさまからのコメントに回報します。 ①ご認識いただいているとおりです。 ②大変失礼いたしました。  該当のソースコード欄の冒頭にfunction1のコードを追記させて  いただきました。よろしくお願いいたします。

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Gmail

GmailとはGoogleによって提供されているウェブメールのサービスのことです。

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。