RPAの第1歩。受け取ったメールをGoogleスプレッドシートへ自動記録する

メールは通常、個人のメールボックスに届きます。一部、サポートや問い合わせなどのメールアドレスについては、全員に転送されるのではなく、決まったシステムの中に取り込んで集中管理されるものもあります。

今回はそうしたシステムを手軽に開発する第1歩として、Customers Mail CloudとGoogleスプレッドシートを使った方法を解説します。

Webhookの利用

Customers Mail Cloudにはメールを受け取ったら、特定のURLを呼び出すWebhook機能があります。そして、このWebhookを使ってGoogleスプレッドシートのGAS（Google Apps Script）を呼び出します。

Google Apps Scriptの内容

Googleスプレッドシートでスクリプトエディタを起動し、コードを記述します。まず、doPostという関数を作成します。これはGoogle Apps Scriptの指定するURLに対してPOSTメソッドがリクエストされた際に実行される関数です。

ここから先の内容は、この関数内に記述していきます。

function doPost(e) {
}

まず記録するのに必要な情報を集めます。今回はメールの送信元、送信日付、メッセージID、本文になります。

const params = JSON.parse(e.postData.getDataAsString());
// メールの送信元  
const fromName = params.headers.filter(o => o.name == 'From')[0].value
// メールの送信日付
const date = new Date(params.headers.filter(o => o.name == 'Date')[0].value)
// メッセージID
const id = params.headers.filter(o => o.name == 'Message-ID')[0].value

そして記録するスプレッドシートのオブジェクトを用意します。今回は メール というシートを作っていますが、必要に応じて変更してください。

// 記録するスプレッドシートについて
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('メール');

次にメッセージIDの重複チェックを行います。これは、WebhookでGoogle Apps Scriptを呼び出した際、場合によって302（リダイレクト）が発生するためです。リダイレクトしていてもエラーではないのですが、Customers Mail Cloudから複数回呼び出してしまうと言う問題があります。重複した記録を避けるため、メッセージIDを使って重複チェックを行っています。

findId関数は後述します。

if (findId(sheet, id)) {
  const output = ContentService.createTextOutput();
  output.setMimeType(ContentService.MimeType.JSON);
  output.setContent(JSON.stringify({ message: "success!" }));
  return output;
}

重複がなければ記録します。現在の一番下の行番号を取得し、それに +1 して記録する行とします。後は件名や本文を記録していき、念のためWebhookで送られてきた全データも残しておきます。

// 最終行を取得
const lastRow = parseInt(sheet.getLastRow()) + 1;
// 記録していく
sheet.getRange(lastRow, 1).setValue(id);
sheet.getRange(lastRow, 2).setValue(fromName);
sheet.getRange(lastRow, 3).setValue(params.subject);
sheet.getRange(lastRow, 4).setValue(date);
sheet.getRange(lastRow, 5).setValue(params.text);
sheet.getRange(lastRow, 6).setValue(JSON.stringify(params));

最後にレスポンスを返します。

// 結果のレスポンス
const output = ContentService.createTextOutput();
output.setMimeType(ContentService.MimeType.JSON);
output.setContent(JSON.stringify({ message: "success!" }));
return output;

全体のコードは次の通りです。

function doPost(e) {
  const params = JSON.parse(e.postData.getDataAsString());
  // メールの送信元  
  const fromName = params.headers.filter(o => o.name == 'From')[0].value
  // メールの送信日付
  const date = new Date(params.headers.filter(o => o.name == 'Date')[0].value)
  // メッセージID
  const id = params.headers.filter(o => o.name == 'Message-ID')[0].value
  // 記録するスプレッドシートについて
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('メール');
  if (findId(sheet, id)) {
    const output = ContentService.createTextOutput();
    output.setMimeType(ContentService.MimeType.JSON);
    output.setContent(JSON.stringify({ message: "success!" }));
    return output;
  }
  // 最終行を取得
  const lastRow = parseInt(sheet.getLastRow()) + 1;
  // 記録していく
  sheet.getRange(lastRow, 1).setValue(id);
  sheet.getRange(lastRow, 2).setValue(fromName);
  sheet.getRange(lastRow, 3).setValue(params.subject);
  sheet.getRange(lastRow, 4).setValue(date);
  sheet.getRange(lastRow, 5).setValue(params.text);
  sheet.getRange(lastRow, 6).setValue(JSON.stringify(params));
  // 結果のレスポンス
  const output = ContentService.createTextOutput();
  output.setMimeType(ContentService.MimeType.JSON);
  output.setContent(JSON.stringify({ message: "success!" }));
  return output;
}

findId関数は次のようになります。A列のデータをすべて取得して、メッセージIDが含まれるかどうかチェックします。

// すでに記録されたメールかどうかチェック
function findId(sheet, id) {
  const lastRow = sheet.getLastRow();
  const vals = sheet.getRange(2, 1, lastRow).getValues();
  return vals.flat().indexOf(id) > -1; // すでに記録されていれば
}

Webアプリケーションとしてデプロイ

コードができあがったら、Webアプリケーションとしてデプロイします。

f:id:moongift:20210916121049p:plain

そうすると、次のようなURLが生成されます。これをコピーします。

https://script.google.com/macros/s/AKf...zOw/exec

f:id:moongift:20210916120937j:plain

このURLはデプロイする度に変更されます。デプロイしたら、Customers Mail Cloud側のURL変更を忘れないでください。また、テスト版のURL（こちらは固定）もあるのですが、そのURLではうまく動きませんでした。通常のデプロイ版URLを利用するのが良いでしょう。

Customers Mail Cloudに設定する

Customers Mail Cloudの管理画面で、API設定 > Webhook > インバウンドとたどります。そこで追加を押して、Webhookの条件を追加します。例えば通知条件として、TO を指定し、値を support@example.com などとします。条件に正規表現が利用できるので、複数パターンをまとめて管理することもできます。

f:id:moongift:20210916120951j:plain