メール集計｜Gmail文面をスプレッドシートへ書き出す方法【GAS】

問い合わせメールを集計したい。Gmailの文面をスプレッドシートに書き出す方法

• 「Gmailのメール中の文面の一部を、スプレッドシートへ書き出したい」という要望に応える方法です
• 例えば、ホームページの問い合わせフォームからのメールをGmailで受けて、それをスプレッドシートへ転記して集計したい場合とか
• スプレッドシートへ書き出した後に、エクセルでダウンロードするとか、CSVにするとかは、お好きにどうぞ
• 本文は、スクリプトの内容よりは「どうやったらいいか」「導入手順」の方に軸足を置いて記載します（スクリプトの解説については、スクリプト内のコメントアウトをご覧いただくか、各関数をググってください）
• 本文は、作成したスクリプトを他者と共有して使うこと（例えば社内で共有するとか）を想定して記載します（個人使用の場合は最下部の「もっと手っ取り早く実行する方法」も、ご確認ください）

例として示すスクリプトの仕様（機能）

• Gmailにおいて、特定のメールの内容をスプレッドシートへ転記するスクリプトです
• スクリプトを実行すると、メールの検索条件として、受信日の範囲（いつから〜いつまで）と、メールタイトル（件名）を問うダイアログが表示されます
• スプレッドシートへ転記されたメールには、Gmail側においてラベル「シート転記済」が付与されるようにしています（スプレッドシートへの重複転記を防止するため、メールの検索条件の一つに『ラベル「シート転記済」を除く』があります）
• メールの文面には「氏名：」「メールアドレス：」「年齢：」「性別：」「メッセージ：」という文字列が入っていると仮定しています

スプレッドシート書き出すスクリプト例

GAS(コード.gs)

