【Google Apps Script】Gmailで受信した内容をスプレッドシートに自動で転記する

単純作業とかは人の手が入るほどミスが発生する可能性が上がるため、できるだけ単純作業のタスクは自動化していきたい。

業務でGmailの内容の一部などをスプレッドシートに記載する必要があり、なんとか楽にならんか？と思って調べてたら、Google Apps Scriptを使えば何とかなりそうで、自動化に向けて試してみたのを、今回まとめてみました。

Google Apps Scriptとは？

Google Apps Script（以下「GAS（ガス）」）とはGoogleが提供しているプログラミング言語で、JavaScriptをベースにしています。

Googleのアカウントを持っていれば、誰でも利用する事ができて、Googleが提供しているサービスと連携することができます。

Googleのサービスというのは、例えば以下のとおり！

・Gmail
・スプレッドシート
・Googleドキュメント
・Googleカレンダー
・Googleドライブ
・Googleマップ

などなど

Gmailで受信できればGASで色々できるみたいですが、今回はGmailの内容の一部をスプレッドシートに自動で転記することにします。

Gmailで受信した内容をスプレッドシートに自動で転記する

Gmailを受信してから、スプレッドシートにGASを使って転記していきます。

※完成のイメージはこちら。

メールのフォーマットが決まっていたりすると、その内容を拾ってこれるので、問い合わせメールなどを集計する時に役に立つかもしれません。

※メールのイメージはこちら。

STEP1　メールにラベルを付け、フィルタを適用する。

まずはメールにラベルを付けます。

Gmailの設定 > 「ラベル」で受信したいメールにラベルを設定

次にラベルを付けたメールにフィルタを適用します。

Gmailの設定 > 「フィルタとブロック中のアドレス」で受信したいメールにフィルタを適用する。

ここで指定したラベルのメールに対して、GASが処理をしてくれます。

STEP2　スプレットシートを用意し、GASを書く

※スプレッドシートのイメージはこちら。

今回、Gmailからスプレッドシートに転記する内容は8項目に設定します。

・発注日
・注文番号
・品名
・商品のカテゴリ
・請求金額合計
・担当者名
・印刷部数
・メッセージID　← 同じメールを読み込まないように

スプレッドシートには「Gmail以外の項目も記載したいんだけど」と思うこともあるのではないでしょうか。

指定をしないと、おそらくA～Eといったように、左から右に転記されていきます。

今回はそうなるとちょっと都合が悪いので、8項目を「それぞれセルを指定して」転記していくことにします。

ツール > スクリプトエディタ

ここにGASを書いていくことになります。

以下をコピペで！

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange(7, 21, sheet.getLastRow()  + 1).getValues();

// myFunctionでもいいけど変更
function getMail01(){
 var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// シート名を記入。例えば2020とか
 var objSheet = objSpreadsheet.getSheetByName("2020");　
 var sheet = SpreadsheetApp.setActiveSheet(objSheet);
 var start = 0;

 // 取得するメールの最大件数
 var max = 100;

 // Gmailのラベル名をここに記載
 var threads = GmailApp.search('label:任意のラベル名 is:unread',start,max);
　
 // 最後の行探してそれ以降に追加　挿入開始位置
 var Messages = GmailApp.getMessagesForThreads(threads);
 var row = sheet.getLastRow() + 1;
 
 // 見出しがあるためシートの1行目を固定  
 sheet.setFrozenRows(1);
 
 // メールから日時、ご注文番号、品名、商品カテゴリ、ご請求金額合計、担当者名、印刷部数を取り出す//
 for(var i = 0; i < Messages.length; i++) {
   for(var j = 0; j < Messages[i].length; j++) {
     var id = Messages[i][j].getId();
     if(!hasId(id)){
     var body = Messages[i][j].getPlainBody();
     var date = threads[i].getMessages()[j].getDate();
  
     var regNumber = new RegExp('ご注文番号　：' + '.*?' + '\r');
     var Number = body.match(regNumber)[0].replace('ご注文番号　：', '').replace('\r', '');
     var regName = new RegExp('品名　　　　：' + '.*?' + '\r' );
     var Name = body.match(regName)[0].replace('品名　　　　：', '').replace('\r', '');
     var regCategory = new RegExp('商品カテゴリ：' + '.*?' + '\r' );
     var Category = body.match(regCategory)[0].replace('商品カテゴリ：', '').replace('\r', '');
     var regMoney = new RegExp('ご請求金額合計：' + '.*?' + '\r' );
     var Money = body.match(regMoney)[0].replace('ご請求金額合計：', '').replace('\r', '');
     var regTantou = new RegExp('担当者名　　　：' + '.*?' + '\r' );
     var Tantou = body.match(regTantou)[0].replace('担当者名　　　：', '').replace('\r', '');
     var regBusu = new RegExp('印刷部数　　：' + '.*?' + '\r' );
     var Busu = body.match(regBusu)[0].replace('印刷部数　　：', '').replace('\r', '');
     
     id
     
 // 同じメールを読み込まないように、メッセージを既読にする
 threads[i].markRead();
         
 // それぞれの項目をセルを指定してスプレッドシートへ転記
 sheet.getRange(row,1).setValue(date);
 sheet.getRange(row,2).setValue([Number]);
 sheet.getRange(row,4).setValue([Name]);
 sheet.getRange(row,5).setValue([Category]);
 sheet.getRange(row,6).setValue([Money]);
 sheet.getRange(row,7).setValue([Tantou]);
 sheet.getRange(row,8).setValue([Busu]);
 sheet.getRange(row,9).setValue([id]);
 row++
                      
 // 受信日時順の昇順に並び替え
 var range = sheet.getRange("A:U");
 range.sort({column: 1, ascending: true});                   
 }
}
}
}

