スプレッドシートのチェックボックスでメールを送信できるようにする

はじめに

前回はフォームの回答をLINEに通知する機能を実装し、スプレッドシートとGASを使えば無料で様々なことができると紹介した。

www.komee.org

フォームのアンケートでは、ユーザ情報でメールアドレスを収集していることも度々ある。
今回は、収集したメールアドレスにワンクリックでメールを送信する機能を実装していく。

全体の流れ

• フォームにメールアドレスとユーザ名を追加
• 回答のスプレッドシートにチェックボックスを追加
• GASで実装

フォームの変更

フォームで集める情報に、以下の情報を追加する。

• ユーザ名
• メールアドレス
• 確認用メールアドレス
  

  

  フォーム変更

スプレッドシートにチェックボックスを追加

メールはチェックボックスをオンにした時に送信するようにしたいので、各回答ごとにチェックボックスを追加する。
チェックボックスは、挿入したいセルを選択し、上部メニューバーで挿入>チェックボックスをクリックすることで挿入できる。1つのセルに挿入したら、そのセルをコピーして同じ列にペーストするとすぐに終わる。

チェックボックス

メール送信機能の実装

前回同様、ツール>スクリプトエディタをクリックし、スクリプトエディタを起動する。 新たにメール送信のために追加したコードは、以下のとおりである。

function showCheckboxInfo() {
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell();
    var rule = myCell.getDataValidation();
    if (rule != null) {
        var criteria = rule.getCriteriaType();
        var status = myCell.getValue()
            if ( criteria == 'CHECKBOX' && status == true) {
                var row = myCell.getRow();
                sendMail(row);
            }
    }
}

function sendMail(row) {
    var spreadsheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxxxxxxxxxx");
    var datasheet = spreadsheet.getSheetByName('answer');

    var name = datasheet.getRange(row, 4).getValue();
    var email = datasheet.getRange(row, 5).getValue();

    var subject = 'タイトル';
    var body = 'メール本文\nメール本文\nメール本文';

    MailApp.sendEmail(email, subject, body);
}

SpreadsheetApp.openById()は、スプレッドシートを選択する別の関数である。getActiveSheetでも問題ないが、別のものを紹介してみた。 IDには、スプレッドシートを開いた時のURLの、https://docs.google.com/spreadsheets/d/xxxxxxのxxxxxの部分を切り取って入れると認識してくれる。

showCheckBoxInfo()

ここでは、更新されたセルがチェックボックスであるかを確認している。対象のセルがチェックボックスで、チェックされた状態になっていたら、メール送信機能を呼び出す仕組みとしている。

var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
var myCell = mySheet.getActiveCell();

まずここまでで、更新されたセルの場所までを取得している。

var rule = myCell.getDataValidation();
if (rule != null) {
    var criteria = rule.getCriteriaType();
    var status = myCell.getValue()
        if ( criteria == 'CHECKBOX' && status == true) {
            var row = myCell.getRow();
            sendMail(row);
        }
}

更新されたセルのデータタイプとチェックボックスのステータスを取得する。データタイプを取得する過程でデータタイプが存在しない場合はこのスコープは実行されない。
更新されたセルのデータタイプがCHECKBOX(チェックボックス)、値がtrue(チェックされた状態)となっていたら、更新された行番号をメール送信機能に渡し、メールが送信される。

sendMail(row)

メールを送信する機能である。

var spreadsheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxx");
var datasheet = spreadsheet.getSheetByName('answer');

var name = datasheet.getRange(row, 4).getValue();
var email = datasheet.getRange(row, 5).getValue();

まず引数で渡されたrowに基づき、ユーザ名とメールアドレスを取得しそれぞれnameとemailに格納している。

var subject = 'タイトル';
var body = name + '様\nメール本文\nメール本文\nメール本文';

MailApp.sendEmail(email, subject, body);

更にタイトルとメール本文をsubject、bodyに格納する。 最後にメールアドレス、タイトル、本文をMailApp.sendEmail()に渡し、メールを送信する。
この時差出人メールアドレスには、このGASの所有者のGoogleアカウントのメールアドレスが使われる。

トリガーの設定

さらにトリガの設定をする。今回は、スプレッドシートの更新が発生したタイミングでshowCheckboxInfo()がトリガされるようにした。チェックボックス以外の更新の場合は、showCheckBoxInfo()の中で場合分けされ、メール送信がされないようになっている。

トリガーは以下のように設定する。

トリガー設定

メール誤爆阻止機能の追加

上記プログラムだと、チェックボックスを押した瞬間にメールが飛んでしまう。
間違えて押してしまった場合、時既に遅しとなってしまうため、チェックしたら確認画面へ遷移するようにコードを改良した。

