More than 5 years have passed since last update.

GASを使って送信失敗したメールアドレスをスプレッドシートに記載するスクリプトを書いた

Posted at 2019-06-12

何がしたかったか

↓ 前回書いた記事の完全版を作りたかったので実装してみた。
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;
}