【Google App Script】Gmail + スプレッドシートの連携がホントに凄すぎるので布教したい

メール...それは偉大なツール。
インターネット間の情報伝達の手段であり、重要な情報、ツールの授受まで幅広く、その利便性は認知されている。

便利が故、みんな使う。ありとあらゆる情報が受信トレイ。
そこに蓄積されていく。
大切な情報も、どうでもいい情報も沢山沢山積み重なって・・・
紙から逃れられても、情報の山から逃れられない・・・。
ああ、大切な情報はどこかしら・・・？

という状況ありません？（あんまりないかもですが）

給与計算という仕事を見つめなおしているのですが、
業務の大切なポイントに「情報の整理」があります。
メールの情報の整理という課題、これに対して

GAS（Google App Script）を使ってGmailの機能を連携すれば、
なんと自動で！情報が正確にスプレッドシートに蓄積されていく仕組みが作れるんです！！

とってもとぉーっても便利ですので是非ご興味あれば見てみてください！

GmailとGoogleスプレッドシートの連携

GAS（Google App Script）はGoogleサービスの可能性を高める魔法の呪文です。時間ベースやイベントベースでスクリプトを自動実行できます。

Google Apps Script: Google Workspace を自動化、統合、拡張。

凄いですよね！勝手に決めたタイミングで動いてくれるんですよ！

例えば

毎朝7時にスプレッドシートの一番下のところにこんな情報追加して～

みたいなオーダーも可能。

スプレッドシートに限らずGmail、Document、DriveさらにはHTMLサービスを使ってWebアプリまで作れちゃいます。夢が広がるツールです。

さあ、そこでGmailとGoogleスプレッドシートでどんなことを実現するか、シナリオはこちら

1.Gmailの検索機能を使って情報を集めたいメールをまとめる
2.メールの文面から条件に合致する（正規表現を使います）文面を抜き出す
3.抜き出した情報をスプレッドシートに書いていく

というシナリオです。
こう見るとシンプルですね。

コードにするとややこしいのかな？という印象もありますが
一歩一歩読み解いていくと慣れていくと思います。

まずはコード全文

function summary_mails(){
 var myAddress = Session.getActiveUser().getEmail(); // 自分のメールアドレス
 var sheet = SpreadsheetApp.getActive().getSheetByName('シート1'); // 情報を書くスプレッドシート
 var range = sheet.getDataRange(); // データが入っているところ全て
 var lastRow = range.getLastRow(); // 最終行
 // 受信トレイを検索する
 var threads = GmailApp.search('newer_than:100d label:gas_amazon'); // 直近100日のラベル「」を検索する方法です
 // ここからループ
 for (var i=0; i < threads.length; i++) {
   var messages = threads[i].getMessages();
   for (var j=0; j < messages.length; j++) {
     if(findId(messages[j].getId(),lastRow,sheet) != true && !messages[j].getSubject().match(/^re:|^fwd:|^\[.*\].*re:|^\[.*\].*fwd:/i)){
       let MailSubject = messages[j].getSubject(); //メールタイトル
       MailSubject =　MailSubject.replace("Amazon.co.jpでのご注文",""); //余計な文字消しちゃいましょう
       let MailDate = messages[j].getDate();　//メールの日付
       let MailBody = messages[j].getPlainBody(); //Amazonの自動返信メールはHTML形式で若干今回はやりづらいのでプレーンテキストで取ります
       let orderAmount = MailBody.match(/(?<=注文合計：).*/g); //注文合計が複数に分かれているケースに対応
       for (let k=0; k < orderAmount.length; k++){
         lastRow++; //ここで書き込み先を指定します。最終行にプラス1してます
         sheet.getRange(lastRow,1).setValue(messages[j].getId());//ID
         sheet.getRange(lastRow,2).setValue(MailDate);//受信日
         sheet.getRange(lastRow,3).setValue(MailSubject);//メールタイトル 
         sheet.getRange(lastRow,4).setValue(orderAmount[k]);//注文合計
       }
     }
   }
 }
}
// 重複するIDがないか調べる
function findId(mailID, lastRow, sheet) {
 for (j = 1; j <= lastRow; j++) {
   if(sheet.getRange(j, 1).getValues() == mailID) {
     return(true);
   }
 }
 return(false);
}

Black FridayセールということもあるのでAmazonの注文後に自動配信されるメールから
注文金額を抜き出していくものを作ってみましょう！

※事務のご担当者様、自動配信のメールに作業に必要な情報が入っている！は絶好の勝ちパターンです！

要素を分解して解説します。

準備

情報をまとめるスプレッドシートが必要です。
ヘッダーは自分の取りたい項目を選ぶ形で構いませんが
必ずメールIDは入れましょう。

Googleサービスの特徴ですが、あらゆるものにIDが振られます。

今回はこんな形でスプレッドシートを作ってみました。
[ ID , 受信日 ,　メールタイトル , 注文合計 ]

