※更新
- スクリプトを修正し、汎用性を高めました(2021/02/27)。
- コードにコメントを入れて読みやすくしました(2021/05/13)。
はじめに
Googleスプレッドシートで、宛先ごとの個別文面のメールを作成して一括送信できるしくみを、GoogleAppsScriptで作りました。この記事は、その導入手順と利用方法について解説するものです。
これは、次のようなしくみです。
- Googleスプレッドシートに「テンプレート」のシートを作成し、その各行にメール一括送信の案件（件名、本文）を定義します。
- メールの一括送信をする案件ごとに、宛先、送信内容の文字列の置き換えをするための変数を管理するシートをつくります。
- 「テンプレート」のシートにおいて、いずれか1つの行を選択（アクティブ状態）にしてから、メニューからスクリプトを実行します。
- スクリプトにより、指定された行の件名・本文をテンプレートとして利用しつつ、参照するシートに定義された宛先に対して、メールの件名・本文の文面を個別に作成しながら、メールを一括送信します。なお、送信されるメールの送信者はスクリプトを実行しているユーザ自身となり、そのユーザのGmailの「送信済みメール」に送信履歴が記録されます。
「メールを一括送信する」機能、さらには、「Googleスプレッドシートでメールの送信先を管理してGoogleAppsScriptでメールを送るいう方法」は、他にもいろいろな方が開発をされ、その知見を公開してくださっているので、目新しいテーマではありません。それでも、私が今回作ったものは、次に示すようなメリットを利用者にもたらす意味で、新規性があると考えています。
- 導入の際には、Googleスプレッドシートの画面からGoogleAppsScriptのスクリプトエディタを開く必要がありますが、この記事中のスクリプトをコピー＆貼り付けするだけで大丈夫なので、利用者がGoogleAppsScriptなどのプログラミングの知識を持たない場合でも、ほぼ問題なく導入ができます。
- メールの件名のテンプレート・メール本文のテンプレート・メールの送信先・メール文面への埋め込み内容などを、GoogleAppsScript側でハードコーディングせずに、すべてスプレッドシート上の要素として定義するものとしているので、スクリプトを書き換えるのではなく、スプレッドシート上の内容を書きるだけで、柔軟に運用することができます。
- スプレッドシートの「行」として一括送信するテンプレートを複数保持でき、また、スプレッドシートの「シート」によって宛先一覧を複数保持できます。これにより、一つのスプレッドシートでそれらのn対nの関係での組み合わせを保持しておくことができます。メール一括送信時には、テンプレートと送信先の組み合わせを任意に選んで一括送信を実行できるということになります。
- 動作に必要なデータのすべてGoogleスプレッドシート上で定義されているということは、そうしたデータの差分的なバージョン管理が行いやすくなり、他システムで作成したデータを連携させやすくなり、さらなる自動化への展望が開くということになります。
- 複数の担当者が当該Googleスプレッドシートを共有して、一括メール送信をする際に必要となるデータを協力して準備し、よく確認しながらメール送信を行うという業務フローを実現できます。メール一を一括送信する業務負担が特定の誰かに集中して業務のボトルネックにならないように、一括送信をするときによく確認をすることでミスを減らせるようになります。
この記事は、「Googleスプレッドシートで宛先ごとに個別文面のメールを作成して一括送信する」「Google Classroomへの各学生の課題提出内容を、Googleスプレッドシートに抽出する」「履修者名簿・提出物URL一覧をもとに、履修者間の相互評価用Googleフォームと評価集計用Googleスプレッドシートを自動生成する」という、「遠隔授業支援ツール」のシリーズのひとつとして書きました。ほかの記事も併せてお読みいただければと思います。
導入
(1) 空のGoogleスプレッドシートを作成する
- Googleアカウントで認証済みのWebブラウザで、Googleスプレッドシートを開き、画面右下隅の＋記号のアイコンから、「新しいスプレッドシートを作成」を実行して、新しいスプレッドシートを作成します。ここでは、ファイル名を「メール一括送信」としました。
(2) スクリプトエディタを開いてスクリプトファイルを保存する
-
Googleスプレッドシートの画面上部のメニューバーの「ツール」から「スクリプトエディタ」を開きます。
-
「無題のプロジェクト」という画面が開くので、「コード.gs」の内容として、既存の
function myFunction(){ }となっているものを削除します。
-
「コード.gs」の内容として、以下に示すコードを貼り付けます。
const DEBUG = false;
const SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();
const TEMPLATE_SHEET_NAME = 'テンプレート';
const TEMPLATE_SHEET = SPREADSHEET.getSheetByName(TEMPLATE_SHEET_NAME);
/**
* 文字列がメールアドレスの形式であるかを調べる
*/
function isValidMailAddress(source) {
return source && source.length > 0 && source.indexOf("@") > 0;
}
/**
* 文字列を置き換える
* 一般的には、return soruce.replace(from, to); でも良いのだが、置換元が$を含む文字列であるので誤動作を防ぐためにsplit-joinで置換する
*/
function replace(source, from, to) {
return source.split(`\${${from}}`).join(to);
}
/**
* メール件名・本文を、指定された変数ラベル・変数値でカスタマイズする
*/
function customizeMailContents(mailSubject, mailBody, labels, values) {
for (let index = 0; index < labels.length; index++) {
const label = labels[index];
const value = values[index];
mailSubject = replace(mailSubject, label, value);
mailBody = replace(mailBody, label, value);
}
return { mailSubject, mailBody };
}
/**
* 「テンプレート」シートとその行を指定して、処理のコンテキストを表すオブジェクトを返す
*/
function getContext(projectRow){
// テンプレートシートのプロジェクト定義行の1列目から、1行分x3列分のセル値を抽出し、プロジェクト名、メール件名、メール本文として代入
const [[projectName, mailSubject, mailBody]] = TEMPLATE_SHEET.getRange(projectRow, 1, 1, 3).getValues();
// プロジェクト名をもとにプロジェクトのシートをゲット
const projectSheet = SPREADSHEET.getSheetByName(projectName);
if (!projectSheet) { // プロジェクトのシートがみつからない場合
throw new Error(`指定されたシートがみつかりません: ${projectName}`);
}
const values = projectSheet.getDataRange().getValues(); // プロジェクトのシートのうち、値が存在するセル範囲の値の配列をゲット
return { mailSubject, mailBody, values };
}
/**
* 「テンプレート」シートで選択されたプロジェクトの定義行を配列で返す
*/
function getProjectRowsWithSelectingRowsOfTemplateSheet() {
const activeRange = TEMPLATE_SHEET.getActiveRange();
const projectRows = [];
if (activeRange != null) {// 選択された行が存在する場合
for (let index = 0; index < activeRange.getNumRows(); index++) { // 選択範囲を1行ずつ順に処理
const row = activeRange.getRow() + index; // 処理する行の番号
if (row >= 2) { // 処理する行の番号が2以上の場合は処理を実施(※テンプレートの1行目のときはヘッダなので処理しない)
projectRows.push(row);
}
}
return projectRows;
} else { // 選択された行が存在しない場合
throw new Error('「テンプレート」シートを選択し、送信したい案件の行をいずれか1行だけ選択状態にして実行してください。');
}
}
/**
* プロジェクト名をもとにプロジェクトの定義行を配列で返す（「テンプレート」シート内をプロジェクト名で検索して該当の行について処理を行う）
*/
function getProjectRowsWithSelectingProjectSheet(projectName) {
const ranges = TEMPLATE_SHEET.createTextFinder(projectName).findAll(); //「テンプレート」シートにおいて、プロジェクト名をテキスト検索
const projectRows = [];
for (let index = 0; index < ranges.length; index++) { // テキスト検索結果を順番に処理
const range = ranges[index];
if (range.getColumn() === 1) { // 「テンプレート」シートにおいて、プロジェクト名をテキスト検索して発見されたセルが、1列目(プロジェクト名)である場合
projectRows.push(range.getRow());
}
}
if (projectRows.length > 0) {
return projectRows;
} else {
throw new Error(`「テンプレート」シートで、選択状態のシートと同じ名前で案件の行を定義してから実行してください: ${projectName}`);
}
}
/**
* プロジェクトの定義行を配列で返す
*/
function getProjectRows(){
const activeSheetName = SPREADSHEET.getActiveSheet().getSheetName();
const projectRows = (activeSheetName === TEMPLATE_SHEET_NAME)?
getProjectRowsWithSelectingRowsOfTemplateSheet() // 現在選択中のシート名が「テンプレート」の場合
:
getProjectRowsWithSelectingProjectSheet(activeSheetName);// 現在選択中のシート名が「テンプレート」以外の場合
return projectRows;
}
/**
* カスタマイズされたメールを送る。定数DEBUGがtrueの場合は、実際にはメールを送付せずに、送付内容を実行ログに表示する。
*/
function sendMail(mailTo, mailSubjectSource, mailBodySource, labels, values) {
const {mailSubject, mailBody} = customizeMailContents(mailSubjectSource, mailBodySource, labels, values);
if (DEBUG) {
console.log({ mailTo, mailSubject, mailBody });
}else{
MailApp.sendEmail(mailTo, mailSubject, mailBody);
}
}
/**
* カスタマイズされたメール一式を送る
*/
function sendMailSet(projectRow) {
const { mailSubject, mailBody, values } = getContext(projectRow);
const labels = values[0]; // 値の配列の1行目相当分には変数名が書かれている
const emailColumnIndex = labels.indexOf("email");
for (let rowIndex = 1; rowIndex < values.length; rowIndex++) { // 行配列のインデクス0(1行目に相当)はヘッダなので、インデクス1(2行目に相当)から処理
const mailTo = values[rowIndex][emailColumnIndex]; // 値の配列から、処理対象となる行における「送付先メールアドレス」の文字列をゲット
if (isValidMailAddress(mailTo)) { // この「送付先メールアドレス」の値は、メールアドレスとして正しい体裁か？
sendMail(mailTo, mailSubject, mailBody, labels, values[rowIndex]);
}
}
}
/**
* テンプレートをもとにメール一式を送る
*/
function sendMailSetWithTemplate() {
try {
const projectRows = getProjectRows();
projectRows.forEach(function(projectRow){
sendMailSet(projectRow);
});
} catch (error) {
Browser.msgBox(error);
}
}
/**
* スプレッドシートをブラウザで開いたときの処理
*/
function onOpen() {
const menu = [{ name: "テンプレートでメールを送信", functionName: "sendMailSetWithTemplate" }];
SpreadsheetApp.getActiveSpreadsheet().addMenu("メール一括送信", menu); // カスタムメニュー
}
- ファイルメニューから「保存」を選び、このファイルを保存をしてください。その際、プロジェクト名を指定するように促されますので、適当に名前をつけてください。
(3) スクリプトを再読み込みして、メニュー表示をカスタマイズする
前項でスクリプトを保存をしたら、ここで、もともとのスプレッドシートの画面において、Webブラウザの更新ボタンを押すなどして、このスプレッドシートを開き直してください。
内部的に、このスプレッドシートにバインドされているスクリプトのonOpen関数が呼び出されることにより、スプレッドシートに 「メール一括送信」というメニューが増設されるはずです。 こうして、メニューから「テンプレートでメール送信」という項目が選べるようになっていることが確認できたら、次に進みましょう。
(4) 「テンプレート」のシートを準備する
- 作成したスプレッドシートの既存のシートの名前「シート1」を、「テンプレート」に変更します。
２. この「テンプレート」シートの、1行目のA〜C列をヘッダとして用いることとし、次のような内容を書きます。
「テンプレート」シート(ヘッダ部分）
| A | B | C |
|---|---|---|
| シート名 | 件名 | 本文 |
ここまでで準備は完了です。
利用
(1) 一括送信メールの件名と本文のテンプレートを1件1行で作成する
前項で作成した 「テンプレート」のシートが、メールを一括送信する際の「テンプレート」をまとめた「テンプレート集」としての役割を果たす内容になります。
では、これから一括送信をしたいメールの案件1つごとに、このシート内の1行分を用いて、テンプレートを定義していきましょう。
- Aカラムには、「シート名」として、これから作成する送付先一覧のシート名にあたる名前を書きます。1行目のこのセルで、参照するシートの名前を表記することで、送付先一覧のシートへの関連付けができます。
- Bカラムには、「件名」として、送付するメールの件名を書きます。
- Cカラムには、「本文」として、送付するメールの本文を書きます。なお、メール本文は改行を含めた内容とすることができます。Googleスプレッドシートでセル内に改行を追加するには、⌘+enter（Mac）ないし Ctrl+Enter（Windows）を押しましょう。
ちなみに、「テンプレート」シートのBカラム・Cカラムにおいて定義される「件名」「本文」の中で、${変数名} という表記をした部分については、メール送信時の前処理において、文字列の置き換えがなされます。「件名」「本文」中で、変数は何種類でも、それぞれ何回でも使用できます。
「テンプレート」シートの例〔ヘッダ部分+ボディ部分）
| A | B | C |
|---|---|---|
| シート名 | 件名 | 本文 |
| 案件α | 件名1 | .... ${変数A} .... ${変数B} .... ${変数C} .... ${変数D} |
| 案件β | 件名2 | ....... ${変数X} .. ${変数Y} ....... ${変数Z} .. |
(2) 一括送信メールの宛先と置き換え文字列を定義する
-
このスプレッドシートに新しいシートを追加します。
- スプレッドシート画面左端最下部の「＋」ボタンを押して、シートを追加します。
- 追加したシートの名前を変更します。その名前には、先に、「テンプレート」シートのAカラム「シート名」の、ボディ部分での値として記入した名前を用いてください。下の例では「面談日時通知」というシート名をつけています。
-
このシートの最初の行をヘッダ行として扱うものとします
- ヘッダ行のいずれかのカラムのセルに「email」と書いてください。こうして「email」と記入したカラムが、このシートを用いて個別的にカスタマイズしたメールを送るときの、送信先アドレスのカラムになります。
- このシートのヘッダ行の、「email」と記入したセル以外のセルについては、文字列置き換えに利用する「変数名」を定義します。
-
このシートのボディ部分において、送信先の数だけ行を増やし、ひとつの送信先に対応する変数の値のセットを記入していきます。
「送信先」シートの例
「案件α」シート
| A | B | C | D | E | .... |
|---|---|---|---|---|---|
| 変数名A | 変数名B | 変数名C | 変数名D | .... | |
| 送信先1 | 送信先1の変数Aの値 | 送信先1の変数Bの値 | 送信先1の変数Cの値 | 送信先1の変数Dの値 | .... |
| 送信先2 | 送信先2の変数Aの値 | 送信先2の変数Bの値 | 送信先2の変数Cの値 | 送信先2の変数Dの値 | .... |
| 送信先3 | 送信先3の変数Aの値 | 送信先3の変数Bの値 | 送信先3の変数Cの値 | 送信先3の変数Dの値 | .... |
「案件β」シート
| A | B | C | D | .... |
|:------:|:---:|:----:|:----:|:-----:|:-----:|
| email | 変数名X | 変数名Y | 変数名Z |.... |
| 送信先1 | 送信先1の変数Xの値 | 送信先1の変数Yの値 | 送信先1の変数Zの値 | .... |
| 送信先2 | 送信先2の変数Xの値 | 送信先2の変数Yの値 | 送信先2の変数Zの値 | .... |
| 送信先3 | 送信先3の変数Xの値 | 送信先3の変数Yの値 | 送信先3の変数Zの値 | .... |
案件「面談日時通知」
(3) テンプレートを選択しメール一括送信を実行する
-
スプレッドシートにおいて、「テンプレート」のシートで定義されたテンプレート集の中で、あなたが今回メール一括送信に利用したい「テンプレート」が定義されている行を、マウスで選んで選択状態にしてください。
-
スプレッドシート画面上部の「メール一括送信」というメニューから、「テンプレートでメール送信」を実行してください。メールの一括送信が実行されます。
(4) 実行を許可する（初回のみ）
-
このスクリプトの初回実行時には
次のような「承認が必要」というダイアログボックスが表示されます。
「続行」を押してください。
-
もしあなたが、G Suiteを契約している組織のGoogleアカウントではなく、
無償のGoogleアカウントを用いて、このスクリプトを実行しようとしている場合には、
次のような警告が表示されることがあります。
警告画面右下の「詳細」をクリックし、さらに「安全ではないページへ移動」をクリックしてください。
-
下のような画面により、アクセスのリクエストに対する許可を発行することができます。「許可」を押してください。
-
このようにして、いったん「許可」をしておけば、スプレッドシート画面上部の「メール一括送信」というメニューから、「テンプレートでメール送信」を実行したときに、警告画面が表示されることなしに、スクリプトが動作するようになります。
どうしてこれを作ったのか
新型コロナウイルスの感染の広がりに対し、教育現場では、遠隔授業手法をどのように実施するかの取り組みが進んできております。そんな中で、同僚の先生から、「学生1人ひとりに、テストの得点、発表へのコメントなどを、個別的にフィードバックするには、どうしたらいい？一通一通手作業でメールの本文をコピペ・部分的に書き換えをしながら送っているけど、面倒なんだよね…」という相談をされました。教室で直接対面で学生たちを指導できない状況で、遠隔授業を一方通行にしないためには、それぞれの学生に対して、個別のフィードバックを返してやることが、とても重要なものになってきています。なるべ手間を減らし、そうして浮いた分で、学生たちに個別的な対応をできるようにして、時間を上手に使っていきたいところです。
たとえばこんな使い方
その1: 学生の提出した課題への個別的なフィードバック
遠隔授業においては、学生それぞれが提出した課題への個別的なフィードバックをする作業に、教員として、どれだけ心を配ることができるか、あるいは、その品質を維持したままでどれだけ手間を減らせるかが、鍵となります。要は、個別的なフィードバックをするのは、とても手間がかかるのだということです。
特定の学生への指導内容をクラスの他の皆学生にもオープンにすることによって、「人のふりみて我がふり直せ」的なやりかたで、学びのコミュニティ全体を指導する方法があります。そうしたフィードバックの内容が「褒める」内容だけであれば、あまり問題にはなりません。でも、教員側としては、ときには厳しいことを言わなければなりません。そうして教員側では冷静に「改善を求めた」つもりが、当の学生側としてはクラスの他の学生の前で「叱られた」とショックを受けてしまうことがあります。微妙な内容を含むフィードバックは、クラスの他の学生に対してオープンな形で伝えるのではなく、個別的にフィードバックを与えるべきです。これが、とても手間がかかるのです。
というわけで、こうしたフィードバックを個別的に行うようにしたい、学生たちへの個別的な指導内容を類型化し、同じ類型の中での指導のためのフィードバック文面をコピペしながら作文できるようにしたい場合に、本記事のしくみが役に立つものと考えています。
その2: 面談希望日時アンケートへの応答
あるいは、フィードバック内容を、フィードバック相手ごとに調整しながら作成しなければならない場合にも、本記事のしくみは有用です。たとえば次のような用途です。
- フォーム(Googleフォームなどで別途用意)で、学生たちからビデオ会議システムでの面談希望日時を連絡してもらう
- 教員は、学生からの回答をもとに面談希望日時を調整する
- 教員は、スプレッドシートで「面談希望日時送信」シートを作成し、「学生のメールアドレス、学生氏名、面談決定日時、ビデオ会議システムのミーティングのURL」を書き込む
- 教員は、このスプレッドシートから、クリック一発で、決定した内容を学生たちにメールで一括送信する
- 教員は、このスプレッドシートに「面談希望日時送信（済）」のシートを用意し、送信のシートから、送信（済）のシートへと、内容を、切り取り貼り付けする
- 学生からの面談希望日時の連絡が追加で行われる場合には、2に戻る
７. 教員は、面談の当日になったら、この表の中の、ビデオ会議システムのURLをクリックして、学生と面談する
「相手によってメール文面を変える必要があるが、決して相手を間違えてはならない」というような場合に、この業務を複数人の担当者で、一覧表としてまとめられたメール送信予定の内容を、ダブルチェック・トリプルチェックしてからメールを送ることができれば、単純ミスを減らすことができます。
まとめ
というわけで、GoogleSpreadsheetを用いて、宛先ごとに個別文面として構成したメールを作成し、それらを一括送信する方法について解説しました。
スクリプトがもう少し複雑になってきたらclapを用いてみたいと思います。
こうした仕組みが、学校の先生方をはじめとして、みなさまのお役に立つようであれば幸いです。