スプレッドシートから対応未完了の人だけにメールを配信したい

実現したいこと

スプレッドシートで〆切案件の管理をしているので
未完了に対してのみ自動でメールを配信されるようにしたい。

出来ているところ

・スプレッドに宛先リスト（件名・宛先・差込データ）と本文のシートをそれぞれ作成しています。
・現状その宛先リストと本文を用いてスクリプトを作成し自動メール配信はできるようになっています。
なので後は完了未完了のフラグをつけて
未完了であればメール配信、完了であれば配信しない。というスクリプトを作成すれば1次完成になります。

できていないところ

メール配信の「sendmail」の前に以下をスクリプトを付けて配信をすると
表の一番上が未配信だったらすべての表の人にメールが配信されてしまい
逆に一番上が配信済の状態だと、だれにも配信されない状態になってしまいます。

for(var x =11;x<lastcolumn;x++){
var Flag =myValues[2][x];
if(Flag !==1){
ーーー
12列目に未完了Flagを付けて”1”以外であれば配信とする。
このfor分の箇所以外を除けばメール配信はされるので条件のところさえあえば
区別してメール配信できるようになると思いますがうまくいかずに、投稿させていただきます。

function sendMail() {
  //------------- （１）宛先リストから処理対象の行数を取得 -----------------------
  var book = SpreadsheetApp.getActiveSpreadsheet();　　　　　// スプレッドシート（MS Excelで言う所のブック）を取得
  var sheetAtesakiList = book.getSheetByName("宛先リスト");　// 宛先リストシートを取得
  var datRange = sheetAtesakiList.getDataRange();　  　　　 // データが入力されている範囲を取得
  var myValues = datRange.getValues();　  　　　　　　　　　　//指定した範囲の値を格納
  var numRows = datRange.getNumRows();  　　　　　　　　　　　// 処理対象の行数を取得
  var lastcolumn = sheetAtesakiList.getLastColumn();　　　　// 処理対象の最終列を取得
  var lastRow = sheetAtesakiList.getLastRow();　　　　　　　// 処理対象の最終列を取得
  
  //------------- （２）メール本文の情報を取得 -----------------------
  var sheetMailHonbun = book.getSheetByName("メール本文");　// メール本文シートを取得  
  var subject = sheetMailHonbun.getRange(2, 3).getValue();// メール件名を取得 
  var body = sheetMailHonbun.getRange(4, 3).getValue();　 // メール本文を取得
  var start = sheetAtesakiList.getRange(1, 5).getValue(); // 処理を開始する行番号を取得

  //------------- （３）メール送信処理 -----------------------
  for (var i = start;i <= numRows;i++)  {　　　　　　　　　　    // 対象行の差込データを取得
    var sasikomiData1 = sheetAtesakiList.getRange(i, 6).getValue();
    var sasikomiData2 = sheetAtesakiList.getRange(i, 7).getValue();
    var sasikomiData3 = sheetAtesakiList.getRange(i, 8).getValue();
    var sasikomiGoSubject = subject;
    var sasikomiGoBody = body;
      
    // 差込データ1が入力されている場合は、差込処理を行う（件名＋本文）
    if (sasikomiData1 != "") {
      sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ1}}", sasikomiData1, "g");
      sasikomiGoBody = sasikomiGoBody.replace("{{差込データ1}}", sasikomiData1, "g");
    }

    // 差込データ2が入力されている場合は、差込処理を行う
    if (sasikomiData2 != "") {
      sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ2}}", sasikomiData2, "g");
      sasikomiGoBody = sasikomiGoBody.replace("{{差込データ2}}", sasikomiData2, "g");
    }

    // 差込データ3が入力されている場合は、差込処理を行う
    if (sasikomiData3 != "") {
      sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ3}}", sasikomiData3, "g");
      sasikomiGoBody = sasikomiGoBody.replace("{{差込データ3}}", sasikomiData3, "g");
    }

    // 対象行の宛先を取得  
    var to = sheetAtesakiList.getRange(i, 2).getValue();
    var cc = sheetAtesakiList.getRange(i, 3).getValue();
    var bcc = sheetAtesakiList.getRange(i, 4).getValue();

    for(var x =11;x<lastcolumn;x++){
    var Flag =myValues[2][x];
    　if(Flag !==1){

    MailApp.sendEmail(to,sasikomiGoSubject,sasikomiGoBody,{cc:cc,bcc:bcc});
  }
    // 配信状態を設定
    sheetAtesakiList.getRange(i, 5).setValue("配信済");
    }}
    }

