この度、わけあって300名ほどの人たちに個別にIDとパスワードを送らなければいけなくなりました。会員用サイトのログインIDとパスワードです。一人ひとりユニークに割り振られています。さすがに一人ずつメールを送るには効率が悪すぎますし、ヒューマンエラーも起こりそうです。
より効率的にかつ間違いなくメールでIDとパスワードを知らせる方法を模索していました。良く調べてみたら、実はGmailとスプレッドシートとドキュメントの組み合わせで本文に任意の情報を差し込みながら、一人ひとりにメールが送信できるという事がわかりました。
その時の備忘録になります。
お問い合わせいただいた内容の返答を最下部に追記しました。
「$&や$$が特別な変数として置換されてしまうのを防ぎたい」
Gmailで差し込みメールを送る方法
１：スプレッドシートを作成
まずは、スプレッドシートを新規に作成します。スプレッドシートの名前は特にプログラム上は関係ないのでわかりやすい名前で問題ありません。
今回はIDとパスワードをメールで連絡したいので、
- name
- id
- password
の４項目（列）を作成して、テスト用の値を入力しました。
２：本文用のドキュメントを作成
続けて、ドキュメントを新規に作成します。こちらも名前は何でも大丈夫です。
ここで作成したドキュメントがメールの本分となります。今回の内容はこんな感じでとてもシンプルです。
{}でくくった個所が自動的に置換されて送信されるシステムです。
自動で置換される項目は次の3つです。
{name}
{id}
{password}
３：スクリプトを作成
そしてスクリプトを作成します。スクリプトの内容についてはコードにコメントが入っていますので、確認してください。状況に応じて皆さんの環境に合わせて使ってください。（この記事の一番下にコードを載せておきます）
赤の四角の箇所には、ドキュメントのIDが入ります。ドキュメントのIDはドキュメントのアドレスバーに表示されています。
「docs.google.com/document/d/ここがIDです/edit」となっています。
ここまで出来たら、あとは実行ボタンを押せば送信できます。
初回実行時には認証が入りますが、そのまま許可して下さい。
メールを送信するアカウントで認証してください。
許可をしてください。
テストで届いたメールがこちら
ちゃんと、ドキュメント通りになっていて、カッコ{}の箇所も置換されています。
誰でも使える仕組みにする
今回のもう一つの目的は、私以外の社員の方々（プログラムなどがまったくわからない方々）が使えるようにする事です。誰でも使えるようにしておかないと、毎回自分がやらないといけなくなりますので。
なので、毎回「スクリプトを開いて実行ボタンを押す」というのは、あまりよろしくありません。わかりにくいだけじゃなくて、コード画面を見ることを嫌がる（わかりにくいと感じる）方もいるでしょうし、もしも間違えてコードをいじってしまったりしたら色々と面倒です。
誰でも実行できる仕組みにする方法として2種類あります。
- VBA(Excel)のようにシート上にボタンを配置する方法
- スクリプトを実行するメニューを追加する方法
一つずつ確認してみます。
EXCELのようにボタンを配置する方法
まずは、EXCELのように図形を配置してボタンにする方法から。
メニューの挿入→図形から適当な図形を作成して挿入します。
とりあえず今回はこんな赤丸の実行ボタンを配置してみました。
図形を右クリックすると3つの点が表示されるので、その点の箇所をクリックします。
するとメニューがでてきますので、スクリプトを割り当てを選びます。
その後、どのスクリプトを割り当てますか？と聞かれるので、スクリプト名を記入します。
スクリプト名はコード内の先頭の「function myFunction() {」の部分になりますので、今回であれば「myFunction」になります。
実行ボタンを押すと、スクリプトが無事に実行されました。
スクリプト実行メニューを追加する方法
次に、メニューに追加する方法も確認します。完成形はこんな感じで、一番右（ヘルプの隣）に新しくメニューを追加します。
メニューに追加する方法は、先ほどのスクリプトの先頭に下記のコードを追記します。
以上です。
どっちにしようかと迷ったのですが、実行ボタンですと間違って削除されてしまいそうな気もしたので、今回はメニューに追加する方法を選択しました。
今回使用したコード
最後に今回使用したスクリプトのコードを記載しておきます。よろしければ参考までに下記よりどうぞ。
// メニューに追加
function onOpen() {
var ui = SpreadsheetApp.getUi('メール送信');
menu.addItem('一斉メールを送信します', 'myFunction');
menu.addToUi();
}
function myFunction() {
/* スプレッドシートの取得 */
var mySheet=SpreadsheetApp.getActiveSheet(); //アクティブシートを取得
var rowSheet=mySheet.getDataRange().getLastRow(); //シートの使用範囲のうち最終行を取得
/* ドキュメントの取得 */
var docTest=DocumentApp.openById("17***6X*****o1P****HY5o-****_XBottH*****v2mgQ"); //ドキュメントをIDで取得
var strDoc=docTest.getBody().getText(); //ドキュメントの内容を取得
/* シートの全ての行について差し込み*/
for(var i=2;i<=rowSheet;i++){
var strEmail=mySheet.getRange(i,1).getValue(); //email
var strName=mySheet.getRange(i,2).getValue(); //name
var strId=mySheet.getRange(i,3).getValue(); //id
var strPass=mySheet.getRange(i,4).getValue(); //password
/*取得した値を置換*/
var strBody=strDoc.replace(/{id}/g,strId).replace(/{password}/g,strPass).replace(/{name}/g,strName);//取得した値を置換
/* メール表題、fromアドレス、差出人名を準備 */
var strSubject = strName +"さんのIDとパスワード"; //メールタイトル
var strFrom="****@gmail.com"; //Fromアドレス
var strSender="〇〇〇〇"; //差出人
/* メールを送信 */
GmailApp.sendEmail(
strEmail, //toアドレス
strSubject, //メールタイトル
strBody, //本文
{
from: strFrom, //fromアドレス
name: strSender //差出人
}
);
}
}$&や$$が特別な変数として置換されてしまうのを防ぎたい
先日、この記事をご覧になった方から、ご質問をいただきました。その内容は簡単にまとめると以下の通りです。
パスワードに「$&」に含まれていると、思うように動作しない。対応できないか。
ということで色々と調べてみましたが、「$$」や「$&」という文字列は特別な変数として扱われてしまい、単純にパスワードの一部（文字列）として出力できませんでしたので、代替え手段として「$$」が「$」に変換されるという特性を逆に利用する方法を考えました。
対応は以下の通りです。
- パスワードの文字列に$が入っていなければ、そのまま置換。
- 文字列に$が入っていたら、$の前後で文字列を分ける。分けた値の間に「$$」を加えて再度結合して出力する。
例えば・・・？
パスワードが「pass$&word&」という文字列だったとすると、取得した「pass$&word&」を一旦「pass」と「&word&」に分けます。
そして、結合するときに$の箇所を$$にして結合しますので、一時的に「pass$$&word&」となりますが、$$は$に変換されるので、最終的には「pass$&word&」と出力されます。
/*パスワードに$があっても変数として置換されてしまうのを防ぐ*/
if(strPass.match('$')){ //パスワードに$が入っていたら
var result = strPass.split('$') //$の前後で分ける
var strBody=strDoc.replace(/{id}/g,strId).replace(/{password}/g,result.join('$$')).replace(/{name}/g,strName).replace(/{email}/g,strEmail);//パスワードに$$を入れて配列を結合し取得した値を置換
}else{ //$がなかったらそのまま置換
var strBody=strDoc.replace(/{id}/g,strId).replace(/{password}/g,strPass).replace(/{name}/g,strName).replace(/{email}/g,strEmail);//そのまま取得した値を置換
}以上となります。
お問い合わせ、ありがとうございました。