LoginSignup
0
1

More than 1 year has passed since last update.

@AbridgeOkinawa(株式会社 エイブリッジ)

GoogleAppsScriptで大量メール送信　第3回実装

Last updated at Posted at 2022-11-22

はじめに

第1回、第2回とお付き合いいただきありがとうございます。
そろそろ、実装内容を記事にしないと飽きられる:relaxed:

ソースコードの体系(？)としては、例えば、エラーメッセージを変更したいのであれば、定数を変えるなど、非エンジニアの方でも、簡単な修正であればできるような記述にしています(たぶん:sunny:)

※こちらは前回までの記事です。

管理方法

  • 送信対象リスト：当スプレッドシートを元にメール送信
    image.png
    社員ID・・・国別コード(2桁) + 数字(4桁)
    名前・・・社員名
    メールアドレス・・・メールアドレス
    テンプレート・・・メールアドレステンプレート(数字1桁)
    勤怠表枚数・・・勤怠表送信対象ファイルす数
    勤怠表1_ID・・・勤怠表スプレッドシートID No.1
    勤怠表2_ID・・・勤怠表スプレッドシートID No.2
    勤怠表3_ID・・・勤怠表スプレッドシートID No.3
    給与明細_ID・・・給与明細スプレッドシートID
    送信結果・・・OKまたはエラーメッセージ格納

  • 国別フォルダ：国別コードごとに勤怠表フォルダおよび給与明細フォルダのID格納
    image.png
    contrycode：国別コード
    timeID：勤怠表フォルダID
    payID：給与明細フォルダID

  • メールテンプレート：バリエーションを持たせるために、メールテンプレートを複数用意
    (例)
    image.png
    image.png

  • 勤怠表フォルダ：当フォルダに勤怠表ファイルをPDFファイルで格納
    命名規則：国別コード(2桁)_社員名_勤怠表.pdf

  • 給与明細フォルダ：当フォルダに給与明細ファイルをPDFファイルで格納
    命名規則：国別コード(2桁)_社員名_給与明細.pdf

プログラム構成を考える

こんな感じで考えました。

image.png
image.png

エラーメッセージ

定数名 テキスト 備考
ERROR_COLO #F08080 一目でわかるようにセルに色を塗ります
NONE_OFFICE_ID 社員IDの入力がありません
NONE_STAFF_NAME 社員名の入力がありません
NONE_TEMPLATE メールテンプレートの入力がありません
NONE_TIME_FOLDER 該当する勤怠表フォルダがありません 　
OVER_TIME_CARD 勤怠表送付件数は3件までです 　
NONE_PAY_FOLDER 該当する給与明細書フォルダがありません 　
NONE_TIME_CARD 該当する勤怠表がありません 　
NONE_PAY_SLIP 該当する給与明細書がありません 　
NONE_MAIL_ADDRESS メールアドレスがありません 　
NONE_TXT_TEMPLATE メールテンプレートがありません 　

グローバル変数

グローバル変数とは、どの関数でも使用できる変数のこと言います。それに対して、1つの関数内でのみ使用できる変数のことをローカル変数と言います。

// 送信対象リスト
const ss = SpreadsheetApp.openById("ファイルID");
// メールテンプレート格納フォルダID
const mailId = "格納フォルダID";
// 国別管理フォルダ
const contryTable = ss.getSheetByName('国別フォルダ管理');
// スタッフ管理フォルダ
const staffTable = ss.getSheetByName('スタッフ管理');

// 社員Id
let offce_id = "";
// 国別Code
let country = "";
// 勤怠表スプレッドシートid
let timeCardId = "";
// 勤怠表スプレッドシート管理フォルダ
let timeCardfolder = "";
// 給与明細スプレッドシートid
let paySlipId = "";
// 給与明細スプレッドシート管理フォルダ
let paySlipfolder = "";
// データ開始行
let row = 2;
// 送信件数
let memberCnt = "";

