環境構築嫌いが作る、Googleサービスを利用したメール一斉送信システム

14976 ワード
googleform GoogleSpreadSheet mail zapier GoogleAppsScript GoogleAppsScript テキストリンク

一言でいうと、
Googleサービス（GAS、Form、スプレッドシート） ＋ Zapier でメンテナンスメール送信ツールを作りました。
※Zapierとは：タスク自動化ツール。様々なサービスと連携し、条件を組み合わせて実行させることができる。

背景

ある日、プロジェクトの内部MTG後に私はプロジェクトリーダーに声をかけられました。

PL「今、手動送信しているメンテナンスメールを一斉に自動送信することはできないか？」

自分の実装が終わり試験中だったので快く引き受け、早速要件を聞くところから始めました。

実装

なんちゃって要件定義

まず、実際に過去に送っていたメールを自分宛に送信してもらいました。
そこから必要な項目を抽出。加えてリーダーからの要望を聞きまとめました。

要件

  • 送るメールの種類は３種類
  • その内、１種類はPDFファイルが添付可能
  • ボタンをポチッと押したら、送信対象者全員に指定のメールが送信される
  • 約１週間後に実行したい（とりあえず仮実装レベルでOK）

私が考えた追加条件

  • システムの属人化を防ぐ
  • できれば環境構築したくない

もし私が休暇を取った場合やプロジェクトから外れてしまった場合、誰でも簡単に修正や調整をできるようにしたいと考えていました
また、なるべく送信者が簡単に操作できる方法にしたいので、ファイルは全て案件のGoogleDriveに置き、ブラウザから実行できるようにしようと思いました。

実際に作ったもの

ブラウザでGoogleフォームを入力し送信ボタンを押すと、メールが送信されるという流れ。

  1. メール送信ページにブラウザでアクセス。メールの種類を選択できるので、送りたいメールをクリック。
  2. 件名、本文などメールに必要な項目が入力できるGoogleフォームに遷移。（選んだメールのテンプレートが初期値となっている）
  3. フォームの送信ボタンを押すと、GASのメール送信履歴作成メソッドが呼ばれる。このメソッドによって、スプレッドシートに必要な情報を埋め込んだ状態でレコードを送信数分作成し、追加。
  4. Zapierが３のレコード追加を検知し、その内容を利用しメールが送信される。

（余談）実際に作ろうとしていたもの

当初Zapierを使わず、GASでメール送信まで全て完結させるつもりでしたが、
GMailの送信元の追加には管理者から権限が必要とのことだったので、諦めました。

必要なファイルを作成

ファイルは前述した通り、案件の共通フォルダに新しくフォルダを作成し、そこに全て置いていきます。
フォームの送信ボタンをトリガーにしたいので、GASはフォームから作成します。（後述）
今回、 値を埋め込む時は {項目名} と記述します。

  • GoogleForm（送信用）
  • スプレッドシート_メール送信リスト
  • スプレッドシート_テンプレートリスト
  • スプレッドシート_送信履歴

<スプレッドシート_メール送信リスト>

<スプレッドシート_テンプレートリスト>

<スプレッドシート_送信履歴>

フォームを作成し、最初に開くページを作成する

フォームの編集ページを開き、メール送信に使用する項目を入力できるようにします。

  • メンテナンス実行日(日付)
  • 件名
  • 本文
  • 署名
  • ファイル（PDFのみ、非必須）

今回はフォーム送信ボタンをトリガーにしたいので、フォームからGASファイルを作成します。
右上の「・・・」（メニュー）の中から「スクリプトエディタ」を選択。

一番最初に開くメール選択ページを作成します。
開いたGASエディタ内にindex.htmlを作成。(CSSフレームワークにmaterializeを使っていますが、コードは除外してありますが、classは残っています)
そして、そのファイルをGETでアクセスした時に表示するようにGETメソッドを記述します。

index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <title>メンテナンスメール管理</title>
  </head>
  <body>
    <h1>メンテナンスメール一斉送信</h1>
    <h2>どのメールを送りますか？</h2>
  </body>
</html>
index.gs
function doGet() {
  return HtmlService.createTemplateFromFile("index").evaluate();
}

メニューバーから公開を行い、生成されたURLからHTMLが表示されることを確認します。

メールのテンプレートをフォームの初期値に埋め込む

次に選択ページにフォームへのリンクを追加します。

まず、初期値有のフォームへのURLを取得する。

フォームのメニューバーの 「事前入力したURLを取得」 からフォームへ移動します。

ファイル以外の項目に適当に値を入力し、URLを生成します。
こちらは後ほど使うので、控えておきましょう。
適当に入力した値はのちに埋め込めるように、{項目名}に置き換えておきます。

copy_url
https://docs.google.com/forms/d/e/XXXX/viewform?usp=pp_url&entry.2121539746={件名}&entry.1652230460={本文}&entry.227080068={署名}

