Googleスプレッドシートでメール文面を自動生成する

いわゆる差し込み印刷的なものです。

Googleスプレッドシートでお客さんであったり、スポンサーなどを一覧で管理していることって多いかと思います。例えばこんな感じ。

で、この相手にメールを送りたいとして、メールの文面を相手の情報によって置き換えながら作成したいことがよくあります。例えば最初のデータの場合はこんな感じ。

ここはメール文面。

AAA ナカツガワアツシ さん宛のメールです。

会社名とか、名前を置き換えているイメージです。手作業でやるのは面倒なのでGoogle Apps Scriptで実現します。

メールの文面を作る

例えばメールの文面は次のようになります。これは別なシートに作成します。

ここはメール文面。

%会社名% %名前% さん宛のメールです。

ここの %〜% で囲まれている部分が置き換わるイメージです。

Google Apps Scriptの作成

Google Apps Scriptの関数を作成します。

function template(cell) {
  // 現在のスプレッドシート
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // 現在のシート
  var sheet = ss.getActiveSheet()
  // 現在の行数
  var row = ss.getActiveCell().getRow();
  // 一行目（置き換えるキーワード）の列数を取得
  var lastCol1 = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
  // 置き換えるキーワードを全部取得
  var ary = sheet.getRange(1, 1, 1, lastCol1).getDisplayValues()[0];
  // 置き換わる文字列を全部取得
  var values = sheet.getRange(row, 1, row + 1, lastCol1).getDisplayValues()[0];
  // 置き換え実行
  for (var i in ary) {
    var r = new RegExp('%' + ary[i] + '%', 'g');
    cell = cell.replace(r, values[i]);
  }
  return cell;
}

やっていること

シートの一行名をキーワード（会社名など）として、取得します。後はアクティブなセル（関数を実行しているセル）がある行に並んでいるデータを置き換え後の文字列としています。置換を正規表現にしているのは、キーワードが複数回使われている場合に備えています。カラム名に微妙な正規表現的な文字列があると誤動作するかも…。

関数を実行

関数は次のように実行します。!を使って行、列を固定しておかないと下方向コピーした時にセルがずれてしまうので注意してください。 'シート2'!A1 にはメールの文面が入っています。

=template('シート2'!$A$1)

メール送信

メール送信は手元のメーラーでもいいですし、Google Apps Scriptからメールを送信するには - Customers Mail Cloud ブログを参考にGoogleスプレッドシートからメール送信することもできるでしょう。