基于Google Sheets与Apps Script的联系表单功能修复请求

一、邮件发送功能修复 #

问题原因 #

1. MailApp.sendEmail 参数错误：重复使用 to 属性，抄送需用 cc 参数
2. 邮件内容为静态字符串，未动态填充表单提交数据
3. 表单对象构建冗余，文件名称变量引用错误

修正后的 code.js 代码 #

替换原 processForm 函数：

var url = '#' // 替换为你的表格URL
var sh = 'file1'
var folderId = '#' // 替换为你的Drive文件夹ID
var receiverEmail = 'receiver_email@gmail.com' // 替换为指定收件人邮箱

function processForm(formdata) {
  var superscript = SuperScript.initSuper(url, sh);
  var formObject = {};
  // 优化表单对象构建，避免重复赋值
  formdata.forEach(element => {
    if (element.name) {
      formObject[element.name] = element.value;
    }
  });
  
  var fileUrl = "";
  var f = formdata.find(({ name }) => name == "myfile");
  if (f && f.value?.name && f.value?.data) {
    // 修正文件名称引用：用f.value.name而非formObject.myfilename
    var file = superscript.uploadFile(folderId, f.value.data, f.value.name);
    fileUrl = file.getUrl();
  }
  
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheets()[0];
  ws.appendRow([new Date(), formObject.name, "'" + formObject.message, formObject.email, formObject.phone, fileUrl]);

  // 发送邮件
  var emailSubject = "新的表单提交通知";
  var emailBody = `
姓名: ${formObject.name}
邮箱: ${formObject.email}
电话: ${formObject.phone}
留言: ${formObject.message}
附件链接: ${fileUrl ? fileUrl : '无'}
  `;

  MailApp.sendEmail({
    to: receiverEmail,
    cc: formObject.email,
    subject: emailSubject,
    body: emailBody.trim()
  });
}

二、加载动画与提交延迟优化 #

问题原因 #

1. 表单ID大小写不匹配：现有表单ID为 myForm，但JS监听的是小写 myform
2. 加载动画CSS未整合到现有页面，且未在提交完成后隐藏
3. 未在表单提交触发时立即显示加载动画

修正后的 index.html 代码 #

将加载动画CSS加入<head>，添加加载动画DOM，并修改提交逻辑：

<!DOCTYPE html>
<head>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"
        integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ=="
        crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css"
        integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
    <!-- 加载动画CSS -->
    <style>
        /* Preloder */
        #preloder {
            display: none;
            position: fixed;
            width: 100%;
            height: 100%;
            top: 0;
            left: 0;
            z-index: 999999;
            background: rgba(255, 255, 255, 0.8);
        }
        .loader {
            width: 50px;
            height: 50px;
            position: absolute;
            top: 50%;
            left: 50%;
            margin-top: -25px;
            margin-left: -25px;
            border-radius: 60px;
            animation: loader 0.8s linear infinite;
            -webkit-animation: loader 0.8s linear infinite;
        }
        @keyframes loader {
            0% {
                -webkit-transform: rotate(0deg);
                transform: rotate(0deg);
                border: 4px solid #056d4d;
                border-left-color: transparent;
            }
            50% {
                -webkit-transform: rotate(180deg);
                transform: rotate(180deg);
                border: 4px solid #056d4d;
                border-left-color: transparent;
            }
            100% {
                -webkit-transform: rotate(360deg);
                transform: rotate(360deg);
                border: 4px solid #056d4d;
                border-left-color: transparent;
            }
        }
        @-webkit-keyframes loader {
            0% {
                -webkit-transform: rotate(0deg);
                border: 4px solid #056d4d;
                border-left-color: transparent;
            }
            50% {
                -webkit-transform: rotate(180deg);
                border: 4px solid #056d4d;
                border-left-color: transparent;
            }
            100% {
                -webkit-transform: rotate(360deg);
                border: 4px solid #056d4d;
                border-left-color: transparent;
            }
        }
    </style>
</head>
<body>
    <!-- 加载动画DOM -->
    <div id="preloder"><div class="loader"></div></div>
    
    <div class="container py-5">
        <div class="row">
            <div class="col-lg-5 col-md-8 mx-auto shadow border bg-white p-4 rounded">
                <nav class="navbar navbar-dark bg-primary">
                    <a class="navbar-brand" href="#" fw-bold mb-3>WRITE YOUR QUERY / MESSAGE</a>
                </nav>
                <br>
                <form id="myForm" onsubmit="handleFormSubmit()">
                    <div id="form_alerts"></div>
                    <div class="form-group mb-3">
                        <label for="name">Name</label>
                        <input type="text" class="form-control" id="name" placeholder="Enter your Name here|" name="name"required>
                    </div>
                    <div class="form-group mb-3">
                        <label for="email" >Email</label>
                        <input type="email"  class="form-control" id="email" placeholder="Enter your email address here|" name="email" required>
                    </div>
                    <div class="form-group mb-3">
                        <label for="phone">phone number</label>
                        <input type="tel" id="phone" class="form-control" name="phone" placeholder="Enter your phone number here with country code|" pattern="[+]{1}[0-9]{2}[0-9]{10}" required>
                        <small>requested example Format: +919263943858</small>
                    </div>
                    <div class="form-group mb-3">
                        <label for="message">Message</label>
                        <textarea class="form-control" id="message" placeholder="Write your message here|" name="message" required ></textarea>
                    </div>
                    <div class="form-group mb-3">
                        <label for="uploadFile">Upload File</label>
                        <input type="file" class="form-control" id="file" name="file">
                        <small>For multiple/bigger file size please attach/share via google drive link in message section.</small>
                    </div>
                    <button type="submit" class= "btn btn-primary btn-block">SEND</button>
                </form>
            </div>
        </div>
    </div>
  <script>
        function preventFormSubmit() {
                var forms = document.querySelectorAll('form');
                for (var i = 0; i < forms.length; i++) {
                    forms[i].addEventListener('submit',function (event) {event.preventDefault();});
                }
            }
            window.addEventListener('load', preventFormSubmit);
            
            function handleFormSubmit() {
                // 显示加载动画
                $("#preloder").fadeIn();
                var formdata = $('#myForm').serializeArray()
                formdata.push({name: 'myfile',value: myfile})
                google.script.run.withSuccessHandler(success).processForm(formdata);
            }
            
            function success(){
                 // 隐藏加载动画
                 $("#preloder").fadeOut();
                 document.getElementById("myForm").reset()
                 Swal.fire({position: 'center',icon: 'success',title: '发送成功！我们会尽快联系您',showConfirmButton: true})
            }
            
        var myfile ={},file, reader = new FileReader();
      reader.onloadend = function(e) {
          myfile.data = e.target.result
          myfile.name = file.name
          console.log(myfile)
      };
     $('#file').change(function(){
       file = $('#file')[0].files[0]
        reader.readAsDataURL(file);
     })
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
    <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>
</body>
</html>

额外优化点 #

• 加载动画背景改为半透明，避免完全遮挡页面
• 修正文件输入框的File属性为name，确保表单序列化正确
• 成功提示文本调整为中文（可按需修改）

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