GASでフォーム送信メールからデータを取得してスプレッドシートに蓄積するツールの完全版

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

Google Apps Scriptでフォーム送信メールからデータを取得してスプレッドシートに蓄積するツールを作成しています。

前回の記事はコチラ。

GASで二次元配列に特定の要素が含まれているかを判定する方法

Google Apps Scriptでフォーム送信メールからデータを抽出してスプレッドシートに登録するツールを作成しております。今回はGASで二次元配列に特定の要素があるかを判定する方法をお伝えします。

tonari-it.com

2020.05.14

二次元配列から特定の値が含まれているかを判定する方法をお伝えしました。

これでようやく以下のようなフローが実現できるのではないかと思われます。

• Gmailから条件にマッチしたメッセージを取得する
• 重複しないためにメッセージIDを取得する
• スプレッドシートに同じメッセージIDが存在していなければ
  • メッセージの本文から正規表現でデータを取得
  • 取得したデータとメッセージIDをスプレッドシートに追加

では、この流れでスクリプトを作成していきます。

GASでフォーム送信メールからデータを取得してスプレッドシートに蓄積するツールの完全版、行ってみましょう！

これまでのおさらい

これまで紹介したスクリプトについておさらいをしていきます。

メールからデータとメッセージIDを抽出する

以下の記事までで、メールから名前、年齢、メッセージIDを抽出する処理を作りました。

Google Apps ScriptでGmailのメッセージIDを取得する方法

Google Apps Scriptでフォーム送信メールからデータを取得してスプレッドシートに蓄積していくツールを作っています。今回は、GmailメッセージのメッセージIDを取得する方法です。

tonari-it.com

2017.11.15

こちらです。

function myFunction() {
 
  const fetchData = (str, pre, suf) => {
    const reg = new RegExp(pre + '.*?' + suf, 'g');
    const data = str.match(reg)[0]
      .replace(pre, '')
      .replace(suf, '');
    return data;  
  };
 
  const sheet = SpreadsheetApp.getActiveSheet();
 
  const query = 'subject:([テスト]フォーム登録通知)';
  const threads = GmailApp.search(query, 0, 30);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);
 
  for(const messages of messagesForThreads){
    for(const message of messages){
      const id = message.getId();
      const body = message.getPlainBody();
      sheet.appendRow([
        fetchData(body, '名前：', 'r'),
        fetchData(body, '年齢：', '才'),
        id
      ]);
    }
  }
}

実行すると、以下のようにデータを取得できます。

あと、処理としてはメッセージIDを取得した後に、「既にシートに同じメッセージIDが存在しない」かどうかを判定して、それがtrueの場合だけ、以降の処理を実行するように修正をする必要があります。

スプレッドシートに特定のIDが存在しているかを判定する

そして、前回の記事で以下の関数hasIdを作りました。

const hasId = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getRange(2, 3, sheet.getLastRow() - 1).getValues();
  const id = '172090bddef1e273';
  console.log(data.flat().includes(id));
}

この関数は、特定のメッセージIDがスプレッドシートのメッセージIDを蓄積している列であるC列に既に存在しているかどうかを判定するものです。

既にメッセージIDが存在していればログにはtrueと出力されます。

今回、この関数はメッセージIDを引数として受けて、trueかfalseをretrunするように修正していきます。

GASでフォーム送信メールからデータを取得してスプレッドシートに蓄積するスクリプト

ということで、以上の関数たちを使って、ツールを完成させてみました。

こちらです。

function myFunction() {
 
  const fetchData = (str, pre, suf) => {
    const reg = new RegExp(pre + '.*?' + suf, 'g');
    const data = str.match(reg)[0]
      .replace(pre, '')
      .replace(suf, '');
    return data;  
  };
  const sheet = SpreadsheetApp.getActiveSheet();
 
  const hasId = id => {
    const data = sheet.getRange(2, 3, sheet.getLastRow() - 1).getValues();
    return data.flat().includes(id);
  }
 
  const query = 'subject:([テスト]フォーム登録通知)';
  const threads = GmailApp.search(query, 0, 30);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);
 
  for(const messages of messagesForThreads){
    for(const message of messages){
      const id = message.getId();
      if(hasId(id)) continue;
      const body = message.getPlainBody();
      sheet.appendRow([
        fetchData(body, '名前：', 'r'),
        fetchData(body, '年齢：', '才'),
        id
      ]);
    }
  }
}

メッセージIDが重複するか判定してスキップする

上記スクリプトの修正ポイントは以下の通りです。

関数hasIdは、引数としてidを受け取るようにして、そのIDがC列のデータ範囲に含まれていればtrue、さもなければfalseを返します。

その関数hasIdは24行目のif文の条件式として使われていて、trueの場合にはcontinue文でその回の以降の処理をスキップします。

falseのときだけ、本文からのデータの抽出とスプレッドシートへの行追加が行われるわけです。

実行結果

対象となるシートはこちらです。

本記事の冒頭のシートの状態でmyFunctionを実行しても、何の変更も起きません。

なにせ、メッセージIDがすでに存在していますからね。

以下のように、メッセージIDをC列から退けた状態で再度myFunctionを実行してみましょう。

すると、以下のように新たなレコードが追加されます。

これで、新たなメッセージのデータだけ追加されるようになりました。

まとめ

以上、GASでフォーム送信メールからデータを蓄積するツールの作り方について、完全版ということでお伝えしました。

正規表現、Gmail、スプレッドシート、配列となかなか役に立つテクニックが散りばめられているのではないかと思います。

ぜひ、使いこなしてみてくださいね。

また、GASの便利なツールの作り方について、折を見てお伝えしますね。

どうぞお楽しみに！

連載目次：GASで正規表現を使ってデータを抽出＆登録

正規表現を使うとGoogle Apps ScriptでメールやHTMLドキュメントからデータを抽出することができます。本シリーズでは、フォーム送信の通知メールからデータを抽出してスプレッドシートに追加するツールの作成を目指します。

1. Google Apps Scriptで正規表現を使って必要な情報を抽出する最も簡単なスクリプト
2. Google Apps Scriptで正規表現でマッチした文字列から不要な部分を削除する
3. Google Apps ScriptでGmailの受信メールから正規表現でデータを抽出する
4. Google Apps Scriptでメールから正規表現で抽出したデータをスプレッドシートに蓄積する方法
5. Google Apps ScriptでGmailのメッセージIDを取得する方法
6. Google Apps Scriptでスプレッドシートの列データを配列として取得する方法
7. GASで二次元配列に特定の要素が含まれているかを判定する方法
8. GASでフォーム送信メールからデータを取得してスプレッドシートに蓄積するツールの完全版