讓教學組提早下班系列 - Google Sheets + Apps Script 印領清冊節數計算

學校教學組每個月底還有個重大工作就是幫代課老師計算鐘點費，

主計主任希望印領清冊上可以看到老師的 1/3 上課日期 和 (2) 當天的節數。

代課老師的代課情形都不太一樣，教學組真的需要花很多時間來一個一個來登打！

現在有了 #Google試算表 和 #GoogleAppsScript 的幫忙，

相信教學組又可以省下一些時間了！

 

不知道有沒有學校跟我們一樣，印領清冊上需要把上課日期和節數寫在上面的，

如果還是手動登打的老師，趕快下載拿去用吧！

單一教師檔案下載、印領清冊優化版

🎯最終優化完成版 - 新增日期、刪除假日、複製工作表🎯

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('產生日期')

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

    .addToUi();

}

function showPrompt() {

  var ui = SpreadsheetApp.getUi();

  var startYearPrompt = ui.prompt(

    '請輸入起始年份：',

    ui.ButtonSet.OK_CANCEL);

  var startYearResponse = startYearPrompt.getResponseText();

  var startYear = parseInt(startYearResponse);

  if (startYearPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(startYear)) {

    return;

  }

  var startMonthPrompt = ui.prompt(

    '請輸入起始月份 (1-12)：',

    ui.ButtonSet.OK_CANCEL);

  var startMonthResponse = startMonthPrompt.getResponseText();

  var startMonth = parseInt(startMonthResponse);

  if (startMonthPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(startMonth) || startMonth < 1 || startMonth > 12) {

    return;

  }

  var startDayPrompt = ui.prompt(

    '請輸入起始日期 (1-31)：',

    ui.ButtonSet.OK_CANCEL);

  var startDayResponse = startDayPrompt.getResponseText();

  var startDay = parseInt(startDayResponse);

  if (startDayPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(startDay) || startDay < 1 || startDay > 31) {

    return;

  }

  var endYearPrompt = ui.prompt(

    '請輸入結束年份：',

    ui.ButtonSet.OK_CANCEL);

  var endYearResponse = endYearPrompt.getResponseText();

  var endYear = parseInt(endYearResponse);

  if (endYearPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(endYear) || endYear < startYear) {

    return;

  }

  var endMonthPrompt = ui.prompt(

    '請輸入結束月份 (1-12)：',

    ui.ButtonSet.OK_CANCEL);

  var endMonthResponse = endMonthPrompt.getResponseText();

  var endMonth = parseInt(endMonthResponse);

  if (endMonthPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(endMonth) || endMonth < 1 || endMonth > 12 || (startYear == endYear && startMonth > endMonth)) {

    return;

  }

  var endDayPrompt = ui.prompt(

    '請輸入結束日期 (1-31)：',

    ui.ButtonSet.OK_CANCEL);

  var endDayResponse = endDayPrompt.getResponseText();

  var endDay = parseInt(endDayResponse);

  if (endDayPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(endDay) || endDay < 1 || endDay > 31) {

    return;

  }

  var daysOfWeekPrompt = ui.prompt(

    '請輸入星期數字 (0=星期日, 1=星期一, ..., 6=星期六)，例如 1, 2, 5：',

    ui.ButtonSet.OK_CANCEL);

  var daysOfWeekResponse = daysOfWeekPrompt.getResponseText();

  if (daysOfWeekPrompt.getSelectedButton() == ui.Button.CANCEL) {

    return;

  }

  try {

    // 將輸入的字符串以逗號分隔，並轉換為數字陣列

    var daysOfWeek = daysOfWeekResponse.split(",").map(Number);

    // 檢查是否包含有效的星期數字

    if (daysOfWeek.some(isNaN) || daysOfWeek.some(day => day < 0 || day > 6)) {

      ui.alert("星期輸入錯誤！");

      return;

    }

  } catch (e) {

    ui.alert("星期輸入錯誤！");

    return;

  }

  // 建立一個物件來儲存每個星期的數字

  var numbersInParentheses = {};

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

    var dayOfWeek = daysOfWeek[i];

    var numberPrompt = ui.prompt(

      '請輸入星期' + dayOfWeek + '幾節課：',

      ui.ButtonSet.OK_CANCEL);

    var numberResponse = numberPrompt.getResponseText();

    var number = parseInt(numberResponse);

    if (numberPrompt.getSelectedButton() == ui.Button.CANCEL || isNaN(number)) {

      return;

    }

    numbersInParentheses[dayOfWeek] = number;

  }

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

  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.getActiveCell().setValue(formattedDates);

}

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;

}

~~快來試試看吧~~