何がしたかったか

↓ 前回書いた記事の完全版を作りたかったので実装してみた。
GmailでMail Delivery Subsystemから来たメアドをgasで取得する【初心者向け】

やりたいことはこんな感じ

背景
- セミナーの参加者のメアドを紙で回収してる
- それを目で見てパソコンに打ち込んでメール送ってた
- 必ず数件メアド間違ってて Mail Delivery Subsystem から返事来る
- これをGmail見てメアドから電話番号調べて電話するのが超面倒くさかった
やりたかったこと
- Mail Delivery Subsystem に来たメールを自動でスプレッドシートに書き込みたい
- 1日1回、出社前には集計されてほしい
- 前に調べたメールを重複して調べないでほしい

結果のイメージ

こんな感じで、B列に送信した時間、C列に送信失敗したメアドが記載されていきます。
初回の稼働と、トリガー(毎朝出社前の6時に動かす)はUIでできるようにしてみました。

実際のコード

// from指定でgmailを検索
var searchString = "from:'Mail Delivery Subsystem'";
var searchTime   = getSearchTime();


/**
 * トリガーを設定する
 */
function setTrigger()
{
  // すでにトリガーがあったら追加しない
  if (ScriptApp.getProjectTriggers().length > 0) return 0;

  // 毎日6時に実行する
  ScriptApp.newTrigger('myFunction').timeBased().atHour(6).everyDays(1).create();
}

/**
 * 検索対象の時間を取得(トリガーに依存)
 */
function getSearchTime() {
  const date     = new Date();
  const unixTime = date.getTime();
  const now      = Math.floor(unixTime/1000); 
  // ※ 集計時間をアレンジする場合はここを修正してください
  // 例えば、6時間起きに実行する場合は、21600 (60s x 60m x 6)を入れてください
  const term     = now - (86400); // 24時間前 (60s x 60m x 24h) ※ 集計したい時間ごとにこの時間を変える
  const termStr  = term.toString(); 
   
  return strTerms = 'after:'+ termStr;
}

/**
 * メインの関数
 */
function mainFunction() {
  // 書き込み対象のシート名指定 (getActiveSheetでもOKです)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // ① 送信失敗したメールを探す
  // max指定可能な500件で取得
  var myThreads = GmailApp.search(searchString + ',' + searchTime, 0, 500);
  var num = 2;
  
  if (myThreads.length === 0) {
    Logger.log("過去24時間での送信失敗はありませんでした");    
    return;
  }
  
  // ② メールがあったら、スプレッドシートに記載されてるスレッドIDの一覧を取得してくる
  var pastFailureIds = getPastFailureIds(sheet);  

  // ③ 送信に失敗したメールの日付・メアドのリストを取得する
  var insertData = getInsertData(myThreads, pastFailureIds);
  
  // もし存在しなかったら何もせずに終わる
  if (insertData == 0) {
    return 0;
  }

  // ④ 取得した件数分だけ、2行目から行を追加
  // データを入れる前に対象のデータ分の行を追加しないと行がずれてしまう
  sheet.insertRows(2, insertData.length);

  // ⑤ スプレッドシートに記載してく
  for (var num in insertData) {
    // javascriptの場合は、普通に「num+2」とかやってしまうと、「02」「12」とかになってしまうので想定外の行に値が空いってしまうのでintに変える
    var targetRowNum = parseInt(num) + 2;
    sheet.getRange(targetRowNum, 2).setValue(insertData[num]["threadID"]);
    sheet.getRange(targetRowNum, 3).setValue(insertData[num]["date"]);
    sheet.getRange(targetRowNum, 4).setValue(insertData[num]["failureMailAddress"]);
  }
}

/**
 * 挿入対象のメール送信失敗データを返す
 */
function getInsertData(myThreads, pastFailureIds)
{
  var insertData = [];
  
  for (var n in myThreads) {
    var thread            = myThreads[n];   
    var msgs              = thread.getMessages();
    var failedMailAddress = "";
    var date              = "";
    var threadID          = thread.getId();

    for(m in msgs) {
      var msg        = msgs[m];
      var tmpAddress = getFailedMailAddress(msg.getBody());
      
      // 複数スレッドになると、うまく取れない場合があるので、取得可能な場合のみ
      if (tmpAddress !== '') {
        failedMailAddress = tmpAddress;
      } 
      
      date = msg.getDate();     
    }
    
    // 過去に同じスレッドIDがあったらスルーする
    if (pastFailureIds !== [] && pastFailureIds.indexOf(threadID) !== -1) {
      continue;
    }
    
    insertData.push({"threadID" : threadID, "date" : date, "failureMailAddress" : failedMailAddress});
  }  
  
  return insertData;
}

/**
 * 送信が失敗したメールアドレスを返す
 */
function getFailedMailAddress(body)
{
  var reg = /<b>.*?<\/b>/;
  if (body.match(reg) === null) return '';
  return body.match(reg)[0].replace('<b>', '').replace('<\/b>', '');
}

/**
 * 過去の検索したメールのスレッドIDを返す
 */
function getPastFailureIds(sheet)
{
  var pastFailureIds = [];

  for (var i = 2; i <= sheet.getLastRow(); i++)
  {
    pastFailureIds.push(sheet.getRange(i, 2).getValue());
  }
  
  return pastFailureIds;
}

ざっくりと処理の流れ

① 送信に失敗してるメール (Mail Delivery Subsystemから24時間以内に来た) を探す
② メールがあったら、スプレッドシートに記載されてるスレッドIDの一覧を取得してくる
- 重複したものを書き込むのを避けるため
③ 送信に失敗したメールの日付・メアドのリストを取得する
④ 取得した件数分だけ、2行目から行を追加
- 追加しないと、1番上の行に追加していけないため
⑤ スプレッドシートに記載してく

誰でもできる使い方説明

1. スプレッドシートを用意します

適当にスプレッドシートを新しく作ってください。
名前をつけて、B1,C1,D1にそれぞれ「ID」「受信日時」「送信失敗したメールアドレス」と書いておきます。

2. スクリプトエディタを開いてコピペします

「ツール」>「スクリプトエディタ」で起動します。

myFunctionを全部選択して、↑のコードをコピペします。

保存すると、名前を入れろと言われるので、適当に入れて保存。

3. スプレッドシートにUIつくる

スプレッドシートに戻って、「挿入」>「図形描画」から図形を入れます。

適当に角丸の図形を選んで作っていきます。

テキストも入れましょう。

こんな感じで2つできればOKです。

このボタンにスクリプトを割り当てるので、ボタンにカーソル合わせると右上に●ポチ３つのマークが出てくるので、これをクリックして「スクリプトを割り当て」をクリック。

「初回失敗メール検知稼働」にはmainFunctionを割り当て。

「毎朝6時に動かすトリガーを設定」にはsetTriggerを割り当て。

4. 動かしてみる

早速「初回失敗メール検知稼働」を押してみましょう。
最初は承認が必要です。と出ますが、全部許可します。
※ ちなみに、最初は24時間以内の送信失敗メールだけ検知します。
※ 何回動かしても重複分は除外されるので問題ないです

次に、「毎朝6時に動かすトリガーを設定」を押して、トリガーが設定されたか確認してみましょう。
スクリプトエディタの「編集」>「現在のプロジェクトのトリガー」をクリック

1つ出来てますね。右端の鉛筆マークをクリックして詳細を見ます。

ちゃんと毎日6~7時に動くように設定されてます。

これで完全自動化ですね！
無駄な仕事はどんどん業務効率化して定時に帰りましょう！！