メールのイベントデータを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の詳細については以下のページも参考にしてください。
