讓教學組提早下班系列 - 印領清冊節數計算 - 優化版

原本只是想解決校內教學組長的工作流程，

想不到上一篇自動生成上課日期和節數這麼受歡迎😍

最近看到學校教學組長，學期末又忙著作業抽查了！

教學組工作真的是一波接一波永遠忙不完！

為了讓教學組可以提早下班，發現上一版只有一個日期還不夠便利，

馬上來個優化版，希望辛苦的教學組可以再省下些時間！

(づ｡◕‿‿◕｡)づ 先了解看看如何使用

如果也跟我們學校一樣需要把日期和節數呈現在印領清冊上的，

趕快下載回去用看看吧！或是可以轉知教學組喔！

👉👉檔案下載優化完成版 - 新增日期、刪除假日、複製工作表👈👈

🎯使用方法：只要維護 日期工作表 內容即可！

🎯教師姓名欄位：抓取日期工作表教師姓名欄位

='日期'!A2

🎯上課時間欄位：運用試算表函數自動合併相關存儲格

='日期'!K2 &'日期'!L2 & CHAR(10) & '日期'!J2

🎯節數欄位：運用試算表函數抓取節數

=IFERROR(VALUE(REGEXEXTRACT('日期'!J2, "共 (\d+) 節")), "")

自行刪掉休假日時，需更改節數部分要特別留意共 〇 節，

共 〇 節中有一個空隔，如果少了空隔程式會讀不到數字，

或是想修改程式把空隔移除也行，一切看老師的用法！ 

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('產生日期')

    .addItem('設定日期', 'showPrompt')

    .addToUi();

}

function getFormattedDates(startYear, startMonth, startDay, endYear, endMonth, endDay, daysOfWeek, numbersInParentheses) {

  // 建立跨年度日期範圍

  var startDate = new Date(startYear, startMonth - 1, startDay);

  var endDate = new Date(endYear, endMonth - 1, endDay);

  var dates = [];

  var currentDate = new Date(startDate); // 確保不直接修改 startDate

  while (currentDate <= endDate) {

    dates.push(new Date(currentDate)); // 新增當前日期

    currentDate.setDate(currentDate.getDate() + 1); // 增加一天

  }

  // 篩選出指定星期的日期

  var filteredDates = dates.filter(function (date) {

    return daysOfWeek.includes(date.getDay());

  });

  // 格式化日期並加上括號內的數字

  var formattedDates = filteredDates.map(function (date) {

    var month = date.getMonth() + 1;

    var day = date.getDate();

    var dayOfWeek = date.getDay();

    var number = numbersInParentheses[dayOfWeek];

    return (month < 10 ? '0' + month : month) + '/' + (day < 10 ? '0' + day : day) + '(' + number + ')';

  });

  // 計算括號內數字的總和

  var totalNumberInParentheses = filteredDates.reduce(function (sum, date) {

    return sum + numbersInParentheses[date.getDay()];

  }, 0);

  // 將日期用逗號分隔，並加上統計天數和括號內數字的總和

  var result = formattedDates.join(', ') + " 共 " + totalNumberInParentheses + " 節";

  return result;

}

function showPrompt() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("日期"); // 取得日期

  // 取得 B2:I 的所有資料

  var data = sheet.getRange("B2:I").getValues();

  // 尋找 B 欄最後一列有資料的索引

  var lastRow = data.length;

  while (lastRow > 0 && data[lastRow - 1][0] === "") {

    lastRow--;

  }

  // 如果 B 欄沒有任何資料，則停止執行

  if (lastRow === 0) {

    SpreadsheetApp.getUi().alert("B 欄沒有任何資料！");

    return;

  }

  // 逐列處理資料，到 B 欄最後一列有資料的索引

  for (var i = 0; i < lastRow; i++) {

    var startYear = data[i][0];

    var startMonth = data[i][1];

    var startDay = data[i][2];

    var endYear = data[i][3];

    var endMonth = data[i][4];

    var endDay = data[i][5];

    // 修正：先將 data[i][6] 轉換為字串再檢查是否包含逗號

    var daysOfWeek = String(data[i][6]).includes(",") ? data[i][6].split(",").map(Number) : [Number(data[i][6])];

    // 修正：先將 data[i][7] 轉換為字串再進行分割

    var numbers = String(data[i][7]).split(",").map(Number);

    // 建立 numbersInParentheses 物件

    var numbersInParentheses = {};

    for (var j = 0; j < daysOfWeek.length; j++) {

      numbersInParentheses[daysOfWeek[j]] = numbers[j];

    }

    // 檢查日期是否有效

    if (isNaN(startYear) || isNaN(startMonth) || isNaN(startDay) ||

      isNaN(endYear) || isNaN(endMonth) || isNaN(endDay) ||

      startYear > endYear || (startYear == endYear && startMonth > endMonth) ||

      (startYear == endYear && startMonth == endMonth && startDay > endDay)) {

      SpreadsheetApp.getUi().alert("第 " + (i + 2) + " 列日期輸入錯誤！"); // i + 2 是因為資料從第二列開始

      return;

    }

    var formattedDates = getFormattedDates(startYear, startMonth, startDay, endYear, endMonth, endDay, daysOfWeek, numbersInParentheses);

    // 將結果寫入到 J 欄位對應的儲存格

    sheet.getRange("J" + (i + 2)).setValue(formattedDates);

  }

}

~~快來試試看吧~~