如何在Google Apps Script网页表单中实现附件上传与动态问卷功能

现有表单已实现团队选择→动态加载对应Google表格标签页的问题→提交响应到Responses标签页的核心流程，但附件上传功能存在问题，以下是修复后的完整代码及关键说明：

Google Apps Script代码（Code.gs） #

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}

function getQuestionsForTeam(team) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(team);
  if (!sheet) {
    return '';
  }

  // 读取对应团队标签页第一列的问题
  var data = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var questionsHtml = data.map(function(row) {
    var questionText = row[0];
    return `<label for="${questionText}">${questionText}:</label><br>
            <input type="text" id="${questionText}" name="${questionText}" required><br><br>`;
  }).join('');

  return questionsHtml;
}

function processForm(formData) {
  // 获取上传的文件Blob
  var fileBlob = formData.file;
  if (!fileBlob) return;

  // 替换为你的Drive文件夹ID，用于存储附件
  var folder = DriveApp.getFolderById("YOUR_FOLDER_ID");
  var file = folder.createFile(fileBlob);
  var fileUrl = file.getUrl();

  // 提取表单中的非文件字段
  var formValues = {};
  for (var key in formData) {
    if (key !== 'file') {
      formValues[key] = formData[key];
    }
  }

  // 处理Responses表格
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Responses');
    // 生成表头：表单字段 + 文件链接列
    var headers = Object.keys(formValues).concat(['File Link']);
    sheet.appendRow(headers);
  }

  // 组装行数据并插入表格
  var values = Object.values(formValues).concat([fileUrl]);
  sheet.appendRow(values);
}

HTML代码（Index.html） #

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      @import url('https://fonts.googleapis.com/css2?family=Google+Sans:wght@500&display=swap');

      body {
        font-family: 'Google Sans', sans-serif;
        background-color: #f1f3f4;
        color: #202124;
        margin: 0;
        padding: 0;
        display: flex;
        justify-content: center;
        align-items: center;
        height: 100vh;
      }

      .container {
        background: #ffffff;
        border-radius: 8px;
        box-shadow: 0 4px 8px rgba(0,0,0,0.1);
        padding: 20px;
        width: 100%;
        max-width: 600px;
        box-sizing: border-box;
        height: 80vh;
        display: flex;
        flex-direction: column;
      }

      h1 {
        color: #4285f4;
        font-size: 24px;
        margin-bottom: 20px;
        text-align: center;
      }

      label {
        font-weight: 500;
        color: #202124;
        display: block;
        margin-bottom: 8px;
      }

      select {
        font-size: 16px;
        padding: 8px;
        border-radius: 4px;
        border: 1px solid #ccc;
        width: 100%;
        box-sizing: border-box;
        margin-bottom: 20px;
        required: true;
      }

      .questions {
        flex: 1;
        overflow-y: auto;
        background: #ffffff;
        padding: 10px;
        border-radius: 4px;
        box-shadow: 0 0 10px rgba(0,0,0,0.1);
      }

      .questions label {
        margin-bottom: 10px;
        color: #202124;
        display: block;
      }

      .questions input {
        font-size: 16px;
        padding: 8px;
        border-radius: 4px;
        border: 1px solid #ccc;
        width: calc(100% - 16px);
        box-sizing: border-box;
        margin-bottom: 12px;
      }

      input[type="submit"] {
        background: #34a853;
        color: #ffffff;
        border: none;
        padding: 12px 20px;
        border-radius: 4px;
        cursor: pointer;
        font-size: 16px;
        transition: background 0.3s ease;
        width: 100%;
        box-sizing: border-box;
      }

      input[type="submit"]:hover {
        background: #2c8c3a;
      }

      input[type="file"] {
        margin-bottom: 20px;
      }
    </style>
  </head>
  <body>
    <div class="container">
      <h1>Job Intake Form</h1>
      <form id="myForm">
        <label for="team">选择团队:</label>
        <select id="team" name="team" required>
          <option value="">--请选择团队--</option>
          <option value="Team1">Team1</option>
          <option value="Team2">Team2</option>
          <option value="Team3">Team3</option>
        </select>

        <div id="questions" class="questions"></div>

        <label for="file">附件上传:</label>
        <input type="file" id="file" name="file" required>

        <input type="submit" value="提交">
      </form>
    </div>

    <script>
      document.getElementById('team').onchange = function() {
        var team = this.value;
        if (team) {
          google.script.run.withSuccessHandler(function(questionsHtml) {
            document.getElementById('questions').innerHTML = questionsHtml;
          }).getQuestionsForTeam(team);
        } else {
          document.getElementById('questions').innerHTML = '';
        }
      };

      document.getElementById('myForm').onsubmit = function(event) {
        event.preventDefault();
        var formData = new FormData(this);

        google.script.run.withSuccessHandler(function() {
          alert('表单提交成功！');
          // 提交后重置表单
          document.getElementById('myForm').reset();
          document.getElementById('questions').innerHTML = '';
        }).processForm(formData);
      };
    </script>
  </body>
</html>

关键修复与优化点 #

1. 附件上传逻辑修复：在processForm中单独提取文件Blob，排除表单中的file字段后再组装数据写入表格，避免Blob对象直接插入单元格导致的错误
2. 表头生成优化：确保表头仅包含实际的表单问题字段和"File Link"列，避免无效字段混入
3. 表单体验优化：给团队选择框添加必填验证，提交成功后自动重置表单并清空问题区域
4. 代码可读性提升：调整HTML模板字符串的写法，去掉转义字符，代码更易维护

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