#はじめに
ベンチャー企業から転職してきた新しい人が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はスプレッドシートの ツール>スクリプトエディタ をクリックして開いて編集が可能です
###メールの内容をスプレッドシートに転記する
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 設定できる値は↓のメール検索条件の文言がそのまま利用できる
###スプレッドシートの内容をフィルタを掛けてソートする
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());
}
}
##トリガーを設定して自動実行する
処理を作成したら、トリガーを設定して自動実行できるようにします。
トリガーの設定はここから↓
今回、私の作ったロジックは、mailToSheetは5分に1回、sortAndFilterは1日に1回の設定にしました。
#最後に
だんだん新しい言語とかとっつきにくくて嫌だな～って思ってしまいがちですが、GASってすっごいとっつきやすいし、ぐぐったらたくさん情報出てくるし、めちゃくちゃ簡単！！！って思いました。
##おまけ
バグっていた箇所がわかった････
キーを比較する場所でTrimかけてるが、そのTrimが全部にかかってなくて、偶然前にスペースが入っている題名のメールが届いてたため、無限に行が作られていた･･･という････（なんだー）