本記事の内容

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/

以上の記事を参考にさせていただきました。ありがとうございました！