Google Sheets自定义联系表单问题修复与自动邮件功能开发

问题修复与功能实现方案 #

针对你的Google Sheets联系表单问题，以下是修复无文件提交失败、添加邮件通知的完整解决方案：

修改后的code.gs代码

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate()
  .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}

// 请替换以下配置信息
var SHEET_URL = 'google sheet url here';
var SHEET_NAME = 'sheet1';
var DRIVE_FOLDER_ID = 'google drive folder id here';
var RECEIVER_EMAIL = '你的接收邮箱地址'; // 接收通知的邮箱

function processForm(formdata){
  // 构建表单对象，优化遍历逻辑
  var formObject = {};
  formdata.forEach(element => {
    if (element.name && element.value) {
      formObject[element.name] = element.value;
    }
  });

  // 处理文件上传（可选）
  var fileUrl = '无附件';
  if (formObject.myfile && formObject.myfile.data) {
    var superscript = SuperScript.initSuper(SHEET_URL, SHEET_NAME);
    var file = superscript.uploadFile(DRIVE_FOLDER_ID, formObject.myfile.data, formObject.myfile.name);
    fileUrl = file.getUrl();
  }

  // 写入Google Sheets
  var ss = SpreadsheetApp.openByUrl(SHEET_URL);
  var ws = ss.getSheetByName(SHEET_NAME); // 改用指定工作表名称，更可靠
  ws.appendRow([
    new Date(),
    formObject.name,
    formObject.message,
    formObject.email,
    formObject.phone,
    fileUrl
  ]);

  // 发送通知邮件
  var emailSubject = `新的联系表单提交：${formObject.name}`;
  var emailBody = `
  收到新的表单提交：
  提交时间：${new Date()}
  姓名：${formObject.name}
  邮箱：${formObject.email}
  电话：${formObject.phone}
  留言：${formObject.message}
  附件链接：${fileUrl}
  `;
  MailApp.sendEmail(RECEIVER_EMAIL, emailSubject, emailBody);
}

修改后的index.html代码

<!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">
</head>
<body>
    <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><br><br>
                        <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><br><br>
                        <small>Format: +919234567898</small><br><br>
                    </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">
                    </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);

        var myfile = {};
        function handleFormSubmit() {
            var formdata = $('#myForm').serializeArray();
            // 仅当有文件时才添加文件数据
            if (myfile.data) {
                formdata.push({
                    name: 'myfile',
                    value: myfile
                });
            }
            google.script.run.withSuccessHandler(success).withFailureHandler(failure).processForm(formdata);
        }

        function success(){
            document.getElementById("myForm").reset();
            // 重置myfile对象，避免下次提交重复上传之前的文件
            myfile = {};
            Swal.fire({
                position: 'center',
                icon: 'success',
                title: '发送成功，我们会尽快联系你',
                showConfirmButton: true,
            })
        }

        function failure(error) {
            Swal.fire({
                icon: 'error',
                title: '提交失败',
                text: error.message,
            })
        }

        var reader = new FileReader();
        reader.onloadend = function(e) {
            myfile.data = e.target.result;
            myfile.name = reader.fileName;
        };

        $('#file').change(function(){
            var file = $('#file')[0].files[0];
            if (file) {
                reader.fileName = file.name;
                reader.readAsDataURL(file);
            } else {
                myfile = {};
            }
        })
    </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>

关键修改说明

1. 修复无文件提交问题：

   • 前端：handleFormSubmit中仅当myfile有数据时才将其加入表单数据，避免空对象传给后端；文件选择框清空时同步重置myfile。
   • 后端：判断formObject.myfile是否存在，仅当存在时调用上传方法，否则设置文件链接为"无附件"，避免报错。

2. 添加邮件通知功能：

   • 新增RECEIVER_EMAIL配置项，替换为你的接收邮箱；
   • 表单写入Sheets后，自动组装包含所有表单信息的邮件内容，调用MailApp.sendEmail发送通知。

3. 其他优化：

   • 后端改用getSheetByName指定工作表，比getSheets()[0]更稳定；
   • 前端添加错误捕获逻辑，提交失败时显示错误提示；
   • 成功提交后重置myfile，避免重复上传旧文件；
   • 简化表单对象构建逻辑，去掉冗余遍历。

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