こんにちは、おかちゃんせんせいです!
先日業務効率化関連の依頼で、
Gmailに届いた特定のメールをGoogleスプレッドシートに転記してほしい
という依頼がありました。
話を詳しく伺うと、
メールに届いた内容のうち必要な項目を選択してコピー。
そのコピーした内容を全部で10〜20項目くらいを
営業支援システムに登録したり、
スプレッドシートに転記したりしていたようで。
誰にでもできる作業ではあり、
1つの作業を終えるのに慣れてこれば3分くらいで終わりますが、
件数があると地味に時間が取れてしまいます。
それに、それが毎日となると・・・。
こういった単純で繰り返し行う作業は自動化しやすいので、
今回であればGASやiPaaS、RPAなどを使えば自動化は可能になります。
今回はGASでの方法について、
備忘も兼ねてまとめることにします。
目次
実装する上でのポイントは3つ
- 特定のGmailを絞り込んで取得すること
- ①で取得した本文の中から必要な情報を抽出すること
- ②の情報をGoogleスプレッドシートに転記すること
③についてはこれまで他のことで何度も実装してきたことはありますが、①②は今回が初めてでしたので、ネットで調べて今回の仕様に合うように調整しました。
- 定期的にメールの情報を取得する際に、重複のないようにデータを取得する
- 特定の件名に合致するメールで絞り込んで取得する
重要なポイントは3つですが、
ステップとしては4つに分けて整理すると分かりやすいです。
まず、ステップ1で転記する先のシートと、前回実行日を記録する設定シートを設定します。
このステップで特にエラーが出る要素はありません。
次のステップ2からはエラーが出る可能性があるため、try~catchでエラーを回収できるようにしておきます。
ステップ2では取得したいGmailの検索条件を設定、取得します。
このステップの中での大事なポイントは3つ。
まず1つ目は秒単位の日時で絞り込みができるようにすること。
GmailApp.searchで絞り込むことのできる要素はいくつかありますが、受信日時については通常の日付形式では日にちまでしか設定できず、時刻で検索できるようにする必要があります。
ネットで調べたところ、
日付型の変数をUNIX TIMEに変換して、
ミリ秒を秒に変換する方法
で解決することがわかりました。
let unixTime = date.getTime(); // UNIX TIMEに変換
let term = Math.floor(unixTime/1000); // ミリ秒を秒に変換
2つ目は、複数条件で絞り込みができるように配列に格納できるようにしておくこと。
// 検索条件指定(検索条件を配列で格納)
const srchCondition = [
‘subject:メールフォームから問い合わせがありました’
].join(“\u0020”); //半角スペースで連結
// 検索条件:termの期間に、srchConditionの条件に合致するメール
const strTerms = ‘after:’+ setDateArr[0] + ‘ ‘ +
‘before:’ + setDateArr[1] + ‘ ‘ +
srchCondition;
3つ目は、上記で設定した条件でスレッドを取得するGmailApp.searchを活用すること
const threads = GmailApp.search(strTerms, 0, 30); // 条件にマッチしたスレッドを取得
searchの第1引数は検索条件。
第2引数はスレッドを取得する開始のインデックス。
第3引数は取得するスレッド数になり、
第2、3は省略が可能です。
ステップ3は、
ステップ2で取得したスレッドの情報からメールを1つずつ抽出して、必要な情報を取得・配列に格納する段階です。
スレッドを一つずつ取り出して、その中にあるメール一覧をgetMessagesで取得。 さらに、その中にあるメールを一つずつ取り出して、メール情報やメール本文(getPlainBody)から必要な情報を抽出。
メール本文の中から必要な情報を抽出する際には、matchメソッドを使い正規表現で欲しい情報を取得します。
このときのポイントはmatchで取得したときには、要素数が2つあるということ。
[0]:検索でヒットした文字列
[1]:正規表現で絞り込んだ文字列
そして、最後にスプレッドシートに一括反映できるように、配列に格納できるようにしておきます。
最後のステップ4では、
ステップ3で必要な情報を格納した配列を、スプレッドシートに反映させます。
getRangeの引数を下記のように設定しておくことで、要素数や項目数が増減した際にもメンテナンスが不要になるのもポイントです。
getRange(lastRow + 1,1,threadsArr.length,threadsArr[0].length)
サンプルコード
/**
* メール検索して該当する情報をスプシに転記するメソッド
*/
function searchMail(){
/* ステップ1: 実行するスプレッドシートの情報を取得する */
// 処理するシートをセットする
let ss = SpreadsheetApp.getActiveSpreadsheet();
let shS = ss.getSheetByName("Set");
let shP = ss.getSheetByName("Gmail");
let beforeDate = shS.getRange("C2").getValue();
try{
/* ステップ2: 特定のメールを取得するための条件を指定して、情報を取得する */
// Gmailから特定条件のスレッドを検索しメールを取り出す
// 受信日時指定
let nowDate = new Date();//現在時刻を取得
// メール情報を取得する時間を取得する配列
// ★beforeDate:前回実施時間、nowDate:現在時刻
let setDateArr = [beforeDate,nowDate];
let cnt = 0;
// 秒単位の情報も取得して検索できるようにする
for(let date of setDateArr){
let unixTime = date.getTime(); // UNIX TIMEに変換
let term = Math.floor(unixTime/1000); // ミリ秒を秒に変換
setDateArr[cnt] = term;
cnt += 1;
}
// 検索条件指定(検索条件を配列で格納)
const srchCondition = [
'subject:メールフォームから問い合わせがありました'
].join("\u0020"); //半角スペースで連結
// 検索条件:termの期間に、srchConditionの条件に合致するメール
const strTerms = 'after:'+ setDateArr[0] + ' ' +
'before:' + setDateArr[1] + ' ' +
srchCondition;
const threads = GmailApp.search(strTerms, 0, 30); // 条件にマッチしたスレッドを取得
/* ステップ3: 合致する情報を配列に格納する */
// スレッド内のメール一覧を取得
// スレッドを一つずつ取り出す
let threadsArr = []; // スプシ転記用配列
threads.forEach(thread => {
// スレッド内のメール一覧を取得
let messages = thread.getMessages();
// メールを一つずつ取り出す
messages.forEach(function(message) {
let shDataArr = []; // 1つのメールから必要な情報を格納する配列
let plainBody = message.getPlainBody(); // メール本文
let sentDate = message.getDate(); // 受信日
let name = plainBody.match(/名前:(.*)/);
let email = plainBody.match(/メールアドレス:(.*)/);
let company = plainBody.match(/会社名:(.*)/);
let detail = plainBody.match(/問い合わせ内容:(.*)/);
shDataArr = [
sentDate,
name[1],
email[1],
company[1],
detail[1]
];
threadsArr.push(shDataArr);
});
});
/* ステップ4: スプシにメールの情報を転記する */
let arrCnt = threadsArr.length;
let lastRow = shP.getLastRow();
// スプシ転記用配列にデータがある場合には、スプシに転記する
if(arrCnt > 0){
shP.getRange(lastRow + 1,1,threadsArr.length,threadsArr[0].length).setValues(threadsArr);
}
// 今回実行時間を前回実行時間として[設定]シートのC2セルに更新する
shS.getRange("C2").setValue(nowDate);
}catch(e){
console.error(e.message);
}
}
参考サイト
基本的な骨組み部分については参考サイトを基にして実装しました。
そこにほんの少しの微差を加えるだけで、
要件を満たす内容を作り込めることさえわかれば
大抵のことは独学でやれてしまうのがGASのいいところ。
このやり方を過去の自分が知っていたら、
どれだけ効率的に作業ができたか・・・
今回の情報が誰かのお役に立てれば幸いです!
最後まで読んでいただき、ありがとうございました!