侧边栏HTML表单无法向Google Sheet提交数据求助

你的代码存在几处关键错误，导致数据无法正常提交到表格，以下是针对性的修正方案：

1. Google Apps Script 代码修正 #

核心错误点： #

• SpreadsheetApp.getActiveSheet(Worksheet) 语法错误：getActiveSheet() 无需传入参数，Worksheet 是未定义变量，会直接触发报错。
• 字段名不匹配：HTML中多选服务的name是servicesRequested，但脚本里误用了form.servicesRequired；HTML里的calloutFee，脚本里写成了form.call-outFee（变量名不能包含横杠）。

修正后的脚本代码： #

//@OnlyCurrentDoc

function onOpen() {
 SpreadsheetApp
   .getUi()
   .createMenu("Intake Form")
   .addItem("Show Intake Form", "showAdminSidebar")
   .addToUi();
}

function showAdminSidebar() {
 var widget = HtmlService.createHtmlOutputFromFile("Intake Form.html");
 widget.setTitle("Intake Form");
 SpreadsheetApp.getUi().showSidebar(widget);
}

function appendRowFromFormSubmit(formData) {
 var row = [
   formData.jobID,
   formData.customerName,
   formData.customerAddress,
   formData.customerPostcode,
   formData.customerPhone,
   formData.customerEmail,
   formData.applianceMake,
   formData.applianceModel,
   formData.reportedFault,
   formData.servicesRequested,
   formData.serviceLocation,
   formData.inspectionFee,
   formData.calloutFee
 ];
 // 若要指定固定工作表，可替换为 getSheetByName("你的工作表名称")
 SpreadsheetApp.getActiveSheet().appendRow(row);
}

2. HTML表单代码修正 #

核心错误点： #

• 多选复选框未处理：google.script.run 不会自动将多选复选框的选中值拼接为数组/字符串，需前端手动收集。
• 存在未闭合的<div>标签，可能影响DOM结构解析。
• 服务地点的标签文字错误，应为"Service Location"。

修正后的HTML代码： #

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
   <script>
     function submitForm() {
       const form = document.getElementById("intakeForm");
       // 收集选中的服务项，用逗号分隔
       const selectedServices = Array.from(form.querySelectorAll('input[name="servicesRequested"]:checked'))
         .map(item => item.value).join(", ");
       // 收集选中的服务地点
       const selectedLocation = Array.from(form.querySelectorAll('input[name="serviceLocation"]:checked'))
         .map(item => item.value).join(", ");
       
       // 构造提交数据对象
       const formData = {
         jobID: form.jobID.value,
         customerName: form.customerName.value,
         customerAddress: form.customerAddress.value,
         customerPostcode: form.customerPostcode.value,
         customerPhone: form.customerPhone.value,
         customerEmail: form.customerEmail.value,
         applianceMake: form.applianceMake.value,
         applianceModel: form.applianceModel.value,
         reportedFault: form.reportedFault.value,
         servicesRequested: selectedServices,
         serviceLocation: selectedLocation,
         inspectionFee: form.inspectionFee.value,
         calloutFee: form.calloutFee.value
       };

       // 提交并添加反馈逻辑
       google.script.run
         .withSuccessHandler(() => {
           alert("提交成功！");
           form.reset(); // 清空表单
         })
         .withFailureHandler(err => {
           alert("提交失败：" + err.message);
         })
         .appendRowFromFormSubmit(formData);
     }
   </script>
 </head>
 <body>
   <h2>Enter Customer Details</h2>
   <form id="intakeForm">
     <label for="jobID">Job ID</label>
     <input type="text" id="jobID" name="jobID"><br><br>
     <label for="customerName">Customer Name</label>
     <input type="text" id="customerName" name="customerName"><br><br>
     <label for="customerAddress">Customer Address</label>
     <input type="text" id="customerAddress" name="customerAddress"><br><br>
     <label for="customerPostcode">Customer Postcode</label>
     <input type="text" id="customerPostcode" name="customerPostcode"><br><br>
     <label for="customerPhone">Customer Phone</label>
     <input type="text" id="customerPhone" name="customerPhone"><br><br>
     <label for="customerEmail">Customer email</label>
     <input type="text" id="customerEmail" name="customerEmail"><br><br>
     <label for="applianceMake">Appliance Make</label>
     <input type="text" id="applianceMake" name="applianceMake"><br><br>
     <label for="applianceModel">Appliance Model</label>
     <input type="text" id="applianceModel" name="applianceModel"><br><br>
     <label for="reportedFault">Reported Fault</label>
     <input type="text" id="reportedFault" name="reportedFault"><br><br>
     
     <div>
       <label for="servicesRequested">Services Requested:</label><br>
       <input type="checkbox" id="inspection" name="servicesRequested" value="Inspection">
       <label for="inspection">Inspection</label><br>
       <input type="checkbox" id="servicing" name="servicesRequested" value="Servicing">
       <label for="servicing">Servicing</label><br>
       <input type="checkbox" id="repairs" name="servicesRequested" value="Repairs">
       <label for="repairs">Repairs</label><br>
       <input type="checkbox" id="refurbishedAppliance" name="servicesRequested" value="Refurbished Appliance">
       <label for="refurbishedAppliance">Refurbished Appliance</label><br><br>
     </div>
     
     <div>
       <label for="serviceLocation">Service Location:</label><br> 
       <input type="checkbox" id="atWorkshop" name="serviceLocation" value="At Workshop">
       <label for="atWorkshop">At Workshop</label><br>
       <input type="checkbox" id="onsite" name="serviceLocation" value="Onsite">
       <label for="onsite">Onsite</label><br><br>
     </div>
     
     <label for="inspectionFee">Inspection Fee</label>
     <input type="text" id="inspectionFee" name="inspectionFee"><br><br>
     <label for="calloutFee">Call-out Fee</label>
     <input type="text" id="calloutFee" name="calloutFee"><br><br>
     
     <input type="button" value="Submit" onclick="submitForm();">
   </form>
 </body>
</html>

3. 测试步骤 #

1. 替换原有的脚本和HTML代码。
2. 刷新Google Sheet，重新打开侧边栏。
3. 填写表单内容后点击Submit按钮，检查表格是否新增数据。

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