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

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

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

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

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

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

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

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

 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】お問合わせ"だった場合、

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

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

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

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

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

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

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」を選択し、他の項目も画像の通り選択します。

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