// 同じメッセージIDがある場合は読み込まないように
function hasId(id) {
 var hasId = data.some(function(array, i, data) {
   return (array[0] === id);
 });
 return hasId;
}

// この記述で正規表記？
function fetchData(str, pre, suf) {
 var reg = new RegExp(pre + '.*?' + suf);
 var data = '';
   data = str.match(reg)[0]
     .replace(pre, '')
     .replace(suf, '');
 return data;  
}

参考：重複しないためにメッセージIDを取得する｜いつも隣にITのお仕事
参考：正規表現を使ってGmailの本文から文章を抜き出す｜株式会社InitialSite

途中を省くのは、面倒くさくなったからではありません（笑）
それぞれを個々に説明した方が、いいとは思うのです。
その方が、読んでくれた人にもわかりやすい。

ただ、まだまだGASを自分でもよくわかってないので、詳しく説明することができないのです。

まあそれはいいのですが、ここでおこなっている処理は以下のとおり

・指定したメールの内容をスプレッドシートに転記する
・転記する項目を指定する（メール本文の一部）
・項目はセルを指定して転記する
・メールの受信順に転記していく
・未読のメールのみ転記する
・転記したメールは既読にする
・メッセージIDを取得して重複を防ぐ

受信するメールがスレッド内に収まってしまう場合、メールを受信するたびに未読になってしまう。

そんなわけで、メッセージIDというのを取得して重複を防ぐようにしています。

メッセージIDとは？

STEP3　スクリプト実行の設定をする

時計マークをクリック

プロジェクト名を決めます。

特定の条件のときに自動で実行するトリガーを設定

「今すぐ通知を受け取る」に設定すると、スプレッドシートを開いた時に実行されます。

これで完了です。

メールを受信した時（未読の状態）、スプレッドシートを開くと（多少時間がかかります）転記されていくようになります。

最後に

Gmailの内容をスプレッドシートに転記するだけなら、ちょっと調べたらコピペで割と何とかなるんですが、今回はセルを指定して転記させたかった。

あれ？・・・動かん・・・の繰り返しでしたが、それでもさぐりさぐりで何とかなるもんです（笑）

「ここ、必要ないんじゃ」「書き方が間違ってるじゃん」というご意見があるかもしれません。
それはそうで、そもそもエンジニアではないので、その辺はさっぱりわからない。

でも・・・動くので良しとしました。
いや～ホント、動いてよかった！

ところがです。

しばらくはちゃんと動いていたのですが、ある日突然動かなくなった。
なぜでしょうか？

単にメールの項目が変更されたのです。それだけの理由。

当たり前ですが、メールの項目が変更された場合はエラーになり動かない。ただ、素人にはなかなか「なぜなのか？」がわかりません。

「あーでもない」「こーでもない」みたいにやっていると、何かのきっかけで「なぜなのか？」がわかるようになるかもしれない。

考えてみれば当然ですね。
試みと失敗を繰り返しながら試行錯誤しているのだから。

意外に何とかなるもんですね（笑）

参考にしたWEBサイト

いつも隣にITのお仕事
https://tonari-it.com/gas-gmail-fetch-data-append/

株式会社InitialSite
http://www.initialsite.com/w01/13064