本記事の内容
Gmailに届くGoogleFormからの問い合わせ内容をスプレットシートに自動転記させる方法について解説しています。
方法としてはGAS(GoogleAppsScript)を用います。
自分は始めてGASを触り、GoogleWorkspaceの便利さに痛感しました。。
要件
上記画像のように、
合計金額:
会社数:
毎月の返済額:
お住まい:
メールアドレス:
電話番号:
名前:
の7つの項目をスプレットシートに自動転記するものとなっています。
実行手順
1.自作スプレットシートを作成する
下記画像のように項目表を作成します。
スレッドIDは既にスプレットシートに記載されているGmail内容を転記しないために必要です。
2.スプレットシート上のツールバー→拡張機能→Apps Scriptを押す
3.「承認が必要です」の文言が出るので、参考記事を基に承認する
参考記事：https://tonari-it.com/gas-script-approval/
4.承認が完了したら、下記のスクリプトを貼り付けする
function myFunction(){
// 件名に該当するGmailのみ検索対象とします。
let query = 'subject:お問い合わせがありました。';
let gmail = getGmail(query);
let sheet = SpreadsheetApp.getActive().getActiveSheet();
let range = sheet.getDataRange();
let values = range.getValues();
// "'"に関しては転記する際にスレッドIDの先頭に'を付け足して転記しているためです。
let threadIDs = values.map(row => "'" + row[0]);
gmail.forEach(info => {
if (threadIDs.indexOf(info[0]) === -1) { // スレッドIDがない場合のみ行を付け足します。
sheet.appendRow(info);
}
});
}
function getGmail(query) {
let threads = GmailApp.search(query);
let gmailInfo = new Array();
threads.forEach(function(thread) {
let threadID = thread.getId();
/* 余程ないことですが、スレッドIDが全て数字となる場合があります。
数字の場合はスプレットシートに転記された際「6.541321E＋15」と表記され、
既に転記されたGmailも転記されます。
そのため、先頭に'を付け足して文字列に加工します。 */
threadID = "'" + threadID;
let messages = thread.getMessages();
/* スレッド内の最新のメッセージのみを取得します。
問い合わせ内容に返信した場合、スレッド内のメールが重なり、
関係のないメールまで転記される恐れがあります。
そのため、最新のメッセージのみを転記としています。 */
let message = messages[0];
// メッセージ受信日取得
var date = message.getDate();
// メール本文
let plainBody = message.getPlainBody();
// 合計金額
let total_amount = plainBody.match(/合計金額:\s*(.*)/);
// 会社数
let number_of_companies = plainBody.match(/会社数:\s*(.*)/);
// 毎月の返済額
let monthly_repayment_amount = plainBody.match(/毎月の返済額:\s*(.*)/);
// お住まい
let address = plainBody.match(/お住まい:\s*(.*)/);
// メールアドレス
let mail = plainBody.match(/メールアドレス:\s*(.*)/);
// 電話番号
let tel = plainBody.match(/電話番号:\s*(.*)/);
// 名前
let name = plainBody.match(/名前:\s*(.*)/);
// 電話番号もスレッドIDと同じように、先頭の0が省かれて記載されてしまうので文字列とします。
tel = "'" + tel[1];
gmailInfo.push([threadID, date, total_amount[1], number_of_companies[1], monthly_repayment_amount[1], address[1], mail[1], tel, name[1]]);
});
return gmailInfo;
}
5.次に実行ボタンを押さずに、自動で転記できるようにトリガーを設定する
下記画像のように設定する。
一番良いのは、「分ベースタイマー→1分おき」ですが、
GASの制約として、「トリガーの合計実行時間 90分/日」となっています。(※一般ユーザーの場合)
https://developers.google.com/apps-script/guides/services/quotas?hl=ja#current_limitations
そのため、「分ベースタイマー→1分おき」とすると、以上の制約を反ししてしまう恐れがあります。
※自分は、「分ベースタイマー→1分おき」とし、下記画像のエラーログが吐かれていました。
Exception: Service invoked too many times for one day: gmail.
とあり、一日のトリガー合計実行時間が過ぎていました。
※1実行8秒 × 60回/1分あたり × 24時間分 = 192分/日
となり、やはり一日の合計実行時間をオーバーしていました(汗)
これで実行ボタンを押せば、スプレットシートに自動転記されると思います。
GASは最新のGmailから探索を開始するため、受信日が最近のものが一番上となってしまいますが、
スプレットシート上で受信日を昇順にすれば、綺麗なスプレットシートになると思います。
所感
始めて業務効率系のエンジニアリング(?)をしましたが、
スプレットシートにGmailの内容が反映されていくのを見るのはとても面白かったです。
参考記事一覧
https://tetsuooo.net/gas/512/
https://tech.torico-corp.com/blog/gmail-export-to-google-spreadsheet-via-google-apps-script-gas/
https://qiita.com/tomokei5634/items/dec0301d3af34a22b00e
https://tonari-it.com/gas-script-approval/
以上の記事を参考にさせていただきました。ありがとうございました！