ご確認いただければ幸いです。

行動規範の内容に同意します

回答1件

ベストアンサー

インデントはちゃんとやったほうがいいですよ。こうなりますよね？

javascript
1function sendMail() {
2    //------------- （１）宛先リストから処理対象の行数を取得 -----------------------
3    var book = SpreadsheetApp.getActiveSpreadsheet();　　　　　// スプレッドシート（MS Excelで言う所のブック）を取得
4    var sheetAtesakiList = book.getSheetByName("宛先リスト");　// 宛先リストシートを取得
5    var datRange = sheetAtesakiList.getDataRange();　  　　　 // データが入力されている範囲を取得
6    var myValues = datRange.getValues();　  　　　　　　　　　　//指定した範囲の値を格納
7    var numRows = datRange.getNumRows();  　　　　　　　　　　　// 処理対象の行数を取得
8    var lastcolumn = sheetAtesakiList.getLastColumn();　　　　// 処理対象の最終列を取得
9    var lastRow = sheetAtesakiList.getLastRow();　　　　　　　// 処理対象の最終列を取得
10  
11    //------------- （２）メール本文の情報を取得 -----------------------
12    var sheetMailHonbun = book.getSheetByName("メール本文");　// メール本文シートを取得  
13    var subject = sheetMailHonbun.getRange(2, 3).getValue();// メール件名を取得 
14    var body = sheetMailHonbun.getRange(4, 3).getValue();　 // メール本文を取得
15    var start = sheetAtesakiList.getRange(1, 5).getValue(); // 処理を開始する行番号を取得
16  
17    //------------- （３）メール送信処理 -----------------------
18    for (var i = start;i <= numRows;i++)  {　　　　　　　　　　    // 対象行の差込データを取得
19      var sasikomiData1 = sheetAtesakiList.getRange(i, 6).getValue();
20      var sasikomiData2 = sheetAtesakiList.getRange(i, 7).getValue();
21      var sasikomiData3 = sheetAtesakiList.getRange(i, 8).getValue();
22      var sasikomiGoSubject = subject;
23      var sasikomiGoBody = body;
24  
25      // 差込データ1が入力されている場合は、差込処理を行う（件名＋本文）
26      if (sasikomiData1 != "") {
27        sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ1}}", sasikomiData1, "g");
28        sasikomiGoBody = sasikomiGoBody.replace("{{差込データ1}}", sasikomiData1, "g");
29      }
30  
31      // 差込データ2が入力されている場合は、差込処理を行う
32      if (sasikomiData2 != "") {
33        sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ2}}", sasikomiData2, "g");
34        sasikomiGoBody = sasikomiGoBody.replace("{{差込データ2}}", sasikomiData2, "g");
35      }
36  
37      // 差込データ3が入力されている場合は、差込処理を行う
38      if (sasikomiData3 != "") {
39        sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ3}}", sasikomiData3, "g");
40        sasikomiGoBody = sasikomiGoBody.replace("{{差込データ3}}", sasikomiData3, "g");
41      }
42  
43      // 対象行の宛先を取得  
44      var to = sheetAtesakiList.getRange(i, 2).getValue();
45      var cc = sheetAtesakiList.getRange(i, 3).getValue();
46      var bcc = sheetAtesakiList.getRange(i, 4).getValue();
47  
48      for(var x =11;x<lastcolumn;x++){
49        var Flag =myValues[2][x];
50        if(Flag !==1) {
51            MailApp.sendEmail(to,sasikomiGoSubject,sasikomiGoBody,{cc:cc,bcc:bcc}); //←**
52        }
53        // 配信状態を設定
54        sheetAtesakiList.getRange(i, 5).setValue("配信済");
55      }
56    }
57}

こう見ると、**をマークしているMailApp.sendMailはフラグをチェックするために設けられたはずのforの中にいます。
また、flagはmyValues[2][x]を使ってますが、これは3行目の12列目をさしてしまっています。

フラグは別にxでループする必要はないので多分↓こういう感じなら動きそう

