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


data-fetch

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

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

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

前回の記事はコチラ。

GASで列データの配列に特定の値が含まれているかを判定するsomeメソッドの使い方
Google Apps Scriptでフォーム送信メールからデータを取得してスプレッドシートに登録していくツールを作成しています。今回は、配列の中に特定の値が含まれているか判定をするsomeメソッドの使い方です。

someメソッドで配列になった列データから特定の値が含まれているかを判定する方法をお伝えしました。

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

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

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

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

これまでのおさらい

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

メッセージの取得とスプレッドシートへの追加

まず、メインの処理ですが、以下のmyFunctionです。

流れとしては

  1. 件名に「[テスト]フォーム登録通知」を含むスレッドを取得する
  2. 取得したスレッドから全てのメッセージを取得
  3. 取得したメッセージ全てについて
    1. メッセージIDを取得する
    2. メッセージ本文を取得する
    3. メッセージ本文から抽出した名前、年齢とメッセージIDをシートに追加する

という流れになります。

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

メッセージ本文から正規表現で必要なデータを抽出する

関数fetchDataは、メッセージ本文を渡して、そこから正規表現を使って必要なデータを抽出する関数です。

文字列strから、接頭辞pre、接尾辞sufに挟まれた部分のみを抽出するという処理をします。

この関数は今回いじる必要はないですね。

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

次に以下の関数hasIdです。

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

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

今回、この関数はメッセージIDを引数として受けて、trueかfalseをretrunするように作っていく必要がありますね。

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

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

こちらです。

スクリプトの修正ポイント

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

まず1,2行目ですが、スプレッドシートのデータを配列としたdataはmyFunctionとhasIdの両方で使うので、グローバル変数化しちゃいました。複雑なスクリプトでわけがわからなくなりそうな時は避けるべきですが、今回はそれほど大きなシステムでもないので良しとします。

続いて12行目のif文の条件式としてhasIdを呼び出しています。

hasIdは、既にメッセージIDが存在していればtrueを返すので、それを「!」演算子で判定して「指定したメッセージIDが存在しない」をifブロックの実行条件としています。

そしてそのhasIdですが、sheetとdataをグローバル領域に出したので、非常にすっきりしていますね。

まず引数としてidを受け取るようにして、someメソッドによる判定結果をそのままreturnしています。

実行結果

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

GASでメッセージIDをスプレッドシートに書き出す

この状態で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で列データの配列に特定の値が含まれているかを判定するsomeメソッドの使い方
  8. GASでフォーム送信メールからデータを取得してスプレッドシートに蓄積するツールの完全版

  投稿者プロフィール

タカハシノリアキ株式会社プランノーツ 代表取締役
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

コメント

  1. オノ より:

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

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

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

  2. オノ より:

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