スプレッドシートからメール_宛先を選択(その4)別シートにあるメアドを取得[GAS]

前回はTo,Cc,Bccをプルダウンで選択しメール送信するGASを紹介しました。

前回まではプルダウンするシートにメアドが紐づいていましたが、

メアドを管理するシートはすでに別にある、という場合も多いと思います。
今回はそのような場合を想定したGoogle Apps Scriptを記します。

ひとつのスプレッドシートファイルにて
宛先をプルダウンで選択するシートは「list」、
メアドを管理しているシートは「addresslist」としています。

スクリプトはこちら。

function createMailData(doSend) {

  //スプレッドシート、および各シートを取得
  const ss           = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet    = ss.getSheetByName('list');
  const mailSheet    = ss.getSheetByName('mail');
  const addressSheet = ss.getSheetByName('addresslist');

  //mailSheetのセルB1-B2から件名、本文を取得
  const subject = mailSheet.getRange(1, 2).getValue();
  const body    = mailSheet.getRange(2, 2).getValue();

  //各シートの最終行を取得、メアドを格納する変数recipient,cc,bccを準備
  const listLastRow    = listSheet.getLastRow();
  const addressLastRow = addressSheet.getLastRow();
  let recipient = '';
  let cc = '';
  let bcc = '';

  //listSheetの列Aの値によってTo,Cc,Bcc振分け
  for (let i = 2; i <= listLastRow; i++) {
    const flag = listSheet.getRange(i, 1).getValue();

    if (flag === "notSend") continue;

    const name = listSheet.getRange(i, 2).getValue();

    for (let j = 2; j <= addressLastRow; j++) {
      const tempName = addressSheet.getRange(j, 1).getValue();
      if (tempName === name) {
        const address = addressSheet.getRange(j, 2).getValue();

        if (flag === "To") {
          recipient += address + ',';

        } else if (flag === "Cc") {
          cc += address + ',';

        } else if (flag === "Bcc") {
          bcc += address + ',';
          
        }
      }
    }
  }

  if (doSend) {
    GmailApp.sendEmail(recipient, subject, body, { cc: cc, bcc: bcc }); //送信  
  } else {
    GmailApp.createDraft(recipient, subject, body, { cc: cc, bcc: bcc }); //下書き   
  }
}

function sendMail() {

  //誤操作による誤送信防止のためにダイアログを実装
  const ui = SpreadsheetApp.getUi();
  const res = ui.alert("メール送信しますか？", ui.ButtonSet.OK_CANCEL);

  if (res === ui.Button.CANCEL) {
    return;
  }
  createMailData(true);
}

function createDraft() {

  createMailData(false);
}

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('スクリプト')
    .addItem('メール送信', 'sendMail')
    .addItem('下書き作成', 'createDraft')
    .addToUi();
}

シート「list」列Bにある氏名を検索キーとして、シート「addresslist」からメアドを探して返すという構造にしています。

---

※メアド管理シートにデータが多いとスクリプト実行時間が長くなってしまう可能性があります。
メアドデータを二次元配列で取得するパターンのスクリプトも記しておきます。

function createMailData(doSend) {

  //スプレッドシート、および各シートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = ss.getSheetByName('list');
  const mailSheet = ss.getSheetByName('mail');
  const addressSheet = ss.getSheetByName('addresslist');

  //mailSheetのセルB1-B2から件名、本文を取得
  const subject = mailSheet.getRange(1, 2).getValue();
  const body = mailSheet.getRange(2, 2).getValue();

  //listSheetの最終行を取得、メアドを格納する変数recipient,cc,bccを準備
  const listLastRow = listSheet.getLastRow();
  let recipient = '';
  let cc = '';
  let bcc = '';

  //メアドデータを二次元配列として取得
  const addressData = addressSheet.getDataRange().getValues();
  addressData.shift();

  //listSheetの列Aの値によってTo,Cc,Bcc振分け
  for (let i = 2; i <= listLastRow; i++) {
    const flag = listSheet.getRange(i, 1).getValue();

    if (flag === "notSend") continue;

    const name = listSheet.getRange(i, 2).getValue();

    for (let j in addressData) {

      if (addressData[j][0] === name) {
        const address = addressData[j][1];

        if (flag === "To") {
          recipient += address + ',';

        } else if (flag === "Cc") {
          cc += address + ',';

        } else if (flag === "Bcc") {
          bcc += address + ',';

        }
      }
    }
  }

  if (doSend) {
    GmailApp.sendEmail(recipient, subject, body, { cc: cc, bcc: bcc }); //送信  
  } else {
    GmailApp.createDraft(recipient, subject, body, { cc: cc, bcc: bcc }); //下書き   
  }
}

function sendMail() {

  //誤操作による誤送信防止のためにダイアログを実装
  const ui = SpreadsheetApp.getUi();
  const res = ui.alert("メール送信しますか？", ui.ButtonSet.OK_CANCEL);

  if (res === ui.Button.CANCEL) {
    return;
  }
  createMailData(true);
}

function createDraft() {

  createMailData(false);
}

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('スクリプト')
    .addItem('メール送信', 'sendMail')
    .addItem('下書き作成', 'createDraft')
    .addToUi();
}