定数

定数は再代入や再宣言不可能な変数のことを言います。

非エンジニアの方でも簡単にエラーメッセージを修正できるように定数にしてみましたが、、分かり辛いかも。。。

// 給与明細メールタイトル
let MAIL_TITTLE = "給与明細の送付({month}月分)";

/*★*★*★*★*★*★*★*★*★*★
* エラーカラー＆エラーメッセージ
*★*★*★*★*★*★*★*★*★*★*/
const ERROR_COLOR = "#F08080";
const NONE_OFFICE_ID = "社員IDの入力がありません";
const NONE_STAFF_NAME = "社員名の入力がありません";
const NONE_TEMPLATE = "メールテンプレートの入力がありません";
const NONE_TIME_FOLDER = "該当する勤怠表フォルダがありません";
const OVER_TIME_CARD = "勤怠表送付件数は3件までです";
const NONE_PAY_FOLDER = "該当する給与明細書フォルダがありません";
const NONE_TIME_CARD = "該当する勤怠表がありません";
const NONE_PAY_SLIP = "該当する給与明細書がありません";
const NONE_MAIL_ADDRESS = "メールアドレスがありません";
const NONE_TXT_TEMPLATE = "メールテンプレートがありません"

メイン関数

function main() {

  /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
  * 国別フォルダ取得
  *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
  let values = contryTable.getDataRange().getValues();

  // 国別コード＆国別フォルダidをオブジェクト化
  const [headers, ...records] = values;
  const objects = records.map(record => Object.fromEntries(record.
    map((value, j) => [headers[j], value])
  ));

  /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
  * 勤怠表取得/給与明細取得/メール送信処理
  *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
  checkData(objects, row);
}

国別コードをキー、国別フォルダIDを値としてオブジェクトを作成しました。

送信データチェック

/*★*★*★*★*★*★*★*★*★*★
* 送信データチェック
*★*★*★*★*★*★*★*★*★*★*/
function checkData(objects, row) {
  // トリガー用のフラグ変数と値の取得
  let ps = PropertiesService.getScriptProperties();
  let taskData = parseInt(ps.getProperty("task"));

  // トリガー終了フラグ(true：終了　false:継続)
  let endflag = true;

  //taskDataの値を判定(値がない時は0とする）
  if (taskData <= 0) {
    taskData = 0;
    ps.setProperty("task", 0);
  }

  // 日時取得
  let startTime = new Date();
  //taskDataが0であれば、total開始時間を保存
  if (taskData == 0) {
    ps.setProperty("scriptstart", startTime);
  }

  // 対象件数算出(データ最終入力行　- タイトル行(1行)）
  memberCnt = staffTable.getLastRow() - (row - 1);

  for (let i = taskData; i < memberCnt; i++) {
    // 社員ID取得(行+1,1列目)
    offce_id = staffTable.getRange(row + i, 1).getValue();
    country = offce_id.substring(0, 2);

    // 社員ID入力チェック
    if (country == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_OFFICE_ID);
      continue;
    }

    for (let [key, value] of Object.entries(objects)) {
      // 国別コードから該当フォルダ(勤怠/給与)取得
      if (value['countrycode'] == country) {
        timeCardId = value['timeID'];
        timeCardfolder = DriveApp.getFolderById(value['timeID']);
        paySlipId = value['payID'];
        paySlipfolder = DriveApp.getFolderById(value['payID']);
        break;
      }
    }

    // 名前入力チェック
    if (staffTable.getRange(row + i, 2).getValue() == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_STAFF_NAME);
    }

    // テンプレ入力チェック
    if (staffTable.getRange(row + i, 2).getValue() == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_TEMPLATE);
    }

    // フォルダ存在チェック
    if (timeCardfolder == '' && paySlipfolder == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_TIME_FOLDER + NONE_PAY_FOLDER);
    } else if (timeCardfolder == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_TIME_FOLDER);
    } else if (paySlipfolder == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_PAY_FOLDER);
    }

    /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
    * メールアドレス存在チェック
    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
    if (staffTable.getRange(row + i, 3).getValue() == '') {
      staffTable.getRange(row + i, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row + i, 10).setValue(NONE_MAIL_ADDRESS);
      continue;
    }

    /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
    * 勤怠表取得処理
    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
    // 勤怠表取得(引数　社員ID　行　スプレッドシート)
    if (timeFiles(offce_id, row + i, staffTable, timeCardfolder) == false) {
      continue;
    }

    /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
    * 給与明細取得処理
    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
    // 給与明細取得(引数　社員ID　行　スプレッドシート)    
    if (payslipFiles(offce_id, row + i, staffTable, paySlipfolder) == false) {
      continue;
    }

    /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
    * メール送信処理
    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
    // メール送信
    processMail(row + i, staffTable);
    // 処理開始から5分以上経過したらトリガーを作って処理を終了
    if (Date.now() - startTime > (5 * 60 * 1000)) {
      ps.setProperty("task", taskData);
      createTrigger();
      endflag = false;
      return;
    }

    //　Newトリガー発行されている場合、進行中のトリガー削除
    if (endflag == true) {
      deleteTriggers();
      ps.setProperty("task", 0);
    }
    // 送信データ行へポイント
    taskData = Number(taskData) + 1;
  }
}