function getMailPostSheet() {
  // 誤入力があったら、途中で止められるように「try-catch」
  try {
    /*
     * シート系の処理
     */

    // 開いているスプレットシートを取得
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    // スプレットシート中のシート一覧を取得
    var sheetNames = [];
    spreadsheet.getSheets().forEach(function (e) {
      sheetNames.push(e.getSheetName());
    });

    // シート「mail_data」が無かったらシートを生成
    if (!sheetNames.includes("mail_data")) {
      spreadsheet.insertSheet().setName("mail_data");
    }

    // 書き込むシートを取得
    var sheet = spreadsheet.getSheetByName("mail_data");

    // セル「A1」が空だったら、1行目にタイトルを挿入
    if (sheet.getRange(1, 1).getDisplayValue() == "") {
      var titleList = ["転記日時", "受信日時", "メールタイトル", "メール送信者", "メールID", "氏名", "メールアドレス", "年齢", "性別", "メッセージ"];
      titleList.forEach(function (title, index) {
        sheet.getRange(1, index + 1).setValue(title);
      });
    }

    /*
     * 日付系の処理
     */

    // 本日の日付を取得
    var now = new Date();
    var today = Utilities.formatDate(now, "JST", "yyyy/MM/dd HH:mm:ss");

    // 翌日の日付をセット（本日に届いたメールも検索時期の範囲に入れるため）
    var tomorrow = new Date();
    tomorrow.setDate(tomorrow.getDate() + 1);
    tomorrow = Utilities.formatDate(tomorrow, "JST", "yyyy/MM/dd");

    // 月初の日付を代入（ddの部分を「01」にしただけ）
    var firstDay = Utilities.formatDate(now, "JST", "yyyy/MM/01");

    // 検索の時期
    var searchDate = [];
    searchDate[0] = firstDay; // 開始日（after:〜）　初期値：月初
    searchDate[1] = tomorrow; // 終了日（before:〜）　初期値：翌日（実質は本日）

    // ダイアログ表示（いつから〜いつまでの入力用）
    var ui = SpreadsheetApp.getUi();

    // 「キャンセル」「クローズ」ボタンをクリックした場合にエラー発生
    function clickCancelButton(button) {
      if (button == ui.Button.CANCEL || button == ui.Button.CLOSE) {
        throw new SyntaxError("処理がキャンセルされました");
      }
    }

    // 開始日の入力表示
    var dialogAfter = { title: "「いつから」の入力", message: `未入力の場合は「${firstDay}（月初）から」で取得します\n\n※ yyyy/mm/dd の形式で入力してください\n` };
    // 終了日の入力表示
    var dialogBefore = { title: "「いつまで」の入力", message: `未入力の場合は「${tomorrow}（翌日）まで」で取得します\n\n※ yyyy/mm/dd の形式で入力してください\n` };

    // キャンセルした場合、誤入力あった場合
    [dialogAfter, dialogBefore].forEach(function (dialog, index) {
      var prompt = ui.prompt(dialog.title, dialog.message, ui.ButtonSet.OK_CANCEL);
      clickCancelButton(prompt.getSelectedButton()); // 「キャンセル」「クローズ」ボタンをクリックした場合
      var response = prompt.getResponseText();
      if (response != "") {
        // ダイアログで入力された文字列が「yyyy/mm/dd」形式かチェック
        if (!response.match(/\d{4}\/\d{1,2}\/\d{1,2}/)) {
          throw new SyntaxError(`${dialog.title}値：${response}\n\n※日付は yyyy/mm/dd の形式で入力してください\n\n処理がキャンセルされました`);
        }
        // ダイアログに入力があったら開始日／終了日を上書き
        searchDate[index] = response;
      }
    });

    /*
     * メール系の処理
     */

    // タイトルを検索するダイアログ表示
    var mailSearchSubjectPrompt = ui.prompt("検索するメールタイトルの入力", "未入力の場合は、件名に「ホームページからの問い合わせ」を含むメールを取得します", ui.ButtonSet.OK_CANCEL);
    clickCancelButton(mailSearchSubjectPrompt.getSelectedButton()); // 「キャンセル」「クローズ」ボタンをクリックした場合
    var mailSearchSubjectResponse = mailSearchSubjectPrompt.getResponseText();

    var mailSearchSubject = mailSearchSubjectResponse ? mailSearchSubjectResponse : "ホームページからの問い合わせ"; // 未入力の場合は「ホームページからの問い合わせ」をセット

    // メール検索条件に該当するスレッド一覧を取得
    var threads = GmailApp.search(`subject:(${mailSearchSubject}) after:${searchDate[0]} before:${searchDate[1]} -label:シート転記済`);

    // スレッドを一つずつ取り出す
    threads.forEach(function (thread) {
      // スレッド内のメール一覧を取得
      var messages = thread.getMessages();

      // メールを一つずつ取り出す
      messages.forEach(function (message) {
        var mailGetDate = message.getDate(); // 受信日時を取得
        var mailSubject = message.getSubject(); // メールタイトルを取得
        var mailFrom = message.getFrom(); // メール送信者を取得
        var mailID = message.getId(); // メールIDを取得
        var plainBody = message.getPlainBody(); // メール本文を取得
        var plainBodyName = plainBody.match(/氏名：(.*)/); // メール本文中から「氏名」部を取り出す
        var plainBodyMail = plainBody.match(/メールアドレス：(.*)/); // メール本文中から「メールアドレス」部を取り出す
        var plainBodyAge = plainBody.match(/年齢：(.*)/); // メール本文中から「年齢」部を取り出す
        var plainBodySex = plainBody.match(/性別：(.*)/); // メール本文中から「性別」部を取り出す
        var plainBodyMessage = plainBody.match(/メッセージ：([\s\S]*)/); // メール本文中から「メッセージ」部を取り出す（改行込みで取得）

        // メール本文が取得できているかGAS上のログに出力して確認（これは無くても良し）
        Logger.log(`${mailGetDate} - ${mailID} - ${mailSubject}`);

        // シートの最終行の次の行をセット（つまり書き込む行）
        var writeRow = sheet.getLastRow() + 1;

        // セルを取得して値を転記
        sheet.getRange(writeRow, 1).setValue(today); // 転記日時
        sheet.getRange(writeRow, 2).setValue(mailGetDate); // 受信日時
        sheet.getRange(writeRow, 3).setValue(mailSubject); // メールタイトル
        sheet.getRange(writeRow, 4).setValue(mailFrom); // メール送信者
        sheet.getRange(writeRow, 5).setValue(mailID); // メールID

        // 以下の項目はメール本文に必ずあるとは限らないので「if」で囲む（途中で本文の項目に変更があったとか...）
        if (plainBodyName) { sheet.getRange(writeRow, 6).setValue(plainBodyName[1]); } // 氏名
        if (plainBodyMail) { sheet.getRange(writeRow, 7).setValue(plainBodyMail[1]); } // メールアドレス
        if (plainBodyAge) { sheet.getRange(writeRow, 8).setValue(plainBodyAge[1]); } // 年齢
        if (plainBodySex) { sheet.getRange(writeRow, 9).setValue(plainBodySex[1]); } // 性別
        if (plainBodyMessage) { sheet.getRange(writeRow, 10).setValue(plainBodyMessage[1].replace(/(\r\n|\n|\r)/gm, '\t')) } // メッセージ　ただし、スプレッドシート上では１行で表示させたいので、改行をタブに置換
      });

      // スレッドに「シート転記済」ラベルを付ける
      var label = GmailApp.getUserLabelByName("シート転記済");
      if (!label) {
        label = GmailApp.createLabel("シート転記済"); // Gmail上にラベル「シート転記済」が無かったらラベル新規作成
      }
      thread.addLabel(label);
    });

    ui.alert("処理が完了しました");
  } catch (err) {
    // エラー表示（キャンセルした場合、誤入力あった場合）
    SpreadsheetApp.getUi().alert(err.message);
  }
}

