メールのイベントデータをGoogleスプレッドシートに保存する方法

Twilio SendGridではメール到達などのイベントを確認できるActivityという機能を提供していますが、この内容をそのままエクスポートすることはできません（※追記：2024年のアップデートにより、エクスポート可能になりました）。その代わりに、イベントのデータを任意のURLにPOSTするEvent Webhookを提供しており、データを好きな場所（サーバ）に逐次保存することができます。ただ、利用にはHTTPレスポンス処理などの専門知識が必要なため、構築ハードルが少し高いのがネックです。

今回はMicrosoftが提供する自動化ツール（RPA; Robotic Process Automation）であるPower Automateを用いて、プログラミングの知識がない方でもGoogleスプレッドシートにイベントデータを保存できる方法を紹介します。

必要なもの

• SendGridアカウント
  • 作成済みである前提で進めます（作成は新規会員登録ページから）。
• Googleアカウント
  • 持っていない場合は新規作成します。
• Microsoftアカウント
  • 持っていない場合はPower Automateのサイトから登録してください。
  • なお今回のフローは「プレミアムコネクタ」が利用可能な有料プランが必要です。企業や教育機関のOffice365アカウントがあれば、90日間無料試用できる場合もあるようです。

手順

Googleスプレッドシートの準備

Googleドライブのマイドライブに、新しいスプレッドシートを作成します（左上の新規ボタンから）。今回は「powerautomate_eventwebhook」というファイル名をつけます。
下の図のように、Event WebhookでPOSTされるパラメータ名を先頭の行に入力しましょう。以下の文字列をA1セルにコピー＆ペーストしたあと、セルの右下に表示されるオプションから「テキストを列に分割」をクリックすればOKです。

email, timestamp, event, smtp-id, sg_event_id, sg_message_id, response, status, reason, type, asm_group_id, ip, url, useragent

なお、このリストはパラメータを網羅しているわけではなく、代表的なものを抜き出しています。このほかにも種類はあり、ユーザ自身が定義できるものもあります。

Power Automateのフローの作成

Power Automateのブラウザ版を利用して、実際にフローを作成します。

Webhookを受信する設定

最初に、SendGridからPOSTされるデータを受け取る設定を行います。
まず、Power Automateの左側のメニューから「作成」を選んで「インスタント クラウド フロー」を選択します。

フローのトリガーを選択する画面になるので、一番下にある「HTTP要求の受信時」を選んで「作成」ボタンを押します。

するとフロー作成画面になり、「HTTP要求の受信時」のボックスが表示されるのでクリックします。「要求本文の JSON スキーマ」に下の文字列をコピー＆ペーストしてください。また詳細オプションを表示して、「method」を「POST」にします。ここではSendGridからEvent Webhookで送られてくるデータの型を定義しています。
最後に「＋新しいステップ」を押します。

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "email": {
                "type": "string"
            },
            "timestamp": {
                "type": "integer"
            },
            "smtp-id": {
                "type": "string"
            },
            "sg_event_id": {
                "type": "string"
            },
            "sg_message_id": {
                "type": "string"
            },
            "event": {
                "type": "string"
            },
            "type": {
                "type": "string"
            },
            "response": {
                "type": "string"
            },
            "status": {
                "type": "string"
            },
            "reason": {
                "type": "string"
            },
            "ip": {
                "type": "string"
            },
            "url": {
                "type": "string"
            },
            "useragent": {
                "type": "string"
            },
            "asm_group_id": {
                "type": "integer"
            }
        },
        "required": [
            "email",
            "timestamp",
            "sg_event_id",
            "sg_message_id",
            "event"
        ]
    }
}

Googleスプレッドシートに書き込む設定

次にGoogleスプレッドシートとの繋ぎ込みを行います。
どのようなアクションを行うかを設定しましょう。検索欄で「google sheets」と入力すると「行の挿入」というアクションが表示されるので選択します。

ここでGoogleアカウントのログインが求められます。Power AutomateがGoogleスプレッドシートを操作できるように、アカウントをPower Automateと連携させることになります。表示に従ってPower Automateからのアクセスを許可しましょう。

表示される「行の挿入」をクリックすると、詳細な内容を入力する画面になります。「ファイル」欄の右端のフォルダアイコンをクリックして、Googleドライブの中にあるスプレッドシート（今回はpowerautomate_eventwebhook）を選択します。「ワークシート」にはスプレッドシートでパラメータを入力したシートの名前を選択してください（今回はデフォルトから変更していないので「シート1」）。
少し待つとPower Automateがスプレッドシートを読み込み、シートの先頭行に入力したパラメータ（email、timestamp…）が追加で表示されます（下図）。ここではそれぞれのカラムに挿入する値を決めることができます。入力欄をクリックするとWebhookの受信のステップで設定したパラメータ名が表示されるので、シートのパラメータと対応するものを選択してください。これで、SendGridから送られてきたデータの「email」パラメータの値を、スプレッドシートの「email」カラムに挿入するように設定できました。全て入力し終えたら画面下の「保存」をクリックします。

最後にもう一度、「HTTP要求の受信時」のボックスをクリックしましょう。すると「HTTP POSTのURL」の欄にPOST用のURLが表示されているのでコピーしておきます。

Event Webhookの設定とテスト

SendGridにログインして、Settings > Mail Settings からEvent Webhooksにアクセスします。「Create new webhook」を押すと右側に設定画面が表示されるので、「Friendly Name」に任意の名前を入力し、「Post URL」に先ほどコピーしたURLをペーストします。「Actions to be posted」のチェックボックスには全てチェックを入れ、「Security features」はオフのまま、下部左側の「Test Integration」のボタンをクリックします。

するとSendGridからテスト用のデータがPower AutomateにPOSTされ、Power Automateがそれをスプレッドシートに書き込みます。スプレッドシートを確認して、以下の図のようにデータが入っていたら成功です！Event Webhookの画面に戻って「Save」ボタンを押し、設定を完了させましょう。

おわりに

このように、Power Automateを使うとプログラミングの知識がなくてもメールのイベントのデータを保存することができます。今回は出力先としてGoogleスプレッドシートを選びましたが、Online版ExcelやSlackへの出力などにも応用可能です。うまく活用してメールのデータを収集しましょう。
SendGridのイベントやEvent Webhookの詳細については以下のページも参考にしてください。

• イベントの種類に関するチュートリアル
• Event Webhookのチュートリアル
• Event Webhookのドキュメント（イベントのパラメータの説明など）