勤怠表検索


/*★*★*★*★*★*★*★*★*★*★
* 勤怠表検索
*★*★*★*★*★*★*★*★*★*★*/
function timeFiles(offce_id, row, staffTable, timeCardfolder) {
  // フォルダ内ファイル取得
  const file = timeCardfolder.getFiles();

  // ファイルカウント変数
  let cnt = 0;
  // 勤怠表ID入力欄(E列)
  let col = 6;
  let timeCard = "";
  // ファイル名取得変数
  let fileName = "";
  // ファイルID取得変数
  let fileId = "";

  // 社員IDに合致した勤怠表を取得して、F～H列に格納
  while (file.hasNext()) {
    // 勤怠表4枚以上の場合はエラー
    if (cnt == 3) {
      staffTable.getRange(row, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row, 10).setValue(OVER_TIME_CARD);
      return false;
      break;
    }
    timeCard = file.next();
    // ファイル名取得
    fileName = timeCard.getName();
    // ファイルID取得
    fileId = timeCard.getId();

    // 社員IDに合致した勤怠表のファイルIDを格納
    if (fileName.match(offce_id)) {
      staffTable.getRange(row, col).setValue(fileId);
      cnt++;
      col++;
    }
  }

  // 勤怠表存在チェック
  if (cnt == 0) {
    staffTable.getRange(row, 1).setBackground(ERROR_COLOR);
    staffTable.getRange(row, 10).setValue(NONE_TIME_CARD);
    return false;
  } else {
    // 勤怠表枚数を格納
    staffTable.getRange(row, 5).setValue(cnt);
    return true;
  }
}

給与明細検索

/*★*★*★*★*★*★*★*★*★*★
* 給与明細検索
*★*★*★*★*★*★*★*★*★*★*/
function payslipFiles(offce_id, row, staffTable, paySlipfolder) {
  // フォルダ内ファイル取得
  const files = paySlipfolder.getFiles();

  // 給与明細ID入力欄(I列)
  let col = 9;
  let paySlip = "";
  // ファイル名取得変数
  let fileName = "";
  // ファイルID取得変数
  let fileId = "";

  // 社員IDに合致した給与明細のファイルIDを取得して、I列に格納
  while (files.hasNext()) {
    paySlip = files.next();
    // ファイル名取得
    fileName = paySlip.getName();
    // ファイルID取得
    fileId = paySlip.getId();

    // 社員IDに合致した給与明細のファイルIDを格納
    if (fileName.match(offce_id)) {
      staffTable.getRange(row, col).setValue(fileId);
    }
  }

  // 給与明細存在チェック
  if (staffTable.getRange(row, col).getValue() == '') {
    staffTable.getRange(row, 1).setBackground(ERROR_COLOR);
    staffTable.getRange(row, 10).setValue(NONE_PAY_SLIP);
    return false;
  }
  return true;

}