準備１） GASの設定

1-1）スクリプトの貼り付け

• Googleアカウントにログインしておきます
• GAS（Google Apps Script）を開きます
• 新しいプロジェクト を立ち上げます
• コード.gs に上記のスクリプト例を貼り付けて保存します

スクリプトの編集

• 上記のスクリプト例は、メールの文中に「氏名：」「メールアドレス：」「年齢：」「性別：」「メッセージ：」が入っている前提で組んでいます。必要に応じて var titleList = [〜省略〜] の部分と var plainBodyName = plainBody.match(/氏名：(.*)/); の部分（あと類似箇所）を編集してください
• スクリプトを編集する場合は、次のページが参考になるかもしれません（Gmailで使えるGASの関数）

1-2）共有範囲の設定

• GAS編集画面の右上にある共有アイコンをクリック
• このスクリプトを共有して使うユーザーを追加してください
• 付与する権限は 閲覧者 でも 編集者 でも、どっちでもいいです

1-3）ライブラリとしてデプロイ

• GAS編集画面の右上にある デプロイ > 新しいデプロイ をクリック
• 種類の選択 の右側の歯車アイコンをクリックして ライブラリ をクリック
• 新しい説明文 を任意で入力し、デプロイ をクリック
• 「デプロイを更新しました」が表示されたら 完了 をクリックしてください

1-4）プロジェクトIDのコピー

• GAS編集画面の左側にある プロジェクトの設定 （歯車アイコン）をクリック
• スクリプト ID の文字列をコピーして、どこかにメモ（保存）しておいてください（工程（2-2）で使います）

準備２）スプレッドシートの設定

2-1）スプレッドシートから「Apps Script」を起動

• 新しいスプレッドシートを立ち上げてください
• メニュー 拡張機能 > Apps Script をクリック（Apps Script の編集画面が開きます）

2-2）ライブラリの追加

• 左サイドにある ライブラリ ＋ をクリック
• スクリプトID を入力する枠に、上記（1-4）でメモしておいた文字列を入力し、 検索 してください
• ID という枠には任意の英数字を入れてください（例「importScript」）
• そして 追加 をクリック（上記の例の通りであれば、ライブラリの枠に「importScript」が追加されているはずです）
• 「ライブラリを検索できませんでした」と表示される場合は、次の項目を確認してください
• • 上記（1-3）でライブラリをデプロイしているか
• • 検索するIDは スクリプトID であり、 デプロイ ID ではありません。誤って入力していませんか？