function showCheckboxInfo() {
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell();
    var rule = myCell.getDataValidation();
    if (rule != null) {
        var criteria = rule.getCriteriaType();
        var status = myCell.getValue()
            if ( criteria == 'CHECKBOX' && status == true) {
                var row = myCell.getRow();
                var result = Browser.msgBox('チェックボックスが押されました。メールを送信しますか？', Browser.Buttons.OK_CANCEL);
                if(result == 'ok'){
                    sendMail(row);
                } else if (result == 'cancel'){
                    mySheet.getRange(row,11).uncheck(); // チェックを外す
                }
            }
    }
}

キャンセルを押したら、チェックボックスがクリックされなかったこととなり、メールは送信されないようになる。

ついでに

前回作ったフォーム回答トリガーと今回の機能を統合し、回答された2つのメールアドレスが一致していたらチェックボックスにチェックを入れて自動で送信する機能をつけると、以下のようになる。

function checkUpdate(){
    ...
        sendLine(row, message);
    var mail1 = mySheet.getRange(row, 5).getValue();
    var mail2 = mySheet.getRange(row, 6).getValue();
    if(mail1 == mail2){
        mySheet.getRange(row, 7).check();   // チェック
        sendMail(row);  // メール送信
    }
}

そして最終的にできた全体のコードは以下のとおりである。

function myFunction() {

}

function checkUpdate(){
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell(); //アクティブセルを取得
    var status = myCell.getValue();
    var col = myCell.getColumn();
    var row = myCell.getRow();
    var q1 = mySheet.getRange(row, 2).getValue();
    var q2 = mySheet.getRange(row, 3).getValue();
    var mail1 = mySheet.getRange(row, 5).getValue();
    var mail2 = mySheet.getRange(row, 6).getValue();
    var message = "回答を受け付けました\n" + q1 + "\n" + q2; 
    sendLine(row, message);
    if(mail1 == mail2){
        mySheet.getRange(row,7).check();
        sendMail(row);
    }
}

function sendLine(row, msg){
    var token = "xxxxxxxxxxxxxxxxxxxx";

    var options = { 
        "method": "post",
        "Content-Type" : "application/x-www-form-urlencoded",
        "payload": "message=" + msg,
        "headers":{"Authorization" : "Bearer " + token}
    };
    UrlFetchApp.fetch("https://notify-api.line.me/api/notify",options);
}

function showCheckboxInfo() {
    var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
    var myCell = mySheet.getActiveCell();
    var rule = myCell.getDataValidation();
    if (rule != null) {
        var criteria = rule.getCriteriaType();
        var status = myCell.getValue()
            if ( criteria == 'CHECKBOX' && status == true) {
                var row = myCell.getRow();
                var result = Browser.msgBox('チェックボックスが押されました。メールを送信しますか？', Browser.Buttons.OK_CANCEL);
                if(result == 'ok'){
                    sendMail(row);
                } else if (result == 'cancel'){
                    mySheet.getRange(row,11).uncheck();
                }
            }
    }
}

function sendMail(row) {
    var spreadsheet = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxx");
    var datasheet = spreadsheet.getSheetByName('answer');

    var name = datasheet.getRange(row, 4).getValue();
    var email = datasheet.getRange(row, 5).getValue();

    var subject = 'タイトル';
    var body = 'メール本文\nメール本文\nメール本文';

    MailApp.sendEmail(email, subject, body);
}

デモ

デモ1: チェックボックスをクリックしたらメールを送信

スプレッドシートの該当行を確認する。

スプレッドシート

チェックボックスをクリックするとポップアップが表示されるので、OKを押して メール送信

送信確認

このGASの所有者のアカウントの送信済みメールボックス

送信済みメール

受信側メールボックス

受信メール

デモ2: フォーム回答時点でメールアドレスが一致していたら自動でメールを送信

まずフォームに回答する。

フォームへの回答

スプレッドシートでは、２つのメールアドレスが一致したため自動でチェックボックスが入力され、メールが送信されている。

メールアドレス検証が実行され自動でチェックが入った状態

送信済みメール

受信メール

まとめ

スプレッドシート、GASともにGoogleのアカウントでログインする必要があるため、必然的にメールアドレスに紐づく形となる。従って、GASでメールを送信する機能を作るにしても、余計な認証を必要とせずにワンライナーで送信ができる。非常に便利である。

スプレッドシートを使えば様々なデータを複数人で共有できる上に無料枠の範囲内であればサーバなどのリソースも必要とせず、様々なサービスを手軽に作ることが可能だった。

ちなみにトリガーにはカレンダーや時刻も指定できるため、cronのような使い方をGUIで設定することも可能である。

GASはJavaScriptライクで使いやすいし、無料だし、インターネットに常に繋がっているし、マルチプラットフォームでどんなOSでも同じように使える。この手軽さを覚えてからは、某社の表計算ソフトを使うことはほとんどなくなって、解約してしまった。スプレッドシート、とてもおすすめです。

参考にしたサイト

Googleスプレッドシートのチェックボックスを便利に使う方法 | Creazy!