Google Sheets侧边栏表单提交报错：TypeError问题求助

错误根源梳理 #

1. 事件绑定拼写错误：按钮点击事件绑定方法addEventListened应为addEventListener，导致点击逻辑无法触发
2. 表单元素ID不匹配：
   • 培训类型下拉框未设置id="trainingType"，无法获取选中值
   • 工号输入框的id错误设为trainingType，应改为jobNumber，导致获取该元素时返回undefined
3. 参数传递不匹配：后端addNewRow期望接收一个整合后的表单数据对象，但前端调用时传入的是DOM元素而非组装好的rowData
4. 冗余JS引入：bootstrap.bundle.min.js已包含Popper.js，无需重复引入单独的Popper和bootstrap.min.js

修正后的前端代码 #

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Training Recorder</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
</head>
<body>
  <div class="container">
    <div>
      <div class="mb-3">
        <label for="dateUndertaken" class="form-label"><strong>Date Undertaken</strong></label>
        <input type="date" class="form-control" id="dateUndertaken">
      </div>
      <div class="mb-3">
        <label for="trainingType" class="form-label"><strong>Training Type</strong></label>
        <!-- 新增id属性 -->
        <select class="form-select" aria-label="Default select example" id="trainingType">
          <option selected></option>
          <option value="Product representative">Product representative</option>
          <option value="Informal staff discussions/training">Informal staff discussions/training</option>
          <option value="Seminar/Webinar">Seminar/Webinar</option>
          <option value="Formal training cource">Formal training cource</option>
          <option value="Technical reading">Technical reading</option>
          <option value="ComplyNZ technical meeting">ComplyNZ technical meeting</option>
        </select>
      </div>
      <div class="mb-3">
        <label for="jobNumber" class="form-label"><strong>CNZ Job Number</strong></label>
        <div id="jobNumberHelp" class="form-text mb-3">Was the training related to assessing a specific job or jobs?  If so, record the job number(s).  If not, enter N/A.</div>
        <!-- 修正id为jobNumber -->
        <input type="text" class="form-control" id="jobNumber">
      </div>
      <div class="mb-3">
        <label for="trainingTopic" class="form-label"><strong>Topic</strong></label>
        <div id="jobNumberHelp" class="form-text mb-3">This is just a headline, not a full description of the training.</div>
        <input type="text" class="form-control" id="trainingTopic">
      </div>
      <div class="mb-3">
        <label for="trainingPurpose" class="form-label"><strong>Purpose</strong></label>
        <div id="jobNumberHelp" class="form-text mb-3">Outline what the purpose of the training was.</div>
        <textarea class="form-control" id="trainingPurpose" rows="3"></textarea>
      </div>
      <div class="mb-3">
        <label for="trainingOutcome" class="form-label"><strong>Outcome</strong></label>
        <div id="trainingOutcomeHelp" class="form-text mb-3">In your own words, describe the outcome of the training, and how you will put it into effect.</div>
        <textarea class="form-control" rows="3" id="trainingOutcome"></textarea>
      </div>
      <button class="btn btn-primary" id="addButton">Add</button>
    </div>
  </div>
  <!-- 只保留bundle版本，移除冗余引入 -->
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
  <script>
    function afterButtonClicked() {
      // 获取表单值，增加空值判断避免undefined
      const dateUndertaken = document.getElementById("dateUndertaken").value || "";
      const trainingType = document.getElementById("trainingType").value || "";
      const jobNumber = document.getElementById("jobNumber").value || "";
      const trainingTopic = document.getElementById("trainingTopic").value || "";
      const trainingPurpose = document.getElementById("trainingPurpose").value || "";
      const trainingOutcome = document.getElementById("trainingOutcome").value || "";
      
      const rowData = {
        dateUndertaken,
        trainingType,
        jobNumber,
        trainingTopic,
        trainingPurpose,
        trainingOutcome
      };
      // 传递组装好的rowData对象
      google.script.run.addNewRow(rowData);
    }
    // 修正事件绑定方法名
    document.getElementById("addButton").addEventListener("click", afterButtonClicked);
  </script>
</body>
</html>

修正后的后端代码 #

function addNewRow(rowData) {
  const dateEntered = new Date();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Data");
  // 增加空值处理，避免因表单未填写导致的错误
  ws.appendRow([
    rowData.dateUndertaken || "",
    rowData.trainingType || "",
    rowData.jobNumber || "",
    rowData.trainingTopic || "",
    rowData.trainingPurpose || "",
    rowData.trainingOutcome || "",
    dateEntered
  ]);
}

额外优化建议 #

• 表单提交前增加必填项验证，避免空数据写入表格
• 可以在google.script.run后添加withSuccessHandler和withFailureHandler来处理成功/失败的反馈，提升用户体验

内容的提问来源于stack exchange，提问作者halma562