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

photo credit: Pen Friends via photopin (license)

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

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

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

【GAS】Gmailのメッセージから返信先とパーマリンクを取得する方法

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

tonari-it.com

2020.05.01

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

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

ということで、今回は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メソッドを使います。

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

message.getId()

実行すると以下のようにメッセージ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オブジェクトのメソッドで、対象の配列を与えられた整数の深さに平滑化するものです。

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

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

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

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

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

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

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

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

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

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

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

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

試してみてくださいね。

まとめ

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

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

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

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

【GAS】Gmailに来た問い合わせをChatworkに通知してかつタスク追加する

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

tonari-it.com

どうぞお楽しみに！

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

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

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