前提
シート名「シート1」が以下のような設定になっているものとします。
【変更前のシート(A1セル〜C3セル)】
/ | A | B | C |
---|---|---|---|
1 | 名前 | 出欠 | コメント |
2 | aha | 出席 | 行きます |
3 | hoge |
上記3行目の「出欠」「コメント」欄を修正し、その内容をメールで送信するものとします。
【変更後のシート(A1セル〜C3セル)】
/ | A | B | C |
---|---|---|---|
1 | 名前 | 出欠 | コメント |
2 | aha | 出席 | 行きます |
3 | hoge | 欠席 | 行きません |
結論
3つの関数を用意します。1と2は一つにまとめても構いません。
1. メール送信するための関数
2. 対象行の情報を取得し、関数1を実行して良いか確認する関数
3. 関数2をスプレッドシートのメニューに表示する= onOpen関数
実際の動作としては「3が2を呼び出し、2が1を呼び出す」という構造になっています。
動作確認スクリプト
1. カーソルのある行の情報を取得しメール送信するための関数
function sendMail_(rowContents){
var recipient = "", subject = "", body = "", options = {};
//宛先指定
recipient = "hogehoge@example.com";
//件名作成
subject = "出欠ステータスを変更しました(" + rowContents[0] + "さん)" ;
//HTMLメッセージ作成(この辺りは「005-動的に変化するHTMLメールを作成したい」をご覧下さい
var htmlBodyTemp = HtmlService.createTemplateFromFile('mail');
htmlBodyTemp.rowContents = rowContents;
options.htmlBody = htmlBodyTemp.evaluate().getContent();
GmailApp.sendEmail(recipient, subject, body, options);
Browser.msgBox("メール送信が完了しました");
}
メール本文に使うHTMLテンプレート
<div>
(本メールはシステムにより自動送信されています)<br>
<?= rowContents[0]?>さんが<?= rowContents[1]?>を表明しました。<br>
<br>
<?= rowContents[0]?>さんからのコメント:「<?= rowContents[2]?>」
</div>
2. 対象行の情報を取得し、関数1を実行して良いか確認する関数
//関数2(実行された際にカーソルが置いてある行をメール送信して良いか確認する)
function checkRow(){
//定数(今回対象となるシートで「利用する一番右の」列数を(A列=1とカウントして)指定)
var MAXCOL = 3;
// 対象行のデータを配列変数に格納します
//現在開いているスプレッドシートファイル情報を取得
var activeSS = SpreadsheetApp.getActiveSpreadsheet();
//変数activeSSの中で、現在開いているシート情報を取得
var activeSheet = activeSS.getActiveSheet();
//変数activeSheetの中で、カーソルが置かれているセル情報を取得
var activeCell = activeSheet.getActiveCell();
//変数activeCellに入れられたセル情報の行番号を取得
var row = activeCell.getRow();
//row行の「A列からmaxColで指定した列(今回はC列)」の情報を取得
var rowContents = activeSheet.getRange(row,1,1, MAXCOL).getValues()[0];
//【参考】取得した情報はfor構文とLogger.logメソッドで確認できます
for(var i = 0; i < rowContents.length; i++)
Logger.log("rowContents[%s] is \n%s",i,rowContents[i]);
//カーソルが対象行以外である場合はメール送信関数を実行するとエラーになるため、事前にチェックし、問題があれば処理を終了します
if(row < 2 || 3 < row){
Browser.msgBox("カーソルがメール送信対象外の行に置かれています。処理を終了します。");
return 0;
}
//メールを送信して良いか確認し、結果を取得します
var yourSelections = Browser.msgBox(row + "行目のステータス変更通知メールを送信します、よろしいですか?", Browser.Buttons.OK_CANCEL);
//確認の結果がcancelだった場合は処理を終了します
if(yourSelections == "cancel"){
Browser.msgBox("メール送信キャンセルを受け付けました。処理を終了します。");
return 0;
}
//メール送信巻数
sendMail_(rowContents);
}
3. 関数2をスプレッドシートのメニューに表示する= onOpen関数
//関数3(スプレッドシートが開かれた時点でメニューに「関数2の実行ボタン」を追加する)
function onOpen(e){
var arr = [
{name: "カーソルの置かれた行についてメール通知する", functionName: "checkRow"}
];
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
spreadsheet.addMenu("ステータス変更", arr);
}
解説
「メールテンプレートに従い、指定した行の情報をメール送信する」というのが目標ですが、予期せぬ情報が入ってしまうとエラーになってしまうため事前にチェックをしています。
今回は関数rowCheck()で対象行を確認したり1(1行目=見出し行や4行目以降=データない行の場合処理を終了する)、実行者の意思を確認したり2といった部分がそれに当たります。
セル情報は、複数セルを指定するためにgetRange(Integer,Integer,Integer,Integer)
メソッドを利用します。
第1・第2引数で「始まりのセル位置3」を、第3・第4引数で「始まりのセルから行・列の移動距離」を指定しましょう。
今回は「開始セルの”行数”」が変数となり、それ以外(取得対象はA列から始まること、対象1行分のみ取得すること、取得列は開始列から3つ目までとなること)は定数としていますが、やりたいことに応じてそれぞれを変数とすればより柔軟な対応が可能です。
なお今回はHTMLメール作成にあたり、テンプレート文の中に配列変数rowContents
の内容を数値(=今回の場合は列番号)で指定しています。
元シートの列を操作(挿入や削除)する場合はこの辺りも忘れずに変更しましょう4。
リリース前の注意
checkRow()
を実行するためには、メニュー内「ステータス変更」をクリックした 本人がスクリプト実行を承認する 必要があります。
複数人で作業する場合は初回クリック時に実行承認確認ポップアップが出ることを周知しておきましょう。
なお送信元はスクリプト実行者になるのでそちらも忘れずに(嫌な場合&G suiteユーザーなら変数options.noReply
をtrueにすることでメールアカウントを隠すことができます)。
つぶやき
シートの更新タイミングが頻繁でない場合はトリガーに「値の変更」や「編集時」を指定してcheckRow()
を呼び出しても良いです5。
その場合はシートのオーナー権限で実行される…ような気がします。オーナーアカウントで事前にスクリプトの実行承認をしておく必要はありますが。