特定のグループのメンバー全員に連絡をしたいことがあったときに、いちいち名簿を見て誰がメンバーだったかを確認するのは面倒です。さらに、複数のグループや個人が入り乱れて使用する部屋の使用者全員に連絡したいときは、より面倒が増します。そこで、Googleフォームから宛先のグループを選択すれば名簿からメールアドレスを取得して、該当者にメールを送ってくれるシステムをつくってみました。

仕事の効率化のためにプログラミングを勉強しはじめたというレベルなので、動けばよいくらいの適当なコードです。

類似のかんたんリマインダーも参照してください。

Googleフォームでの準備

以下のようなフォームを作成する。送信者のメールアドレス、宛先、メールのタイトル、メールの本文という順番を前提として後述のプログラムを作成した。

、

Googleスプレッドシートでの準備

フォームの回答が保存されるスプレッドシートを用意する。フォームの編集画面から、「回答」タブを開くとスプレッドシートへのリンクが出てくる。

フォームの回答が保存されているスプレッドシートに、グループの名簿(groupシート)と部屋使用者の名簿(roomシート)を作成する。シートの構成は、1行目にグループ名や部屋名、2行目以降にグループ名か個人のメールアドレス。1列目に説明、2列目以降に中身。フォームからの回答が記録されるシートの名前はanswerに変更する。このあたりはコードの対応が取れていればいいのでお好みで。

スプレッドシートのメニューからスクリプトエディタを開き、以下のコードをコピペ。トリガーにフォームの値送信時を設定する。コードの中のスプレッドシートのidを変更する。

function sendmail() {
  var file = SpreadsheetApp.openById("スプレッドシートのid");
  var answer_sheet = file.getSheetByName('answer');
  var group_sheet = file.getSheetByName('group');
  var room_sheet = file.getSheetByName('room');

  //フォームに入力された値を読み取る
  var rowtoread=answer_sheet.getDataRange().getLastRow(); //フォームの回答が記入された行を求める
  var answer = answer_sheet.getRange(rowtoread,2,1,4).getValues(); //フォームの回答を取得

  var sendfrom = answer[0][0];
  var sendto = answer[0][1];
  var title = answer[0][2];
  var message = answer[0][3];

  //グループのアドレスリストを入手する
  var groupnum = group_sheet.getDataRange().getLastColumn()-1; //グループ数を取得
  var grouplist = group_sheet.getRange(1,2,1,groupnum).getValues(); //グループ名のリストを取得

  var group_lastrow = group_sheet.getDataRange().getLastRow();

  //宛先がGroupの場合。宛先リストにメールアドレスを追加する。
  var sendtolist = [];
  appendfromgroup(sendto, sendtolist, group_sheet, groupnum, group_lastrow);

  var roomnum = room_sheet.getDataRange().getLastColumn()-1; //room数を取得
  var roomlist = room_sheet.getRange(1,2,1,roomnum).getValues(); //room名のリストを取得

  var room_lastrow = room_sheet.getDataRange().getLastRow();

  //roomシートのroom構成員を宛先にいれる。
  for(var k = 2; k <= roomnum + 1; k++) {
    if(sendto === room_sheet.getRange(1,k).getValue()) {

      for(var l = 2; l <= room_lastrow; l++) {
        //roomの構成員がgroupの場合
        var temp = room_sheet.getRange(l,k).getValue();
        if (grouplist[0].indexOf(temp) > -1){
        appendfromgroup(temp, sendtolist, group_sheet, groupnum, group_lastrow);
        }
        //roomの構成員がgroupでない場合、メールアドレスが入力されていると判定
        else{
          sendtolist.push(temp);
        }
        }
      }
    }

  //重複をのぞく
  var sendtolist_true = sendtolist.filter(function (x, i, self) {
            return self.indexOf(x) === i;
        });

  //メール本文の修正
  message = "宛先: " + sendto + "\n送信者: " + sendfrom + "\n\n" + message;

  //メール送信
  MailApp.sendEmail(
      sendtolist_true, //toアドレス
      title,  //メールタイトル
      message,　//本文
      {
        from: sendfrom,　//fromアドレス
        cc: sendfrom, //送信依頼者にccで送る
        name: "同報メール送信フォーム"　//差出人
      }
    );
}

function appendfromgroup(sendto, sendtolist, group_sheet, groupnum, group_lastrow){
  //宛先リストに該当するメールアドレスを追加する
  i = 0;
  for(var i = 2; i <= groupnum + 1; i++) {
    if(sendto === group_sheet.getRange(1,i).getValue()) {
      for(var j = 2; j <= group_lastrow; j++) {
        sendtolist.push(group_sheet.getRange(j,i).getValue());
      }
    }
  }
  return sendtolist;
}