【GAS】新たな問い合わせメールをGmailで取得しスプレッドシートに随時追加する


contact

photo credit: Pen Friends via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

GASで問い合わせメールをスプレッドシートに収集する方法をシリーズでお伝えしています。

前回、こちらの記事で、Gmailから特定条件で検索したメールとその返信用アドレスやパーマリンクもスプレッドシートに書き出す方法についてお伝えしました。

【GAS】Gmailのメッセージから返信先とパーマリンクを取得する方法
Gmailで届いた問い合わせフォームからのメールを収集するツールを作成しています。今回は、GASでGmailのメッセージから返信先アドレスとスレッドのパーマリンクを取得する方法をお伝えします。

ただ、過去のメールは良いのですが、これから来る問い合わせメールについてはどうしましょう?

来たメールを順次このスプレッドシートに追加してくれると便利なんですけどね…

ということで、今回はGoogle Apps Scriptで問い合わせメールを都度スプレッドシートに追加していく方法についてお伝えします。

スポンサーリンク

新たな問い合わせメールをスプレッドシートに追加するシステム

新たなメールを追加するシート

問い合わせのメールについて追加するのは、こちらのシートです。

問い合わせメールをシートに出力

過去の問い合わせメールについて、以下の項目を記録していくというものです。

  1. 日付
  2. 送信元(返信先)
  3. 件名
  4. メッセージ(冒頭200文字)
  5. パーマリンク

前回のスクリプト

さて、スクリプトですが前回紹介したこちらのスクリプトを大活用したいと思います。

function searchContactMail() {
 
  const query = '"このメールは 株式会社プランノーツ http://plannauts.co.jp のお問い合わせフォームから送信されました"';
  const start = 0;
  const max = 10;
 
  const threads = GmailApp.search(query, start, max);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);
 
  const values = [];
  for(const messages of messagesForThreads){
    const message = messages[0];
    const record = [
      message.getDate(),
      message.getReplyTo(),
      message.getSubject(),
      message.getPlainBody().slice(0,200),
      message.getThread().getPermalink()
    ];
    values.push(record);
  }
 
  if(values.length > 0){
    SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
  }
 
}

新たなメッセージのみを追加する流れ

システムの動作としては、以下の流れとすれば良さそうです。

  1. イベントトリガーで15分ごとなど動作
  2. 問い合わせフォームからのメッセージを抽出する
  3. 抽出したメッセージのうち、新しいメッセージIDのメッセージのものについて
    1. シートの最終行にそのメッセージの情報を追加する

同じメッセージを重複して記録してしまわないように、メッセージに固有に振られている「メッセージID」を使うという作戦です。

ですから、前述のスクリプトから、変更すべき点は以下のとおりです。

  • メッセージから取得する項目にメッセージIDも追加
  • 抽出したメッセージについて、既にメッセージIDが存在しないかを判定する
  • シートへの出力先を最終行の次の行に

メッセージIDを取得する

まず、メッセージIDを取得するところを先に確認していきましょう。

メッセージIDを取得するには、MessageオブジェクトのgetIdメソッドを使います。

Messageオブジェクト.getId()

前述のスクリプトの13~19行目、二次元配列valuesにプッシュする配列recordの要素として、以下一文を挿入すれば、F列にメッセージIDを取得してくれるようになります。

message.getId()

実行すると以下のようにメッセージIDを取得できるようになります。

シートにメッセージIDを追記

スプレッドシートに新しいメッセージを追加するスクリプト

続いて、メッセージIDが重複していないか判定する処理と、最終行以降に二次元配列を追加するように変更を加えます。

これらを反映したのが、以下のスクリプトです。

function searchContactMail() {
 
  const query = '"このメールは 株式会社プランノーツ http://plannauts.co.jp のお問い合わせフォームから送信されました"';
  const start = 0;
  const max = 10;
 
  const threads = GmailApp.search(query, start, max);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);
 
  const values = [];
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const ids = sheet.getRange(2, 6, lastRow).getValues().flat();
 
  for(const messages of messagesForThreads){
    const message = messages[0];
    const id = message.getId();
   
    if(!ids.includes(id)){
      const record = [
        message.getDate(),
        message.getReplyTo(),
        message.getSubject(),
        message.getPlainBody().slice(0,200),
        message.getThread().getPermalink(),
        message.getId()
      ];
      values.push(record);
    }
  }
   
  if(values.length > 0){
    sheet.getRange(lastRow + 1, 1, values.length, values[0].length).setValues(values);
  }
 
}

以下、ポイントを解説していきますね。

メッセージID列を一次元配列として取得する

スクリプトの12,13行目を注目してください。

定数lastRowは、getLastRowメソッドにより、データのある最終行数が格納されます。

シート6列目、つまりF列の2行目からデータのある最終行までの範囲について、そのデータをgetValuesメソッドで二次元配列として取得し、さらにそれに対してflatメソッドを使用しています。

flatメソッドは、Arrayオブジェクトのメソッドで、対象の配列を与えられた整数の深さに平滑化するものです。

Arrayオブジェクト.flat(深さ)

深さはデフォルトで1になります。

多くの場合、二次元配列を一次元化するときに使用されるメソッドです。

つまり、F列のid列のデータを一次元配列化したものが、定数idsに格納されているということになります。