メールの種類分、フォームへのリンクボタンを表示する

最初に作成したメールテンプレートリストの内容を取得し、HTMLタグを生成するGASのメソッドを作成します。

[メールテンプレートのID]はURLから取得できます。
改行は .replace(/\n/g,'%0A') で置換して正しく表記されるようにしています。

index.gs
var formUrlTemplete = 'https://docs.google.com/forms/d/e/XXXXX/viewform?usp=pp_url&entry.2121539746={件名}&entry.1652230460={本文}&entry.227080068={署名}'
var aTagTemplate = '<a href="{url}" class="btn large col offset-s1">{ボタン名}</a>'

function createFormUrl() {
  var result = ""

  // メールテンプレート一覧を取得
  var spreadsheet = SpreadsheetApp.openById("[メールテンプレートのID]")
  var sheet = spreadsheet.getActiveSheet()
  var data = sheet.getRange('A2:D4').getValues()

  // ボタン生成
  for (var i = 0; i < data.length; i++) {
    var body = data[i][2] 
    var formUrl = formUrlTemplete.replace('{件名}', data[i][1]).replace('{本文}', body).replace('{署名}', syomei).replace('\n\n','%0A%0A').replace(/\n/g,'%0A')
    result += aTagTemplate.replace('{url}', formUrl).replace('ボタン名', data[i][0])
  }

  return result
}

HTMLで上記のメソッドを呼んで、生成したHTMLコードを埋め込む。

index.html
<body>
    <h1>メンテナンスメール一斉送信</h1>
    <h2>どのメールを送りますか？</h2>

    <div class="row"><?!= createFormUrl()?></div>
</body>

バージョンが上がったので公開し、表示を確認。
ボタンが表示され、クリックするとそれぞれ初期値が入ったフォームへ遷移できればOKです！

送信ボタンを押したら、スプレッドシートに送信履歴レコードを追加する

メール送信履歴を生成するメソッドを作成し、送信ボタンがトリガーになるように設定を行います。

メソッド作成

メール送信リスト分ループさせて履歴を作成します。

index.gs
var fromName = '送信者名'
var syomei = '仮署名'

/**
** メール送信メソッド
**/
function sendMail(e) {
  try {
    var formDate = e.response.getItemResponses()
    var formExecuteDate = formDate[0].getResponse()  
    var formSubject = formDate[1].getResponse()
    var formBody = formDate[2].getResponse()
    var formFooter = formDate[3].getResponse()
    var file = ''
    if (formDate[4] !== undefined) {
     file = DriveApp.getFileById(formDate[4].getResponse().toString())
     Logger.log(file.getName().replace(/\-.*\./g, '.'))
     file = file.setName(file.getName().replace(/\-.*\./g, '.'))

    }

    // メール送信情報
    var mailSubject = formSubject.replace('{date}', formExecuteDate)
    var mailBody = formBody.replace('{fromName}', fromName).replace('{date}', formExecuteDate).replace('{footer}', formFooter)

    // メール送信一覧を取得
    var mailList = SpreadsheetApp.openById("[メール送信リストのID]")
    var sheet = mailList.getActiveSheet()
    var data = sheet.getRange('A:C').getValues()

    // メール送信履歴に追加
    var mailSendHistory = SpreadsheetApp.openById("[メール送信履歴のID]")
    for (var i = 1; i < data.length; i++) {
      // データがなくなったら終了
      if (data[i][0] == '') {
        break
      }

      // メール履歴を追加
      var toMail = data[i][0]
      var sendMailBody = mailBody.replace('{name}', data[i][1] + ' ' + data[i][2])
      mailSendHistory.appendRow([e.response.getTimestamp(), e.response.getRespondentEmail(), formExecuteDate, mailSubject, sendMailBody, file, toMail, 'テスト()', 'addRowテスト'])
    }

  }catch(e){
    Logger.log('エラー')
    Logger.log(e)
  }
}

トリガーを設定する

メニューバーの　「編集」 > 「現在のプロジェクトのトリガー」 をクリックし設定画面を開きます。

右下の 「トリガーを追加」をクリックし、設定モーダルを開きます。
設定を下記のように変更し、保存。

  • 実行する関数：先ほど作成したメソッド
  • 実行するデプロイ：現状を実行するなら「Head」、公開した指定のバージョンなら実行したいバージョンを選択
  • イベントのソースを選択：「フォームから」
  • イベントの種類を選択：「フォーム送信時」

実行するデプロイは認識と異なると実行失敗してしまうので、気をつけましょう！！

送信履歴が追加されたら、メールが送信されるようにする（Zapierの設定）

Zapierの登録をしましょう。
ファイルの有無の分岐がうまくできなかったので、添付ファイルがある場合とない場合のZapの２つを作成します。
まずはある場合を作成し、そのあとにコピーして修正を加え、ない場合を用意します。