2-3）コード.gsの編集

• 既存の関数　myFunction() の中に importScript.getMailPostSheet(); を追加（上記の例の通りの場合）
• プロジェクトを保存する（フロッピーアイコンをクリック、もしくは Command/Ctrl + S ）

2-4）マクロのインポートと使用許可

• スプレッドシートに戻り、メニュー 拡張機能 > マクロ > マクロをインポート をクリック（表示されない場合は、一度スプレッドシートをリフレッシュ）
• 関数を追加 をクリックし、右上の「Ｘ」をクリック
• すると、メニュー 拡張機能 > マクロ > myFunction が追加されているので、クリック
• 「承認が必要」が表示されたら 続行 をクリック
• 自身のGoogleアカウントを選択し、 許可 をクリック

スクリプトの実行（メールからスプレッドシートへ転記）

• スプレッドシートのメニュー 拡張機能 > マクロ > myFunction をクリックすると、スクリプトが実行されます
• 上記のスクリプトを実行すると、メールの検索条件を問うダイアログが表示されます
• 「いつから」が未入力のまま実行した場合は「実行時の月初から」となります
• 「いつまで」が未入力のまま実行した場合は「本日まで」となります
• 「メールタイトル」が未入力のまま実行した場合は、件名に「ホームページからの問い合わせ」を含むメールを取得します
• GASにはサービス利用の制限があります。特に スクリプト ランタイム - 6 分 / 実行 があります（要は、処理が6分以上続くとタイムアウトするよ、ということ）
• 何かしらの条件で転記するメールを制限しておかないと、場合によってはタイムアウトしてしまうので、上記のスクリプトではメール受信日でフィルタリングするようにしています
• もし、途中で処理が止まったり、エラーが発生した場合は、サービスの利用制限の部分も確認しましょう

補足1）Gmailの確認

• 上手くスプレッドシートへ転記ができた場合は、Gmail側も確認してください
• 上記スクリプトの通りであれば、転記されたメールにラベル「シート転記済」が付与されていると思います
• このラベルが付いているメールは、メール検索条件で対象外にするようにしています（ var threads = GmailApp.search(" 〜省略〜 -label:シート転記済") のところ）
• スプレッドシートへ重複して書き出さないようにする為です
• もし「最初から書き出したい（すでに転記済みメールも含めて書き出したい）」となった場合は、スクリプトからラベルを付与する処理を外すか、このラベルを転記済みメールから外してください

補足2）デプロイ後のスクリプト変更

• 工程（1-3）でスクリプトをデプロイした後に、スクリプトを変更した場合は、再度にデプロイをした方がいいかもしれません。
• デプロイ > デプロイを管理 をクリックし、右上の鉛筆アイコン（編集）をクリック
• バージョンを 新バージョン を選択してデプロイを実行
• 工程（2-2）でライブラリを追加した方についても、バージョンを上げてください
• 追加したライブラリ（例であれば「importScript」）をクリックすると、バージョンを上げるUIが表示されます

もっと手っ取り早く実行する方法

• 「自分しか使わないよ」「他の人は実行しないよ」という場合は、上記のいくつかの工程を省くことができます
• 「準備１） GASの設定」と「2-2）ライブラリの追加」は不要です
• ようは、スプレッドシートから直接GASを開き、マクロを実行する方法です（ただし、この方法だと作成したスクリプト（マクロ）を他者と共有することはできません）
• 工程（2-3）において、工程（1-1）のスクリプトを貼り付けてください（ myFunction() の部分を getMailPostSheet() に置き換える）
• 工程（2-3）以降の工程において、 myFunction() の部分を getMailPostSheet() に置き換えて解釈してください

誰かの役に立ったら嬉しいです