GmailとGoogle SpreadSheetの連携

#はじめに
ベンチャー企業から転職してきた新しい人がAdventカレンダー書こうよ！って言い出したので、半年前に作ったGmailとGoogleSpreadSheetの連携って意外とやりたい人いるのでは。と思って書きました。
ほんとに初めてGASを書いてみる人も、簡単にできるので是非お試しあれ


####自己紹介
某パッケージベンチャー企業　開発者Java,JavaScript（3.5年）夜中2時まで働いて9時に出社する暗黒期も･･･
⇒　反動でニート（半年）
⇒　ランスタッド社内SE　（4月で丸3年。はや。）
てゆかこれで年齢ばれるやつや･･･

趣味：キックボクシング　犬（ポン太）の世話
ITスキル：低め。でも思ったことが形になるのって楽しいよね、開発楽しいね！っていうありがちなタイプ。
　　　　　インフラ系は全然無理
　　　　　コミュニケーションだけで何とか生きてます



#GmailとGoogleSpreadSheetをGASで連携する

###半年間まともに動いていた処理が半年たった今、盛大にばぐっているので直すついでにまとめる。

###半年間まともに動いてたのになぁ･･･

　半年前くらいに、GmailとGoogleSpreadSheetを連携して、
　Gmailに入ってきたメールデータを一覧表示できるようにスクリプトを組みました。
　ここ半年くらいまともに動いてたのに、ここに来て盛大にばぐっている･･･（なぜ･･･）
　とりあえず修正しながらドキュメントととしてまとめます。

　そもそも何で作ったかというと、IT本部にくるデータ取得の依頼を管理していた
　ワークフローシステムをある日突然の使わない宣言が下されたから････（代替案なし）
　それ以来、メールで依頼が飛んでくるようになり、それはそれはカオスな状況となり、
　来ていた依頼をすっぽかす事態が多発したからです。

　この処理でやりたかったことは、シンプルに以下2つ。
　**1.　特定のキーワードが含まれるメールをスプレッドシートに転記する**
　　　-　メールは1日前までさかのぼって確認し、取りこぼしのないようにする
　　　-　既にデータがスプレッドシート内に存在する場合は、転記しない
　**2. シート内をフィルタ＋ソートして、スプレッドシート内を見やすくする**
　　　-　「対応済」のステータスのデータはフィルタして見えないようにする
　　　-　対応希望日順に並べて対応漏れのないようにする

###具体的にはこんな感じ･･･

　**前提条件**
　1.　依頼はメールの題名か、メール本文内のどこかに必ず「**データ抽出**」という文言が入っている（はいってないものはゴメンナサイ）
　2.　メールは、必ず特定のアドレスに送信される（データ抽出を行うチームのMLなど）
　3.　スプレッドシートには以下のカラムがある
「管理用No.」「メール題名」「申請日」「希望日」「ステータス」「対応者」「メール内容」

