Gmailに届いた問い合わせメールをスプレッドシートに自動書き込みする方法

Gmailに届いた問い合わせメールをスプレッドシートに自動書き込みする方法

弊社では、検索集客からのコンバージョンの精度を高めるため、サイトに寄せられる問い合わせ内容を共有していただくことがあります。

問い合わせはContactForm7などのフォーム系プラグインから転送していただくのですが、膨大な問い合わせ内容を管理するために、頂いたメールは一括でスプレッドシートで管理すると便利です。

この記事では、Gmailに届いた問い合わせ内容を、AppScriptを使ってスプレッドシートに自動的に転記する仕組みの構築方法についてまとめています。
 

ログインとコードのコピペ

まず、問い合わせメールが届くGmailのGoogleアカウントにログインした状態で、書き込みしたいスプレッドシートを開きます。

「拡張機能」→「App Script」を開きます。

左サイドバーより「エディタ」を開き、以下のコードをコピペしてください。

js
 function ExportMailToSheet() {    // 抽出条件指定    let query = 'subject:抽出したいメールのタイトル';    // 新しいメールデータを取得    let newGmailData = getGmail(query);    // スプレッドシートを取得    let spreadsheet = SpreadsheetApp.getActive();    // シート名が「問い合わせ内容」のシートを取得、存在しない場合は新規作成    let sheet = spreadsheet.getSheetByName('問い合わせ内容');    if (!sheet) {        sheet = spreadsheet.insertSheet('問い合わせ内容');        // ヘッダー行を追加(初回のみ)        sheet.appendRow(['受信日時','会社名', '部署名', '氏名', '住所', 'メールアドレス', '電話番号', 'FAX番号', '希望対応時期', '対応について', 'お問合わせ内容']);    }    // スプレッドシートの既存データを取得    let existingData = sheet.getDataRange().getValues();    // 既存のデータにない新しいメールを識別    let newDataToAdd = newGmailData.filter(function(newEmail) {        let newEmailDate = newEmail[0].toString();        return !existingData.some(function(existingRow) {            return existingRow[0].toString() === newEmailDate;        });    });    // 新しいデータをシートの上部に挿入    newDataToAdd.reverse().forEach(function(row) {        sheet.insertRowBefore(2); // ヘッダー行の次に行を挿入        sheet.getRange(2, 1, 1, row.length).setValues([row]);    });}function getGmail(query) {  //Gmail取得ここから  //Gmailの履歴からマッチするメールを一覧化  let threads = GmailApp.search(query);  let gmailInfo = new Array();      //一覧化したメールの件数分繰り返し   threads.forEach(function(thread) {    // スレッド内のメール一覧を取得    let messages = thread.getMessages();        // メールを一つずつ取り出す    messages.forEach(function(message) {      let plainBody = message.getPlainBody();      // メール本文      let company = plainBody.match(/会社名:(.*)/);      // 会社名      let busho = plainBody.match(/部署名:(.*)/);      // 部署名      let name = plainBody.match(/お名前:(.*)/);      // 氏名      let address = plainBody.match(/◆住[\s ]*所:\r\n([\s\S]*?)\r\n\r\n◆メールアドレス:/); // 住所      let mail = plainBody.match(/メールアドレス:(.*)/);      // メールアドレス      let tel = plainBody.match(/電話番号:(.*)/);      // 電話番号      let fax = plainBody.match(/FAX 番号:(.*)/);      // FAX 番号      let time = plainBody.match(/ご希望の対応時期:\r\n(.*)/);      // ご希望の対応時期:      let taiou = plainBody.match(/対応について:\r\n(.*)/);      // 対応について:      let naiyou = plainBody.match(/お問合わせ内容:([\s\S]*)/);      // お問合わせ内容:      let receivedDate = message.getDate();      // メール受信日時を取得      // お問合わせ内容の先頭にある改行・スペースを削除      let naiyouText = naiyou[1].replace(/^\s+/, '');      // 各メール情報を組みとした二次元配列として格納      gmailInfo.push([receivedDate, company[1], busho[1], name[1], address[1], mail[1], tel[1], fax[1], time[1], taiou[1], naiyouText]);      // //各メール情報を組みとした二次元配列として格納 (naiyouの1行目に改行が入る)      // gmailInfo.push([receivedDate,company[1],busho[1],name[1],address[1],mail[1],tel[1],fax[1],time[1],taiou[1],naiyou[1]]);    });      });   return(gmailInfo); }

問い合わせメールの送信内容によってコードを調整

問い合わせメールの内容によって、以下の部分を書き換えます。

こちらのサンプルコードでは、例えば以下のようなメールが送信された場合

スプレッドシートに、こちらのように転記されます。

抽出したいメールのタイトル

let query = 'subject:抽出したいメールのタイトル';

こちらは、抽出したいメールのタイトルになります。
例えば、問い合わせのメールのタイトルが"【株式会社bluegoat】お問合わせ"だった場合、

js
let query = 'subject:【株式会社bluegoat】お問合わせ';

と修正することで、受信された全てのメールの中から、タイトルに
"【株式会社bluegoat】お問合わせ"
を含むメールだけをピックアップします。

シートに転記したいメールの内容

問い合わせ内容によって取得したい情報は異なるため、メールの内容によって以下の行を適宜調整してください。

js
sheet.appendRow(['受信日時','会社名', '部署名', '氏名', '住所', 'メールアドレス', '電話番号', 'FAX番号', '希望対応時期', '対応について', 'お問合わせ内容']);

こちらのコードは、スプレッドシートのヘッダーセルの内容を示しています。

js
let company = plainBody.match(/会社名:(.*)/); // 会社名let busho = plainBody.match(/部署名:(.*)/); // 部署名let name = plainBody.match(/お名前:(.*)/); // 氏名let address = plainBody.match(/◆住[\s ]*所:\r\n([\s\S]*?)\r\n\r\n◆メールアドレス:/); // 住所let mail = plainBody.match(/メールアドレス:(.*)/); // メールアドレスlet tel = plainBody.match(/電話番号:(.*)/); // 電話番号let fax = plainBody.match(/FAX 番号:(.*)/); // FAX 番号let time = plainBody.match(/ご希望の対応時期:\r\n(.*)/); // ご希望の対応時期:let taiou = plainBody.match(/対応について:\r\n(.*)/); // 対応について:let naiyou = plainBody.match(/お問合わせ内容:([\s\S]*)/); // お問合わせ内容:let receivedDate = message.getDate(); // メール受信日時を取得

こちらのコードでは、メールの中から正規表現にマッチする行を取得しています。

例えば
plainBody.match(/会社名:(.*)/)
という記載は

会社名:株式会社bluegoat
の行がヒットし、変数「company」に「株式会社bluegoat」が格納されます。
※正規表現で.*は全ての文字を示し、()で囲むことで、変数として取得することを意味します。

実行して動作を確認

保存して実行します。
スプレッドシートに狙った値が入ってくれば成功です。

トリガーの設定

このままの状態だと、毎日手動で実行しないとシートに値が入ってきません。
そのため、自動実行するためにトリガーを設定します。

サイドバーから「トリガー」を選択し、「トリガーを追加」をクリックします。

実行する関数に「ExportMailToSheet」を選択し、他の項目も画像の通り選択します。

※こちらの設定は、「4時間に1回起動する」という設定です。
起動トリガーのタイミングは、1日に何通もメールが来て頻繁にチェックする場合は、高い頻度で実施した方が良いです。

しかし、1日に1通メールが来るか来ないかの場合は、
時間ベースのトリガーのタイプを選択
→日付ペースのタイマー

時刻を選択
→午前7〜8時
などに設定しておき、高頻度でチェックさせないようにしましょう。

トリガーを設定後は、しばらく時間を置いて、トリガーが正常に稼働しているかどうか確認してください。

あなたへのおすすめ