例えば「経費精算の情報が自動メールで着て蓄積される」であったり、
「Googleフォームの回答から給与に反映する数字が来る」といった場合は
蓄積された情報をそのままデータ受け入れ！で終わってしまうという
スーパースマートな仕組みができます！

さらには「特定のメールに対して、ケースバイケースで決まったメールを返す」であったり「業務コードの管理から担当者、業務のステータスを更新する」という応用も可能。夢広がりまくりですね。

日常ではあまり必要ないですが「業務」では本当に素晴らしいコードです。
前置きが長いですがやってみましょう！

1.Gmailの検索機能を使って情報を集めたいメールをまとめる

タイトルだけ見るとなんか難しそう・・・。と思いがちですが
Gmailではメールのフィルタルールでラベルの自動付与ができますよね？

メールのフィルタルールの設定の仕方は簡単

ここ押して

こう

Amazonチャージなどは除外したかったのでメールの文面に条件を細かく指定しました。
今回は実験用に「GAS_Amazon」というラベルを付けます。

ここで検索をかけている技は、下のコードです。

  // 受信トレイを検索する
 var threads = GmailApp.search('newer_than:100d label:gas_amazon'); // 直近100日のラベル「」を検索する方法です

2.メールの文面から条件に合致する（正規表現）文面を抜き出す

メールの受信日、タイトル、メール文面をそのまま変数に入れてしまいます。

let MailSubject = messages[j].getSubject(); //メールタイトル
MailSubject =　MailSubject.replace("Amazon.co.jpでのご注文",""); //余計な文字消しちゃいましょう
let MailDate = messages[j].getDate();　//メールの日付

少し解説すると、Amazonの注文履歴メールはHTMLメールなのでこのコードは

let MailBody = messages[j].getPlainBody(); //Amazonの自動返信メールはHTML形式で若干今回はやりづらいのでプレーンテキストで取ります

プレーンボディというテキストで取得しています。
業務であれば

let MailBody = messages[j].getBody();

で問題ないと思いますが、内容見て決めてください。

下の関数「findId」では重複回避しています。
ここでIDの登場です。

スプレッドシートの最終行まで都度調べてIDが同じものがないかチェックしているんですよね。

function findId(mailID, lastRow, sheet) {
 for (j = 1; j <= lastRow; j++) {
   if(sheet.getRange(j, 1).getValues() == mailID) {
     return(true);
   }
 }
 return(false);
}

同じもの入力しても仕方がないので、それを除外する回避策です。

また「返信、転送」といった面倒なケースもあるのでそれを省くためにこの正規表現を使っています。
返信の「re:」や転送の「fwd:」を省くケースですね。また先頭に「re:」「fwd:」が来るとは限らないのでその対策も詰めました。

!messages[j].getSubject().match(/^re:|^fwd:|^\[.*\].*re:|^\[.*\].*fwd:/i))

正規表現は苦手なので調べながらなんとか仕立てたものです・・・。
インターネットのお知恵に感謝

さあ条件分岐が終わったので転機・・・と思いきやAmazonのメールには一つのメールに複数の明細が含まれるケースもあるようです。
凄く単純な分岐で申し訳ないですが「注文合計：」がいくつ入っているか、で今回は検証してみましょう。

let orderAmount = MailBody.match(/(?<=注文合計：).*/g);

3.抜き出した情報をスプレッドシートに書いていく

さあ、いよいよ情報を書いていきます！

for (let k=0; k < orderAmount.length; k++){
 lastRow++; //ここで書き込み先を指定します。最終行にプラス1してます
 sheet.getRange(lastRow,1).setValue(messages[j].getId());//ID
 sheet.getRange(lastRow,2).setValue(MailDate);//受信日
 sheet.getRange(lastRow,3).setValue(MailSubject);//メールタイトル 
 sheet.getRange(lastRow,4).setValue(orderAmount[k]);//注文合計
}

この作業が合致したメールの数だけ繰り返されるわけですね。

実験

動かしてみましょう！！
条件は私の直近100日のAmazonの注文情報です！

最初は要認証です。認証しないと動きません。

結果はこちら！！

おお～！！できました。

さらに自動化にはもうワンステップ！

GoogleサービスのCoolな仕組み、トリガーを使います。

ここから

時間主導型のトリガーを選択。
毎日0時にでもやってもらいましょう。

これで毎日自動更新される管理表の完成です。

自動ですよ！自動！！！
凄くないですか！？

事務作業の一つのポイントに

「いかに効率的に正確に情報を集約するか」

といったことがあると思ってます。

情報の抜け漏れがないように頑張って入念なチェック、
Wチェック、管理表の管理表の管理表の・・・

全部無駄です。仕組みでやってしまいましょ！

改めて給与計算業務を見直したときにもの凄く助かったな、というとっておきのスクリプトを紹介しました。

パターンが決まっていれば対応できるので気になることあればお気軽に聞いてくださいね！

楽しく、ラクしましょ！

今回もお付き合いいただき、ありがとうございました！！