ちなみに、GASはスプレッドシートの　ツール>スクリプトエディタ　をクリックして開いて編集が可能です
![makeNewScript.png](https://qiita-image-store.s3.amazonaws.com/0/311091/c7ae3337-7079-d60b-18aa-8e297af8a8a9.png)







###メールの内容をスプレッドシートに転記する

```JavaScript:

function mailToSheet () {
  // スプレッドシートURL
  var sheet_url = 'スプレッドシートのURL';
  
  // 書き込み先のシート
  var sheet_name = '書き込みしたいシート名';
  
  // 申請番号を管理しているシート（いけてない番号管理方法）
  var num_sheet = 'シート11';
  
  // Gmailの抽出条件　*Gmail検索の検索窓に出ているやつがそのまま使える※1
  var mail_query = 'Gmailの検索条件';

  //スプレッドシートを開いて、シートを指定する
  var ss = SpreadsheetApp.openByUrl(sheet_url);
  var sheet = ss.getSheetByName(sheet_name);
  var num_sheet = ss.getSheetByName(num_sheet);
  
  //スプレッドシート内のデータ
  var existence_keys = fetchExistenceKeys();
  //メールデータ
  var mail_data = fetchMailData();
  
  //取得したメールデータ分処理をまわす
  for (var i = 0; item = mail_data[i]; i++) {
    //スプレッドシート内に存在しない場合、行を追加する
    if (!existence_keys[generateKey(item)]) sheet.appendRow(itemToRow(item));
  }  


  //メールのデータを配列に格納
  function fetchMailData () {
    var result = [];
    var threads = GmailApp.search(mail_query);//Gmailを特定の検索条件で取得
    var message = GmailApp.getMessagesForThreads(threads);
    
    for (var i = 0; i < message.length; i++) {    
       var item = {};
       item['date'] = message[i][0].getDate();//メールの受信日
       item['due_date'] = makeJstDateFormat(makeDueDate(message[i][0].getDate()));//メール受信日＋5日
       item['date_format'] = makeJstDateFormat(message[i][0].getDate());//メール受信日をyyyy/MM/ddの形式に
       item['subject'] = trimData(message[i][0].getSubject().toString().replace("【データ抽出申請】","").replace("【データ抽出申請書】","").replace("【データ抽出申請依頼】","").replace("【データ抽出】","").replace("【データ抽出依頼】",""));//みんな好きに題名を入れてくるので、ありがちな文言を除去
       item['from'] = removeMailAdress(message[i][0].getFrom());//送信者情報からメールアドレス部分を除去
       item['body'] = message[i][0].getPlainBody().slice(0,200);//メール本文を200文字でカット
       result.push(item);
    }
    return result;
  } 
 
  //スプレッドシート内にあるデータの取得（既存データの取得）
  function fetchExistenceKeys () {
    var existence_keys = {};
    var sheet_data = sheet.getDataRange().getValues();
    for (var i = 0; row = sheet_data[i]; i++) {
      existence_keys[generateKey(rowToItem(row))] = true;
    }
    return existence_keys;
  }

  //比較用のキーの作成（日付とメールの題名で同じとみなすため、日付_題名　というキーを作成する）
  function generateKey (item) {
    return makeJstDateFormat(new Date(item['date'])) + '_' + item['subject'];
  }

  //スプレッドシートのデータ比較するために形を整える
  function rowToItem (row) {
    var item = {};
    item['subject'] = trimData(row[1]);
    item['date'] = trimData(row[2]);
    return item;
  }

  //Itemのデータをスプレッドシートに入れるために形を整える
  function itemToRow (item) {
    var row = [];
    row[0] = "18-" + getNum();//18年度の何個目か？のカウント（ほんとは今のタイムスタンプから年度を取るべきだけどさぼり）
    row[1] = item['subject'];
    row[2] = item['date_format'];
    row[3] = item['due_date'];
    row[4] = item['from'];
    row[5] = "未対応";
    row[6] = "対応者未定";
    row[7] = "";
    row[8] = item['body'];
    return row;
  }


  //データ抽出の管理番号を取得
  function getNum(){
    var last_row_num = num_sheet.getLastRow() -1; 
    var data = num_sheet.getDataRange().getValues();
    var max_num = data[last_row_num][0];
    var return_num = Number(max_num) + 1;
    var row = [];
    row[0] = return_num;
    num_sheet.appendRow(row);
    return return_num;
  }


  //空白を除去する
  function trimData(target){
    if (target == null || target == undefined){
      return "";
    }
    return target.toString().replace(/(^\s+)|(\s+$)/g, "");
  }


  //送信者からメールアドレスと「”」を除去する　
　//送信者データが「てすと　太郎<test@gmail.com>」のように入ってくるので、<>の間の文字を除去
  function removeMailAdress(mailAdress){
    var startIndx = mailAdress.indexOf('<');
    return mailAdress.slice(0, startIndx).replace("\"", "").replace("\"", "");
  }


  //申請日＋5日の日付を作る（本当は営業日換算だけどめんどくさいから単純に＋5日）
  function makeDueDate(arg_date){
    var date = new Date(arg_date);
    date.setDate(date.getDate()+5);
    return date;
  }


  // 日付をyyyy/MM/ddの形式に整える
 function makeJstDateFormat(arg_date){
    return Utilities.formatDate(arg_date,'Asia/Tokyo', 'yyyy/MM/dd');
 }
}


```
※1　設定できる値は↓のメール検索条件の文言がそのまま利用できる
![mailCondition.JPG](https://qiita-image-store.s3.amazonaws.com/0/311091/f16a1f18-b0bb-90f9-8c9a-2873fdd70e6d.jpeg)






###スプレッドシートの内容をフィルタを掛けてソートする

```JavaScript:
function sortAndFilter(){
  // スプレッドシートURL
  var sheet_url = 'シートのURL';
  
  // ソートしたいシート
  var sheet_name = 'シート名';

  var ss = SpreadsheetApp.openByUrl(sheet_url);
  var sheet = ss.getSheetByName(sheet_name);

  doFilter();
  doSort();
  
  //シートを特定項目でソートする
  function doSort(){
   var range = sheet.getRange("A3:J");//1行目、2行目は題名なのでソート・フィルタの対象にしない
   range.sort([{column: 4, ascending: true}]);//4カラム目を昇順に並べる
  }
  

  //シートを特定カラムでフィルタする
  function doFilter(){
    var filterSet,columnIndex,request  
    filterSet = {};
    filterSet.range = {
     sheetId:sheet.getSheetId()
    }; 

    filterSet.criteria = {};
    columnIndex = 5;//フィルタをかけるカラムNo
    filterSet['criteria'][columnIndex]={
      'hiddenValues':["対応済","却下","対応不要"]//フィルタで非表示にしたいステータス
    };

    /*requestメソッドでAPI接続してフィルターをかける*/
    request = {
      "setBasicFilter": {
        "filter": filterSet
      }
    };

    /*updateメソッドでシート更新*/
    Sheets.Spreadsheets.batchUpdate({'requests':[request]},ss.getId());
  }
}
```

##トリガーを設定して自動実行する
処理を作成したら、トリガーを設定して自動実行できるようにします。

トリガーの設定はここから↓
![Trigger.png](https://qiita-image-store.s3.amazonaws.com/0/311091/af7500f6-536c-3b18-6ca9-747fedf33d6a.png)

今回、私の作ったロジックは、mailToSheetは5分に1回、sortAndFilterは1日に1回の設定にしました。

![Trigger2.JPG](https://qiita-image-store.s3.amazonaws.com/0/311091/cd490c6a-6576-c9de-8b37-506f56c11e2e.jpeg)
![Trigger3.JPG](https://qiita-image-store.s3.amazonaws.com/0/311091/0c24b62a-d999-8ee8-179b-32860abf103f.jpeg)


#最後に
だんだん新しい言語とかとっつきにくくて嫌だな～って思ってしまいがちですが、GASってすっごいとっつきやすいし、ぐぐったらたくさん情報出てくるし、めちゃくちゃ簡単！！！って思いました。



##おまけ
バグっていた箇所がわかった････
キーを比較する場所でTrimかけてるが、そのTrimが全部にかかってなくて、偶然前にスペースが入っている題名のメールが届いてたため、無限に行が作られていた･･･という････（なんだー）