メール送信

/*★*★*★*★*★*★*★*★*★*★
* メール送信
*★*★*★*★*★*★*★*★*★*★*/
function processMail(row, staffTable) {
  //勤怠表ファイルID列(F列)
  let timeCol = 6;
  //給与明細ファイルID列(I列)
  let payCol = 9;

  // メールアドレス列(C列)からメールアドレスを取得
  let address = staffTable.getRange(row, 3).getValue();

  /*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
  *　メールアドレスあり：メール送信処理
  *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*/
  // 名前列(B列)から名前を取得
  let name = staffTable.getRange(row, 2).getValue();

  // 勤怠表枚数(E列)取得
  const cnt = staffTable.getRange(row, 5).getValue();

  // 勤怠表ファイルID(F～G列)をListに格納
  let tempList = [];
  let time = "";
  for (let i = 0; i < cnt; i++) {
    time = staffTable.getRange(row, timeCol).getValue();
    tempList.push(DriveApp.getFileById(time).getBlob());
    timeCol++;
  }

  // 給与明細取得
  let pay = staffTable.getRange(row, payCol).getValue();
  pay = DriveApp.getFileById(pay).getBlob();

  // 給与明細を添付ファイルリストに追加
  tempList.push(pay);
  // メールテンプレート識別子格納
  let temple = staffTable.getRange(row, 4).getValue();

  // 添付ファイル
  const options = {
    attachments: tempList
  };

  // メール送信
  execution(address, name, temple, options);
}

メール送信実行

/*★*★*★*★*★*★*★*★*★*★
* メール送信実行
*★*★*★*★*★*★*★*★*★*★*/
function execution(address, name, temple, options) {
  // メールの件名作成
  let subject = MAIL_TITTLE;
  const lastMonth = new Date().getMonth();
  subject = subject.replace('{month}', lastMonth);

  // メールテンプレート格納フォルダにアクセス
  let target = DriveApp.getFolderById(mailId);

  let files = target.getFiles();
  let txtTemplate = '';

  // 該当するメールテンプレートを抽出
  while (files.hasNext()) {
    let file = files.next();
    let fileName = file.getName();

    if (fileName == temple) {
      let txtDoc = DocumentApp.openById(file.getId());
      txtTemplate = txtDoc.getBody().getText();
      txtTemplate = txtTemplate.replace('{name}', name.replace(' ', '　'));
    }
  }

  // 該当するメールテンプレート有無チェック
  if (txtTemplate == '') {
    staffTable.getRange(row, 1).setBackground(ERROR_COLOR);
    staffTable.getRange(row, 10).setValue(NONE_TXT_TEMPLATE);
    return false;
  } else {
    // メール送信
    try {
      GmailApp.sendEmail(address, subject, txtTemplate, options);
    } catch (e) {
      console.log('エラーを検知しました。');
      console.log('エラー内容：' + e.message);
      staffTable.getRange(row, 1).setBackground(ERROR_COLOR);
      staffTable.getRange(row, 10).setValue(e.message);
    }
  }
}

トリガー作成＆削除


/*★*★*★*★*★*★*★*★*★*★
* トリガー作成
*★*★*★*★*★*★*★*★*★*★*/
function createTrigger() {
  ScriptApp.newTrigger("main")
    .timeBased()
    .after(1 * 60 * 1000)
    .create();
}

/*★*★*★*★*★*★*★*★*★*★
* トリガー削除
*★*★*★*★*★*★*★*★*★*★*/
function deleteTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  for (let i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
}
0
1
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1