LoginSignup
16

More than 5 years have passed since last update.

GmailとGoogle SpreadSheetの連携

Last updated at Posted at 2018-11-30

はじめに

ベンチャー企業から転職してきた新しい人が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

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


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

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

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

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

Trigger2.JPG
Trigger3.JPG

最後に

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

おまけ

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
16