📧

未記入リマインドメールの自動送信ハンズオン

2023/03/29に公開

Gmail

Google Sheets

Google Apps Script

tech

リマインドメールはメール振り分け設定によって無視されたり、自分が対象外だと誤解されることがあります。しかし、一人一人にメールを送ることは手間がかかります。今回はGoogleスプレッドシートとわずかなGoogle Apps Scriptを使用して、自動で対象者だけにメールを送信する方法を紹介します。

今回は下記の構造で自動化を進めていきます。

Googleスプレッドシートで指定のセルに記入していない人を抽出する
スプレッドシートからメールを送る
抽出からメール送信までを自動化する

1. Googleスプレッドシートに記入していない人を抽出する

今回スプレッドシートに必要な情報は、下記4点になります。

記入済みかどうかを判定するセル
記入されていなかった時にメールを送る送り先
メールを送る日付
(日付の代わりに営業日で指定する場合)祝日や会社の休日

記入用シートを用意する

今回は単純なものを想定します。記入する欄をC列に設け、メールの送信先をB列で規定しているものとします。

メールの原稿を作成する

原稿は別のシートに用意します。配信日、宛先、件名、本文、祝日などの情報を記載します。今回は配信日として、「今月の第一営業日」という仮の日付を設定します。具体的には、以下の関数を使用します。

=WORKDAY(EOMONTH(TODAY(),-1),1,A7:A)

この関数を使えば、毎月自動的に第一営業日にメールが送信されるようになります。この関数は、「今日の日付の先月の最終日の次の営業日」を表すものであり、要約すると今月の第一営業日になります。この関数は、TODAY関数を使って今日の日付を取得し、EOMONTH関数を使ってその月の最終日を取得し、さらにWORKDAY関数を使って営業日を計算しています。
次に、宛先欄の関数について説明します。以下の関数を使います。

=TEXTJOIN(",",TRUE,QUERY('記入'!A2:C,"select B where C=''"))

この関数は、記入シートのC列が空の場合にB列の行をカンマ区切りで引用するためのものです。QUERY関数を使用して、未入力のセルの行のメールアドレスを取得し、TEXTJOIN関数を使用してカンマ区切りで結合しています。

2. スプレッドシートからメールを送る

次に、自動化する処理を作成します。自動化シートのシート名を取得し、その中の指定のセルをGmailのフォーマットに落とし込んでいます。

コードを埋め込む

まず、スプレッドシートのスクリプトエディタを開きます。

次に、下記のコードをコピペします。

コード.gs

const sheet = SpreadsheetApp.getActive().getSheetByName('メール自動化');//''の間にシート名を入れるとそのシート名の情報を持ってくる
const recipient = sheet.getRange(2, 2).getValue(); // TO B2
const subject   = sheet.getRange(3, 2).getValue(); // 件名 B3
const body      = sheet.getRange(4, 2).getValue(); // 本文 B4
const remind_hour = "9";//この時間だけ動作
const date = Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyy/MM/dd"); //今日の日付
const hour = Utilities.formatDate(new Date(), "Asia/Tokyo", "H"); //今の時間
const workday = Utilities.formatDate(sheet.getRange(1, 2).getValue(), "Asia/Tokyo", "yyyy/MM/dd"); //配信日 B1

function sendMail(){
 if(hour == remind_hour && workday == date){ //日時が合っているか
    GmailApp.createDraft(recipient, subject, body); //下書きにする場合はcreateDraft、直接送る場合はsendEmail
    }
}

function onOpen(){
 SpreadsheetApp.getUi()
 .createMenu('メール')
 .addItem('メール送信', 'sendMail')
 .addToUi();
}

コードをテストする

onOpenを選択して、実行ボタンを押します。

初回だけ下記ポップアップが出てくるので、「権限を確認」をクリックします。

その後、複数のポップアップ表示されますのですべて許可をしてください。
無事onOpenが実行できると、スプレッドシートのツールバーに手動でメール送信するメニューが追加されます。

現状のコードだとメール送信はされませんので、テストでメール送信ボタンを押下してください。
スプレッドシートのメール自動化シートB1の日付と今日の日付、スクリプトのremind_hourと今の時間が合致していればメールボックスに下書きが保存されます。

何も起きない場合はB1とremind_hourを合わせてテストしてみてください。
テストに問題がなければ、コードの12行目、createDraftをsendEmailに置換しましょう。
こうすることで、実際にメールが送られる設定になりました。

3. 抽出からメール送信までを自動化する

2で作った処理を毎時自動で動作させます。コードを開いて、左メニューのトリガーを選択してください。

次の画面で右下の「トリガーを追加」を選択し、「sendMailを毎時発火する」トリガーを設定します。

そうすると、パソコンを閉じていても毎時動作して、指定の日時の場合のみメールが送られるトリガーが完成しました。

補足

今回はごく単純なメール自動化について説明しました。
Google Apps Scriptは、スプレッドシートの列や行を追加したり削除したりする際に柔軟に対応することができません。しかし、読み取りやすいコードであるため、sheet.getRangeの参照セルを変更することで対応できます。また、「GAS sendEmail」で検索することで、CCなどの機能を追加することもできます。

QUERY関数のように入り組んだ関数もありますが、IF文でそれらの部分を書き出し、TEXTJOIN関数の対象とする範囲を決めることもできます。このような方法を使えば、簡易的なマーケティングオートメーションを構築したり、Googleフォームでアンケート未記入の人にのみメールを送信したりすることもできます。