【GAS】メール本文中の特定の文字列を抽出してスプレッドシートに転記する

こんにちは。

スタートアップ企業でプロダクトマネージャーをしているマイコです。
社内に煩雑で冗長なオペレーションを見つけると自動化・半自動化すべく、Google App ScriptやSQLを書いて実装をしています。

【Don't Repeat Yourself】ということで、またnoteに記録を残します。

今回の課題

ユーザに対して社内データベースから直接メールを送信しています。
毎回DBを確認するのは大変なので、特定のメールを送信した「ユーザ名」と「送信日」をリストとして取得したいと考えました。
データベースを除いてみたところ、メール送信日時が記録されていない。そういうカラムがないのです。

カラムを追加してもらえるようお願いはしていますが、GASを使って自力でなんとかすることにしました。

なお、個人情報保護の観点から、保存ではなく、都度限定的に取得、破棄するようにしています。

実現方法

ユーザ向けに送信しているメールにCCしているメールアドレスに、自分も含まれていることに着目。
DBにはアクセスせずとも、受信メールから送信日、ユーザ名（本文記載）、メールアドレス（送信先）を一覧として取得することができます。

本当はユーザIDも一緒に取得したかったのですが、ユーザIDはメールには含まれていないので、ここは必要な時に突合して対応することにします。

難しかったところ

今回のGASで難しかったのは、主に次の2点（私独自調べ）。それ以外は、ネット上で先人たちが残してくれたサンプルスクリプトやGASドキュメントを参照して組むことができました。

①Gmailをスレッド単位ではなくメール一つ一つを取得させる

②特定の部分の抽出をスプシ上の関数で実行させる

今回作ってみたGAS

function myFunction() {
 // 検索条件に該当するスレッド一覧を取得
 var threads = GmailApp.search('subject:メールタイトル after:2021/MM/DD before:2021/MM/DD',0,200);
 // 書き込むシートを取得
 var sheet = SpreadsheetApp.getActive().getSheetByName('任意の名前');
 let firstRow = sheet.getLastRow() + 1;
 sheet.getRange(firstRow, 1).setValue("メール送信日時");
 sheet.getRange(firstRow, 2).setValue("ユーザ名"); 
 sheet.getRange(firstRow, 3).setValue("Email");  
 sheet.getRange(firstRow, 4).setValue("ユーザ名抽出");
 var lastMessage = ""
 // スレッドを一つずつ取り出す
 threads.forEach(function(thread) {
   // スレッド内のメール一覧を取得
   let messages = thread.getMessages();
   // メールを一つずつ取り出す
   messages.forEach(function(message) {
     // メール本文を取得
     var plainBody = message.getPlainBody();
     if (plainBody === lastMessage) {
       return
     }
     lastMessage = plainBody
     // メール受信日時を取得
     var mailSent = message.getDate();
     // ユーザ名が記載されているものの一部を取得
     var nameArea = message.getPlainBody().slice(start,end);
     
     // メールアドレス＝送信先を取得する
     var emailAddress = message.getTo();
     
     // 最終行を取得
     var lastRow = sheet.getLastRow() + 1;
     
     // セルを取得して値を転記
     sheet.getRange(lastRow, 1).setValue(mailSent);
     sheet.getRange(lastRow, 2).setValue(`=LEFT(D${lastRow},FIND("*",D${lastRow})-1)`); 
     sheet.getRange(lastRow, 3).setValue(emailAddress);      
     sheet.getRange(lastRow, 4).setValue(nameArea);
   });
 });
}

実行結果

スクリプトを実行すると、図のようにSpreadsheetに出力されました！

D列は不要な情報なので、他の人と共有するときはHideしておきます。

スクリプト解説

対象スレッドを取得

​var threads = GmailApp.search('subject:メールタイトル after:2021/MM/DD before:2021/MM/DD',0,200);

GmailApp.searchを使って、対象スレッドを取得。検索条件は、件名と日付、start, maxを指定。

項目名出力

 // 書き込むシートを取得
 var sheet = SpreadsheetApp.getActive().getSheetByName('任意の名前');
 let firstRow = sheet.getLastRow() + 1;
 sheet.getRange(firstRow, 1).setValue("メール送信日時");
 sheet.getRange(firstRow, 2).setValue("ユーザ名"); 
 sheet.getRange(firstRow, 3).setValue("Email");  
 sheet.getRange(firstRow, 4).setValue("氏名抽出");

Activeになっているスプシの任意の名前のシートの１行目に項目名を出力。

メールを一つずつ取り出す

// スレッドを一つずつ取り出す
 threads.forEach(function(thread) {
   // スレッド内のメール一覧を取得
   let messages = thread.getMessages();
   // メールを一つずつ取り出す
   messages.forEach(function(message) {

Threadを一つずつ取り出す中に、message（＝メール）を一つずつ取り出す文を入れ込む（自力では解決できずにヘルプしてもらった）。

メール本文から氏名のみを表示する

これはGASのメールからの本文抽出とSpreadsheetの関数の２段階で実現しました。本来は全てScript上で処理できると美しいのですが、今のところSpreadsheetの関数を使った方が書きやすいため（私のスキル制約上）、関数を出力することで、対応です。

まず、 ユーザを含む本文の一部分をgetPlainBodyとsliceを使って抽出します。今回の対象メールはテンプレなので、特定の文字数目（start）から必ずユーザ名が表示されていたので、そこから15文字程度取れば確実にユーザ名が取得可能です（この数字をendとする）。

     var nameArea = message.getPlainBody().slice(start,end);

続いて、メール本文から抽出したものから、さらに氏名だけを抜き出す処理をします。

今回の対象メールには必ず「○○ *様」のように記載されているため、抽出したもののうち「*」の前のみを表示すると氏名のみが表示されるという特徴があります。

さらに、次の関数を使うとあるセルに表示された文字列のうち、「*」より左側のみを抽出することができます。

=LEFT(セル番地,FIND("*",セル番地)-1)

これをSpreadsheetに出力することにします。

     // セルを取得して値を転記
     sheet.getRange(lastRow, 1).setValue(mailSent);
     sheet.getRange(lastRow, 2).setValue(`=LEFT(D${lastRow},FIND("*",D${lastRow})-1)`); 
     sheet.getRange(lastRow, 3).setValue(emailAddress);      
     sheet.getRange(lastRow, 4).setValue(nameArea);

4列目＝スプシD列に抽出したメール本文から名前が書かれている部分を出力しています。

２列目は、D列に表示された文字列から"*"の左側部分だけを抽出します。列は固定ですが行を1行ずつ変えるため、${lastRow}としました。

完成です！

まとめ

ネット上のGmailメッセージをスプシに転記するスクリプトをベースに、スプシ関数を組み合わせて欲しいものが表示されるリストを作ってみました。メールから特定の情報を抽出してリスト化したいという場面は、今回に限らず、ちょこちょこあると思います。そんな時に改変して活用いただければ幸いです。

免責：非エンジニアのGAS初心者です。自分の環境では期待していた通りの動作が確認できていますが、コピペ使用される場合は動いているかご確認願いますm（_ _）m。また、こうした方がシンプルで綺麗だよ、というアドバイスも大歓迎です。