「Make a Zap!」から新しくZapを作ります。
１つ目のブロックのトリガーを設定します。
条件は「スプレッドシートにレコードが追加されたら」なので、「GoogleSheet」を選択し、「New Spreadsheet Row」をさらに選択します。
あとはアカウント情報と使うシートを選択します。使うシートはもちろん、フォームからレコードを追加する「メール送信履歴」ファイルです。

２つ目のブロックにフィルターを用意します。
フィルターは「Filter by Zapier」を選択し、条件を選択します。
１つ目のブロックのシートがインポートされているので検出対象に「file」列を選択し、条件を「Exists」に設定。

3つ目のブロックは添付するファイルを取得します。
使用するアプリは「GoogleDrive」、アクションイベントは「Find a File」を選択。
※案件のフォルダをマイドライブに追加しておきましょう（ドライブの対象フォルダの右クリックメニューからできます）

ファイル名は送信履歴のスプレッドシートに記述しているので、「file」列を選択。

次の項目ではファイル形式も指定できます。今回はPDFのみの想定なので、PDFを選択。

最後の4つ目のブロックはメール送信設定をします。
システムメールはAmazon SESを利用しています。アプリは「SMTP by Zapier」を選択。

Choose Accountで、IPやパスワードなど、メール送信に必要な情報を入力します。
Customize Emailでは、件名などの項目にメール送信履歴シートのどの値を使うか選択していきます。
添付ファイルは、Atachmentに３つ目のブロックで探したファイルを選択しましょう。

ある場合のZapはこれで完成です！
Zapをオンにしておきましょう、検知が始まります。
ない場合も忘れずに作成しましょう。

  • MyZapsからコピー
  • フィルターの条件を「file」列が「存在しない」にする
  • ファイルを探すブロックを削除
  • メール送信のAtachmentの項目をからにする

テスト

選択ページからフォームを開き、送信ボタンを押すとメールが送信されることを確認します。
メールを全通受け取ることができれば完了！！（メールの送信には数分かかることがあります。）
メール送信後のページに選択ページのURLを貼っておくと、元のページに戻れて便利です！

万が一エラーになった場合は、GASエディタでコンソールを開くか、Zapierのタスクヒストリーを見ると良いです。

まとめ

よかったこと

  • メールの内容を可変にすることで柔軟にメールを送れる
  • 利用者はブラウザで完結することができる
  • Googleフォームを利用することで、送信のフロント部分を考える必要なし

よくなかったこと

  • ソースのバージョン管理ができない（大したコード書いてないのでいんですけども...）
  • バージョン公開をうまく使いこなせていなかった　→管理方法を確定し、マニュアルを作成
  • zapierのアカウントが現在自分のものを利用しているので属人　→共通アカウントを作成すればOK

トラブル

メンテナンス開始メールの初回実行時、送信に失敗してしまい大変迷惑をかけてしまいました。
フォーム送信時に実行されるバージョンは公開しているものだと思って、中途半端にコードを放置していたのですが、
どうせ公開を忘れるだろうと過去の自分はHeadを実行するように設定していました。
結果、中途半端なコードが実行され、エラーに...
メンテナンス終了メールの本文に謝罪文を入れていただき、そのメンテナンスは無事に終わりました。（本文を編集できるようにしていてヨカッタ！！）

残課題

  • 添付ファイルが英語しか使えない →現状は英語縛りで運用
  • メール送信の監視
  • メール送信リストと署名をブラウザから管理できるようにする
  • 多言語化対応........(フロントの工夫を考え中)
  • ファイル名重複不可（ファイル名で検索しているため）
  • Zapierのお試し期間が終わってしまった（？）　→要確認

追記：
Zapierのタスク数（実行できるアクション数？）がMax100に到達していた為でした。
他の自動化のテストにも使っていたのでまぁそうなりますよねといった感じです。
メールを送る人やメンテナンス回数が増えることがあれば、要検討ですね...(１００人に対して送ったらおそらく途中で達してしまう)
とりあえず、来月を待ちます。

追追記：
Zapierに登録すると、勝手に２週間だけ特別なお試し期間が適用されるようです。
フリープランだと、１トリガー+1アクションのZapを５個しか動かせないし、フィルターも使えないし、月に100Taskしか実行できない(フリープランで作れるZapは1Taskなので100回)です。
これに加え、ランクが上のプランを１週間お試しで試せます。(フリープランに戻った時はTaskはリセットされてました、有難や〜)

最後に

これを作成したおかげでZapier使えば自動化簡単じゃん！と気がつくことができ、
他の無駄なタスクも自動化しちゃおうと手を動かすきっかけになったのがよかったです。

もともとこれは仮ツールでしたが、結局たくさん要望が出てきてしまったのでキチンと作り直すことに...
しかし、まだ何回かは使ってもらえるようなので、ほったらかしにせず引き続き改善していきます！

P.S.
環境構築は嫌いですが、Dockerでバシッとできるようになりたいとは思っていますので、ゆっくり頑張ります...