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

前回はTo,Cc,Bccをプルダウンで選択し、別シートにあるメアドを参照してメール送信するGASを紹介しました。

実際には、すでに別のスプレッドシートファイルでメアドを管理していることが多いかと思います。
Google Workspaceではスプレッドシートなどのファイルに一意のIDが振られており、ファイルパス（ファイルの置き場所）を気にしなくてもファイルの中身を参照できるのが強力なメリットです。

今回は別スプレッドシートにあるメアドデータを参照してメール送信するGoogle Apps Scriptを記します。
（大変ややこしくて恐縮ですが、前回の記事では「別シート」、すなわち同一のスプレッドシートファイルにある別のシートを参照する記事でした。今回は別のスプレッドシートファイルを参照するものです。）

プルダウンで宛先を選択＆メール本文が格納されているスプレッドシートファイル「SS_mail」、それとは別にメアドを管理しているスプレッドシート「SS_addresslist」があるとします。

スクリプトはこちら。（スクリプトは「SS_mail」に記述し実行します）

function createMailData(doSend) {

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

  const addressSs    = SpreadsheetApp.openById('***SS_ID***');
  const addressSheet = addressSs.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();
}

４行目にあるopenByIdメソッドによって、他のスプレッドシートファイルを取得することが出来ます。
（***SS_ID***の部分は実際の「SS_addresslist」のスプレッドシートIDに置き換えてください。）

const addressSs = SpreadsheetApp.openById('***SS_ID***');

「スプレッドシートID」は、URLの最後のスラッシュとその前のスラッシュに挟まれた部分です。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=0

---

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

function createMailData(doSend) {

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

  const addressSs    = SpreadsheetApp.openById('***SS_ID***');
  const addressSheet = addressSs.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();
}