SpreadsheetとGoogleAppsScriptでメールテンプレートに任意の文字列を埋め込んで一斉送信できるようにする

人事がメイン業務だけど元々エンジニアだったから手元で業務効率化に取り組む、ジンジニア（人事＋エンジニア）一発目の記事です。

社内/社外問わず、めっちゃメールを送ることが多いです。

できるだけ丁寧に、だけど一人ひとりにお知らせしたい細かい内容の違う時が多く、手作業でやったら半日メール送信で終わることもある量。
うちの会社ではGoogleAppsのメールを使っているのですが、内容的にできるだけ外部のエクステンションとか使いたくない。
ということで、自分でテンプレートを個別にカスタマイズしてメールを一斉送信できるものをSpreadsheetとGoogleAppsScriptで作ってみました。
手前味噌ですが安心＆快適でフル活用中です！

設定方法

Spreadsheet側

テンプレート用シート

こんな感じでよしなにタイトルや本文、埋め込みたい変数などを設定します。一応Fromにalias使えるようにもしています。

送信先シート

一斉に送りたい先のメールアドレスと、使用するテンプレート、埋め込みたい文字列をセットします。

Spreadsheet側はこれで設定完了。あとはこれを元にしてよしなにメールを送ってくれるスクリプトを書きます。

GoogleAppsScript

やっていることは2つだけで、メール送信を実効するメソッドと、ボタン一つで実行するためのメニュー追加のメソッド、という感じです。

メール送信スクリプト

API叩きすぎ疑惑は置いておきましょう。。いつか直したいな。。

function sendMail(e){
  var COL_S_TEMPLATE_NO = 1;
  var COL_S_ADDRESS = 2;
  var COL_S_VAL1 = 3;
  var COL_S_VAL2 = 4;
  var COL_S_VAL3 = 5;
  var COL_S_VAL4 = 6;
  var COL_S_VAL5 = 7;
  var COL_T_NO = 1;
  var COL_T_TITLE = 2;
  var COL_T_BODY = 3;
  var COL_T_VAL1 = 4;
  var COL_T_VAL2 = 5;
  var COL_T_VAL3 = 6;
  var COL_T_VAL4 = 7;
  var COL_T_VAL5 = 8;
  var COL_T_ALIAS = 9;
  var COL_T_FROM_NAME = 10;
  var COL_T_HTML_BADY = 11;
  var COL_T_ATTACHMENT = 12;
  
  var ss = SpreadsheetApp.getActive().getSheetByName("送信設定")
  
  var cols = ss.getLastColumn();
  var rows = ss.getLastRow();
  var rg   = ss.getDataRange();
  var templ = SpreadsheetApp.getActive().getSheetByName("テンプレート");
  
  for(var i = 2; i <= rows; i++){
    
    // 対象行がチェック
    var col_select = rg.getCell(i,1).getValue();
    if(col_select == ""){
      continue;
    }
    
    // カラム情報の取得
    var sAddressVal = rg.getCell(i, COL_S_ADDRESS).getValue();
    var s1Val= rg.getCell(i, COL_S_VAL1).getValue();
    var s2Val = rg.getCell(i, COL_S_VAL2).getValue();
    var s3Val = rg.getCell(i, COL_S_VAL3).getValue();
    var s4Val = rg.getCell(i, COL_S_VAL4).getValue();
    var s5Val = rg.getCell(i, COL_S_VAL5).getValue();
    var sTemplateNoVal = rg.getCell(i, COL_S_TEMPLATE_NO).getValue();

    // テンプレートの指定
    var subject = templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_TITLE).getValue();
    var body = templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_BODY).getValue();
    var aliasesNo = templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_ALIAS).getValue();
    var aliases = GmailApp.getAliases();
    var fromName = templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_FROM_NAME).getValue();
    
    // 埋め込み変数のリプレイス
    var body = body.replace(templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_VAL1).getValue(), s1Val);
    var body = body.replace(templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_VAL2).getValue(), s2Val);
    var body = body.replace(templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_VAL3).getValue(), s3Val);
    var body = body.replace(templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_VAL4).getValue(), s4Val);
    var body = body.replace(templ.getDataRange().getCell(sTemplateNoVal + 1, COL_T_VAL5).getValue(), s5Val);
    
    // alias使うかどうか振り分けてメール送信
    if (typeof(aliasesNo) == 'number') {
      GmailApp.sendEmail(sAddressVal, subject, body , {'from': aliases[aliasesNo], 'name': fromName,});
    } else {
      GmailApp.sendEmail(sAddressVal, subject, body);
    }      
  }
}

実行メニューの追加

画面上部に実行できるメニューを表示させておきます。

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "メールを送信する", functionName: "sendMail"}];
  ss.addMenu("★実行メニュー★",menuEntries);
}

すると、こんな感じでメニューが出ます。

あとは実行するだけ！

で、このメニューから実行をするとこんな感じで内容が変わったメールが届きました！

1通目

2通目

やったね！

本題

最近はもっぱら手軽にできてしまうGoogleAppsScriptばっかり書いていますが、本来使うべきことに時間を使えるようになるのでこういう自動化は大事ですね。
次は権限制御したりとかGoogleグループのメンバーを書き出すスクリプトなんかについて書こうかなと思います。

さて、こんなジンジニアが人事をやっている会社では、フロントエンドからバックエンド、データエンジニアリングまで全域でアドテクやりたいエンジニアの方を募集しています！
ちょっと話聞かせろやくらい感じで遊びに来てくれたら嬉しいです！
【株式会社ジーニー リクルートサイト】
http://geniee.co.jp/recruit/index.html

ではまたー。