はじめに

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

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

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

管理方法

送信対象リスト：当スプレッドシートを元にメール送信

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

contrycode：国別コード
timeID：勤怠表フォルダID
payID：給与明細フォルダID
メールテンプレート：バリエーションを持たせるために、メールテンプレートを複数用意
(例)
勤怠表フォルダ：当フォルダに勤怠表ファイルをPDFファイルで格納
命名規則：国別コード(2桁)_社員名_勤怠表.pdf
給与明細フォルダ：当フォルダに給与明細ファイルをPDFファイルで格納
命名規則：国別コード(2桁)_社員名_給与明細.pdf

プログラム構成を考える

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

エラーメッセージ

定数名	テキスト	備考
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]);
  }
}