準備下拉選項

讀取試算表為下拉選單做準備

GAS

//準備下拉選單

function getOptions() {

  const ss = SpreadsheetApp.openById(sheetID);

  const teachers = ss.getSheetByName("教師基本資料").getRange("C2:C").getValues().flat().filter(String);

  const classes = ss.getSheetByName("選單資料").getRange("E2:E").getValues().flat().filter(String);

  const subjects = ss.getSheetByName("選單資料").getRange("F2:F").getValues().flat().filter(String);

  const leaves = ss.getSheetByName("選單資料").getRange("C2:C").getValues().flat().filter(String);

  return { teachers, classes, subjects, leaves };

}

HTML、JavaScript

    // === 初始化選單 ===

    function initForm(options) {

      teachers = ["-- 教師 --", ...options.teachers];

      classes = ["-- 班級 --", ...options.classes];

      subjects = ["-- 科目 --", ...options.subjects];

      leaves = ["-- 假別 --", ...options.leaves];

      document.getElementById("請假教師").innerHTML =

        teachers.map(t => `<option>${t}</option>`).join("");

      document.getElementById("假別").innerHTML =

        leaves.map(l => `<option>${l}</option>`).join("");

      addRow();

      var status = document.getElementById("status");

      status.style.color = "green";

      status.innerText = `✅ 系統已就緒`;

    }

//以下JavaScript指令置於</Body>前面

<script>

    google.script.run.withSuccessHandler(initForm).getOptions();

</script>