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

メールのイベントデータを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

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

Event WebhookでPOSTされるパラメータ名を先頭の行に入力

Power Automateのフローの作成

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

Webhookを受信する設定

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

Power Automateの左側のメニューから「作成」を選んで「インスタント クラウド フロー」を選択

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

一番下にある「HTTP要求の受信時」を選んで「作成」ボタンを押す

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

「要求本文の JSON スキーマ」に下の文字列をコピー&ペースト、「method」を「POST」にする

{
    "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 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」のボタンをクリックします。

Event Webhookの設定

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

スプレッドシートに書き込まれる

おわりに

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

アーカイブ

メールを成功の原動力に

開発者にもマーケターにも信頼されているメールサービスを利用して、
時間の節約、スケーラビリティ、メール配信に関する専門知識を手に入れましょう。