javascript
1function sendMail() {
2    //------------- （１）宛先リストから処理対象の行数を取得 -----------------------
3    var book = SpreadsheetApp.getActiveSpreadsheet();　　　　　// スプレッドシート（MS Excelで言う所のブック）を取得
4    var sheetAtesakiList = book.getSheetByName("宛先リスト");　// 宛先リストシートを取得
5    var datRange = sheetAtesakiList.getDataRange();　  　　　 // データが入力されている範囲を取得
6    var myValues = datRange.getValues();　  　　　　　　　　　　//指定した範囲の値を格納
7    var numRows = datRange.getNumRows();  　　　　　　　　　　　// 処理対象の行数を取得
8    var lastcolumn = sheetAtesakiList.getLastColumn();　　　　// 処理対象の最終列を取得
9    var lastRow = sheetAtesakiList.getLastRow();　　　　　　　// 処理対象の最終列を取得
10  
11    //------------- （２）メール本文の情報を取得 -----------------------
12    var sheetMailHonbun = book.getSheetByName("メール本文");　// メール本文シートを取得  
13    var subject = sheetMailHonbun.getRange(2, 3).getValue();// メール件名を取得 
14    var body = sheetMailHonbun.getRange(4, 3).getValue();　 // メール本文を取得
15    var start = sheetAtesakiList.getRange(1, 5).getValue(); // 処理を開始する行番号を取得
16  
17    //------------- （３）メール送信処理 -----------------------
18    for (var i = start;i <= numRows;i++)  {　　　　　　　　　　    // 対象行の差込データを取得
19        var sasikomiData1 = sheetAtesakiList.getRange(i, 6).getValue();
20        var sasikomiData2 = sheetAtesakiList.getRange(i, 7).getValue();
21        var sasikomiData3 = sheetAtesakiList.getRange(i, 8).getValue();
22        var sasikomiGoSubject = subject;
23        var sasikomiGoBody = body;
24    
25        // 差込データ1が入力されている場合は、差込処理を行う（件名＋本文）
26        if (sasikomiData1 != "") {
27            sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ1}}", sasikomiData1, "g");
28            sasikomiGoBody = sasikomiGoBody.replace("{{差込データ1}}", sasikomiData1, "g");
29        }
30    
31        // 差込データ2が入力されている場合は、差込処理を行う
32        if (sasikomiData2 != "") {
33            sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ2}}", sasikomiData2, "g");
34            sasikomiGoBody = sasikomiGoBody.replace("{{差込データ2}}", sasikomiData2, "g");
35        }
36    
37        // 差込データ3が入力されている場合は、差込処理を行う
38        if (sasikomiData3 != "") {
39            sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ3}}", sasikomiData3, "g");
40            sasikomiGoBody = sasikomiGoBody.replace("{{差込データ3}}", sasikomiData3, "g");
41        }
42  
43        // 対象行の宛先を取得  
44        var to = sheetAtesakiList.getRange(i, 2).getValue();
45        var cc = sheetAtesakiList.getRange(i, 3).getValue();
46        var bcc = sheetAtesakiList.getRange(i, 4).getValue();
47  
48        var Flag = myValues[i-1][11];
49//      var Flag = sheetAtesakiList.getRange(i, 12).getValue(); //下手にmyValuesとかを使うより、toと同じやりかたで12列目を指したほうが分かりやすいのではないの？？
50        if(Flag !== 1) {
51            MailApp.sendEmail(to,sasikomiGoSubject,sasikomiGoBody,{cc:cc,bcc:bcc});
52        }
53        // 配信状態を設定
54        sheetAtesakiList.getRange(i, 5).setValue("配信済");
55    }
56}

投稿2018/11/16 11:11

papinianus

総合スコア12705

ankou

2018/11/19 01:29

いつもご回答ありがとうございます。ご教示いただいたスクリプトでうまく起動することができるようになりました。ありがとうございます。別で〆切関連のスクリプトを確認仕手いた時にループがいくつもあったのでそればかりが頭に残りループしないといけないと考えておりました。 myValuesもよくよく考えたら使う必要はないですね。アドバイスいただいたtoと同じようなもので構築いたしました。

papinianus

2018/11/19 08:25

もしjavascriptの配列をうまく使いこなせるのであれば、myValuesを使ったほうが処理が高速化します(件数にもよりますが体感できるレベルで) ただ、私は*高速*よりも、*安全*を重視するので、「一行の列の状態を見る」という同じ処理で違う方式を使うのは、バグの温床になりやすいと思っています。なので、もし高速化をしたいなら、toもccもbccもフラグも全ての箇所において、`getRange`をやめてmyValuesにするようにドラスティックに変えたほうがいいと思います。

ankou

2018/11/26 02:49

返信漏れておりました。すみません。ありがとうございます。myValuesを使ったほうが高速でより安全に対応できるとのことで対応してみます！

行動規範の内容に同意します