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

## 何がしたかったか

↓ 前回書いた記事の完全版を作りたかったので実装してみた。
[GmailでMail Delivery Subsystemから来たメアドをgasで取得する【初心者向け】](https://qiita.com/rikkeydevinfo/items/b9963658759795f0aa41)

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

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

## 結果のイメージ

![スクリーンショット_2019-06-12_20_05_46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/ea81640f-5e94-3b59-e539-c484b17080f5.png)

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

## 実際のコード

```js
// 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」「受信日時」「送信失敗したメールアドレス」と書いておきます。

![スプレッドシート作成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/cd6f120d-b84f-ffa6-a66b-9839ece23503.png)

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

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

![スクリプトエディタを起動.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/e65bab9d-de9b-ca5d-9b08-2d0b764c7dde.png)

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

![選んで.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/9698569e-7133-dec0-0fcd-68bb983d9763.png)

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

![名前を入れましょう.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/6f72241f-3bcc-5d9d-b388-94fc12eaf283.png)


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

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

![図形を挿入.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/45c381a0-0257-3c58-8e8b-620d82ecb882.png)

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

![適当に角丸の図形選んで.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/d2203017-9421-480e-7d80-88cc9f5970c8.png)

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

![テキストボックスから名前入れる.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/ee449b21-aa8b-66b2-4127-836350c5610d.png)

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

![2つ作る.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/ae9969ce-e11d-31c1-e6f7-2aa7643b3dc0.png)

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

![スクリプトを割り当て.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/7cd0c8a8-33d6-44e7-38e5-898c384b4e0e.png)

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

![mainFunctionを割り当てよう.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/b20ee540-d203-e9ab-db8d-9b49d8b98ccb.png)

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

![こっちはsetTrigger.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/9f517ecf-4a2d-dad7-d57d-fca8492d5f8d.png)

### 4. 動かしてみる

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

![承認が必要ですと出る.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/228b9786-7f0d-eaf6-f092-9113e8b8884d.png)

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

![トリガーを確認_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/1b891170-e983-5033-250a-d97dab75e2b8.png)

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

![トリガーを確認_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/8d3c10e9-61b6-812f-b858-e940a8423286.png)

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

![トリガーを確認_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/434023/15fbdc36-d9f2-0d82-9779-ee9e5839b21f.png)

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