配列に要素が存在するかを判定する

19行目が、その配列ids内に、現在のメッセージIDが含まれているかどうかを判定するif文です。

配列の中にある値が要素として含まれているかどうかを判定するには、Arrayオブジェクトのincludesメソッドを使います。

Arrayオブジェクト.includes(値)

引数で指定した値が存在するかどうかをブール値で返しますので、そのままif文の条件式として使用できます。

今回はnot演算子が使われていますので、「idが配列idsに含まれてないときにtrue」となり、if文内の処理が実行されるわけです。

シートの最終行の下に追加する

33行目ですが、シートの最終行であるlastRowの次の行の範囲に対して、setValuesをしました。

これにより、過去のデータを残したまま、新たなメッセージのデータをシートに追加してくれるようになります。

いくつかのメッセージの行を削除してから、スクリプトを実行すると、削除したメッセージのみがシートに追加されることを確認できるはずです。

試してみてくださいね。

まとめ

Google Apps Scriptで問い合わせメールを都度スプレッドシートに追加していく方法についてお伝えしました。

  • メッセージIDとそれによる重複の判定
  • シートの最終行の次の行以降にデータを追加する

いずれもよく使う便利なテクニックなのでぜひマスターしてくださいね。

次回は、問い合わせメールが来たらチャットワークに通知して、かつタスク追加する方法についてお伝えしたいと思います。

【GAS】Gmailに来た問い合わせをChatworkに通知してかつタスク追加する
Google Apps Scriptを使って問い合わせメールをスプレッドシートに記録集計するシステムを作っています。今回はさらに問い合わせメールの通知をChatworkに送りつつタスクも立てる方法です。

どうぞお楽しみに!

連載目次:GASでGmailに届いた問い合わせメールを収集する

お仕事の現場では日々様々なメールが届きます。その中で重要なのが「問い合わせメール」ですね。このシリーズでは、Webサイトからの問い合わせメールを自動でスプレッドシートに取り込んだり、チャットワークに送る方法についてお伝えしていきます。

  1. 【GAS】Gmailに送られた過去のメールから特定条件のメールを検索して取得する
  2. 【GAS】Gmailからメールを検索してスプレッドシートに書き出す方法
  3. 【GAS】Gmailのメッセージから返信先とパーマリンクを取得する方法
  4. 【GAS】新たな問い合わせメールをGmailで取得しスプレッドシートに随時追加する
  5. 【GAS】Gmailに来た問い合わせをChatworkに通知してかつタスク追加する
  6. Google Apps ScriptにおけるGmailの読み取り件数の制限とその対処法

コメント

  1. ぜっきー より:

    詳細な解説ありがとうございます。
    参考にさせていただきました。

    ちなみに、ほぼコピペで本機能を実装したのですが、
    メールを既読にする機能だけがうまく動作しませんでした・・・

    タカハシ様の方では、今でも動作していますか?
    GASの仕様変更を疑っているので、ご確認していただけましたら幸いです。

    • ぜっきーさん

      コメントありがとうございます!

      このスクリプトは実際に弊社でも利用しているのですが、今のところ正しく動作しています。

      • ぜっきー より:

        タカハシ様

        迅速なご確認ありがとうございます。
        こちら、当方の確認不足でした。失礼致しました。

        トリガーの実行間隔を「1分」にしていたため、
        処理が完了する前に次の処理が発生していたことが原因でした。
        トリガーの実行間隔を「10分」に変更したところ、正常に動作しました。

        こちらの記事、大変参考になりました。
        ありがとうございます。

  2. スレッド困った より:

    丁寧な解説ありがとうございます。
    大変参考になりました。
    実際活用できるまであと一歩というところです。
    Gmailからスレッド単位で取得しているようで、1スレッドに複数のメールがまとまっている場合、一番古いメールしか取得していないことがわかりました。
    これをメール単位で処理できると、使えるようになるのですが、いい方法はありませんでしょうか。
    初心者なもので、全く応用がきかず・・・もしいい解決方法がありましたら、お教えいただけると大変ありがたいです。

    • コメントありがとうございます。

      確かにこの記事のやり方ですと、スレッドのうち一番目(最も古い)メッセージだけを取得する形になっていますね…
      2次元配列になっているのですが、そのすべてのメッセージについてチェックするようにする必要があります。

      追って記事を修正したいと思いますので、それまでお待ちいただくか、他の文献を参考に修正いただけると助かります。

  3. 山もん より:

    初めまして。よろしくお願いします。
    GASの記事を参考にさせていただきました。ありがとうございます。
    1点エラーが出て困ってます。
    デバッグをしたところ mySheetがundefinedで見つからないとなるのですが、シートの取得に失敗してるように思います。

    ですが、間違いなくシートはございます。

    ——-
    //コードの一部抜粋

    if(myMsgs.length>0){

    var mySheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘お客様問い合わせメール内容’); //シートを取得

    ——-
    //エラーの内容
    TypeError: null のメソッド「getSheetByName」を呼び出せません。

    • 山もんさん

      エラーを見る限り、シートではなくてスプレッドシートの取得に失敗していますね。
      スクリプトはコンテナバインドで作られていますよね?

タイトルとURLをコピーしました