【Googleスプレッドシート】SendGridの送信ログをSheetsに記録!配信状況の可視化

SendGridでメール配信を行っているものの、送信ログを確認するたびに管理画面を開くのが面倒だと感じたことはありませんか。特に多数のトランザクションメールを送信している場合、個別の配信状況を追跡するのは手間がかかります。この記事では、SendGridの送信ログをGoogleスプレッドシートに自動記録する方法を解説します。配信状況をスプレッドシート上で一覧できるようになり、管理がぐっと楽になります。

具体的には、SendGridのイベントWebhook機能とGoogle Apps Script(GAS)を組み合わせて、ログをリアルタイムに近い形で収集します。スプレッドシートには、送信日時、宛先、件名、配信ステータス(配信済み、開封済み、クリック済みなど)が自動で追記されます。この仕組みを導入すれば、メール配信の効果測定やトラブルシューティングがスムーズに行えるようになります。

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

SendGridは、メールの配信状況に関するイベント(配送成功、開封、クリック、バウンスなど)をリアルタイムで通知する「イベントWebhook」機能を備えています。このWebhookをGoogle Apps ScriptのデプロイURLに設定すると、イベントが発生するたびにHTTP POSTでJSONデータが送信されます。GAS側では、受信したJSONを解析し、特定のスプレッドシートのシートに行として追記します。これにより、手動でログをエクスポートすることなく、自動的に配信状況のデータベースが構築できるのです。

この仕組みを使用するには、Googleアカウント(スプレッドシートとGASの実行に必要)とSendGridのアカウント(少なくとも「Mail Send」の権限が必要)が必要です。また、SendGridの管理画面でWebhook設定を行うため、管理者権限があるとスムーズです。

SendGridの送信ログをスプレッドシートに記録する手順

ここからは、具体的な設定手順を説明します。大きく分けて「スプレッドシートとGASの準備」「SendGridのWebhook設定」「動作確認」の3ステップです。

1. スプレッドシートとGoogle Apps Scriptの準備

1. 新しいスプレッドシートを作成する
   Googleドライブから新しいスプレッドシートを作成します。シート名を「送信ログ」などに変更しておきましょう。このシートに、以下のカラムを用意します:日時(Timestamp)、イベント種別(Event)、宛先(Email)、件名(Subject)、その他(その他の情報を格納する列)。
2. スクリプトエディタを開く
   スプレッドシートのメニューから「拡張機能」→「Apps Script」を選択します。すると、新しいタブでスクリプトエディタが開きます。
3. Webhook受信用のコードを記述する
   デフォルトのコードを削除し、以下のコードを貼り付けます。このコードは、POSTリクエストを受け取り、JSONデータを解析してスプレッドシートに追記します。

   function doPost(e) {
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var data = JSON.parse(e.postData.contents);
     var events = data; // SendGridは配列で送信
     if (!Array.isArray(events)) events = [events];
     events.forEach(function(event) {
       var timestamp = new Date(event.timestamp * 1000);
       var row = [
         timestamp,
         event.event,
         event.email,
         event.subject || '',
         event.category || ''
       ];
       sheet.appendRow(row);
     });
     return ContentService.createTextOutput('OK');
   }

4. スクリプトを保存し、デプロイする
   スクリプトエディタの左上のフロッピーアイコンをクリックして保存します。次に「デプロイ」→「新しいデプロイ」を選択します。種類は「ウェブアプリ」を選び、アクセス権限は「全員(匿名ユーザーを含む)」に設定します。これはSendGridのWebhookが認証なしでアクセスするためです。デプロイを実行するとURLが発行されるので、コピーしておきます。

2. SendGridのイベントWebhook設定

1. SendGrid管理画面にログインする
   SendGridのアカウントにログインし、左側メニューから「Settings」→「Mail Settings」を選択します。
2. イベントWebhookを有効にする
   「Event Webhook」の設定項目を探し、「Activate」をクリックします。すると、URLの入力フィールドが表示されます。
3. GASのデプロイURLを設定する
   先ほどコピーしたGASのウェブアプリURLを入力します。必要に応じて送信するイベントの種類を選択できます。通常は「Delivered」「Opened」「Clicked」「Bounced」などを選択しておきましょう。設定が完了したら「Save」をクリックします。

3. 動作確認

1. テストメールを送信する
   SendGridからテスト用のメールを送信します。実際に配信されるメールアドレスでも構いません。送信後、しばらく待つとイベントが発生します。
2. スプレッドシートを確認する
   スプレッドシートの「送信ログ」シートに、日時やイベント種別などが自動で追加されていることを確認します。もしデータが追加されない場合は、GASのデプロイが正しく行われているか、Webhook設定のURLが間違っていないかを見直してください。

注意点とトラブル対処法

この連携を運用する上で、いくつか注意すべき点があります。ここでは、よく発生する問題とその対処法を説明します。

Webhookが届かない場合

SendGridからGASへのWebhookが正しく届かない原因として、GASのウェブアプリのアクセス権限が適切でないことが多いです。デプロイ時に「全員(匿名ユーザーを含む)」を選択しているか再確認しましょう。また、GASの実行時間制限(1回あたり6分)に注意してください。大量のイベントが短時間に発生すると、処理がタイムアウトする可能性があります。その場合は、トリガーを使って処理を分割するなどの対策が必要です。

データが重複して記録される

SendGridは同一イベントを複数回送信することがあります(再送信の可能性)。重複を防ぐには、GAS側でイベントID(sg_event_id)をキーにして重複チェックを行うと良いでしょう。既存のシートのイベントID列を検索し、同じIDがあればスキップする処理を追加します。

スプレッドシートの行数が増えすぎる

長期間運用すると、スプレッドシートの行数が数十万行に達する可能性があります。その場合は、定期的に古いデータを削除するか、別のシートにアーカイブするスクリプトを追加することを検討してください。また、Googleスプレッドシートの制限(セル数200万まで)に注意しましょう。

手動記録と自動連携の比較

まとめ

この記事では、SendGridのイベントWebhookとGoogle Apps Scriptを組み合わせることで、送信ログをGoogleスプレッドシートに自動的に記録する方法を紹介しました。一度設定しておけば、配信状況を常にスプレッドシートで確認でき、マーケティング施策の効果測定やエラーの早期発見に役立ちます。さらに、スプレッドシートのデータを使ってグラフを作成すれば、視覚的なダッシュボードとしても活用できます。ぜひ、自社のメール配信管理に取り入れてみてください。