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

data-fetch

photo credit: Sebastiaan ter Burg Slimcity bijeenkomst via photopin (license)

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

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

前回の記事はコチラ。

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

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

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

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

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

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

スポンサーリンク

これまでのおさらい

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

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

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

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

こちらです。

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
      ]);
    }
  }
}

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

GASでメッセージ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でフォーム送信メールからデータを蓄積するツールの作り方について、完全版ということでお伝えしました。

正規表現、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でフォーム送信メールからデータを取得してスプレッドシートに蓄積するツールの完全版

コメント

  1. オノ より:

    はじめまして。
    こちらの一連の記事について大変参考になりました。ありがとうございます。
    1点お伺いしたいのでコメント致しました。
    完成形をコピー&ペーストして実行してみたのですが、

    >範囲の座標、またはサイズが無効です。(行 2、ファイル「コード」)

    というエラーが出て実行できません。
    色々試した結果、
    スプレッドシート側でヘッダーである1行目のみ入力されていてその他が全て空欄の場合
    (つまりシートを初めて使用する状況)でエラーが出るようです。
    A2の名前だけ、B2の年齢だけ、C2のメッセージIDだけ、ここでは関係ありませんがD2以降に適当な文字を入力している場合では実行可能でした。
    また2行目が全て空欄でも、3行目以降に何かしら入力されていれば実行可能でした。
    当方の誤処理によるものかもしれませんが、これはどのように対応したら良いでしょうか。
    ご教示いただけると幸いです。

  2. オノ より:

    お世話になっております。
    昨日質問させていただいた件なのですが、
    ・dataをグローバル変数化させずにhasId内での宣言に戻す
    ・C2セルが空欄の時(=初回使用時)は転記、そうでない場合は12行目のif文を実行する
    に変更した所、想定した動きになりました。
    もし他に良い対処方法がありましたらご教示下さい。
    良い記事を更新して下さりありがとうございます。

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