【Googleスプレッドシート】SendGridメール送信ログを記録!Webhookでイベント受信

SendGridでメールを送信した後、その送信ログをGoogleスプレッドシートに自動で記録したいと思ったことはありませんか。SendGridはメールの配信状況をWebhookで通知する機能を備えており、そのイベントデータをスプレッドシートに蓄積することで、配信状況の分析やエラー追跡が手軽に行えます。本記事では、SendGridのWebhookをGoogle Apps Scriptで受け取り、スプレッドシートに書き込む方法を詳しく解説します。

SendGridのWebhookとスプレッドシート連携の仕組み

SendGridはメール送信に関する様々なイベント(送信成功、開封、クリック、バウンス、スパム報告など)をリアルタイムで外部サーバーに通知するEvent Webhook機能を提供しています。この機能を使うと、送信結果を自社のシステムに取り込めます。本記事では、そのWebhookの送信先としてGoogle Apps Scriptで作成したWebアプリのURLを設定し、受信したイベントデータをGoogleスプレッドシートに記録します。これにより、メール配信のパフォーマンス分析や障害対応が容易になります。

SendGridのWebhookイベントをスプレッドシートに記録する手順

以下の手順で、Webhookで受け取ったイベントをスプレッドシートに自動記録します。

1. SendGridでEvent Webhookを設定する

1. SendGrid管理画面にログインする
   左側メニューから「Settings」→「Mail Settings」を開きます。
2. Event Webhookを有効化する
   「Event Webhook」の行の鉛筆アイコンをクリックし、「Enable」にチェックを入れます。
3. 送信先URLを設定する
   「HTTP Post URL」に、後で作成するGoogle Apps ScriptのWebアプリURLを入力します。最初は仮のURLでも構いません。
4. 通知したいイベントを選択する
   「Select Actions」で、記録したいイベント(Delivered、Opened、Clicked、Bouncedなど)にチェックを入れます。
5. 保存する
   画面下部の「Save」をクリックします。

2. Google Apps Scriptを作成し、Webアプリとしてデプロイする

1. スプレッドシートを開く
   ログを記録するGoogleスプレッドシートを新規作成または既存のものを開きます。
2. Apps Scriptエディターを開く
   メニュー「拡張機能」→「Apps Script」をクリックします。
3. スクリプトを作成する
   エディターに以下のコードを貼り付けます。このコードは、POSTリクエストを受け取り、JSONデータをパースしてスプレッドシートに書き込みます。

   function doPost(e) {
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var data = JSON.parse(e.postData.contents);
     var events = data._json ? data._json : [data]; // 配列か単一オブジェクトに対応
     events.forEach(function(event) {
       var timestamp = new Date(event.timestamp * 1000);
       var row = [
         timestamp,
         event.event,
         event.email,
         event.sg_event_id,
         event.category ? event.category : '',
         event.reason ? event.reason : ''
       ];
       sheet.appendRow(row);
     });
     return ContentService.createTextOutput('OK');
   }

4. スクリプトを保存する
   プロジェクト名を適当に設定し、保存ボタンをクリックします。
5. Webアプリとしてデプロイする
   「デプロイ」→「新しいデプロイ」をクリックし、種類を「ウェブアプリ」に設定します。アクセスできるユーザーは「全員(匿名ユーザーを含む)」に変更します。これはSendGridから認証なくPOSTを受け取るためです。デプロイを実行し、生成されたURLをコピーします。

3. SendGridのWebhook URLにデプロイURLを設定する

1. SendGrid管理画面に戻る
   先ほどEvent Webhook設定の「HTTP Post URL」に、コピーしたApps ScriptのWebアプリURLを貼り付けます。
2. 保存する
   「Save」をクリックして設定を反映します。

4. テスト送信して動作確認する

1. SendGridからテストメールを送信する
   任意の宛先にテストメールを送信します。
2. スプレッドシートを確認する
   数分以内に、送信したメールのイベント(Deliveredなど)がシートに追加されていることを確認します。

運用時の注意点と制限事項

Webhookの署名検証を行わない場合のリスク

SendGridはWebhookリクエストに署名(Verified Signature)を付加できます。署名を検証しないと、第三者が偽のリクエストを送信してスプレッドシートを改ざんする恐れがあります。Apps Script内で署名検証を実装することを推奨します。ただし、検証にはSendGridのAPIキーが必要です。

大量のイベントが来た場合の制限

Google Apps Scriptには1回の実行で6分間という制限があります。また、スプレッドシートへの書き込みは1分間に60回までという制限もあります。大量のイベントが短時間に集中した場合、すべてのデータを記録できない可能性があります。その場合は、イベントをバッチ処理するか、BigQueryなどの外部サービスにデータを送ることを検討します。

重複データの防止策

SendGridは同じイベントを再送信することがあります。そのまま書き込むと重複データが発生します。対策として、イベントごとにユニークなID(sg_event_id)をキーにして、既存のシートに同じIDが存在する場合はスキップするロジックを追加します。

スプレッドシートの行数制限

Googleスプレッドシートは最大1000万セルという制限があります。大量のログを長期間蓄積すると上限に達する可能性があります。定期的に古いデータを削除するか、別のシートにアーカイブする運用を検討します。

手動記録とWebhook自動記録の比較

まとめ

本記事では、SendGridのEvent Webhookを利用してメール送信イベントをGoogleスプレッドシートに自動記録する方法を解説しました。この仕組みにより、メール配信の開封率やエラー率をリアルタイムに把握できるようになります。次は、収集したデータを基にダッシュボードを作成するなど、分析に活用してみてください。また、Apps ScriptのdoPost関数を拡張して、複数のスプレッドシートに振り分けたり、Slack通知と組み合わせることも可能です。