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

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

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


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/77cad326-18a1-79b0-8721-8e0b1436919d.png)

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


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

```javascript: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の設定

https://script.google.com/

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/48041080-f219-d9c4-4ebf-13c43123b01a.png)

- Googleアカウントにログインしておきます
- [GAS（Google Apps Script）](https://script.google.com/)を開きます
- `新しいプロジェクト` を立ち上げます
- `コード.gs` に上記のスクリプト例を貼り付けて保存します

### スクリプトの編集

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

https://for-dummies.net/gas-noobs/gas-japanese-reference-for-gmail/

https://developers.google.com/apps-script/reference/gmail?hl=ja


## 1-2）共有範囲の設定

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/a7f2156d-be67-176b-5dc3-14ba3df1b5e9.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/e7953f98-2fd0-a93c-3700-c94b737245d8.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/529549be-cfa1-2c4b-761e-21ec32a607ad.png)

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


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

https://docs.google.com/spreadsheets/

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/a323a7c4-5928-331f-2ca6-b0d42d386997.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/4788daad-8b60-1434-1e54-884bf644de7a.png)

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


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/862cd926-d3d8-2696-8b9a-019068334a73.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/c90debce-eef9-eada-080f-bd2f32d27bbf.png)

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


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/283603/ed62d64a-9b1c-a42c-412b-2615abb57947.png)

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

https://developers.google.com/apps-script/guides/services/quotas?hl=ja#current_limitations

## 補足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()` に置き換えて解釈してください


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