HTML表单文件上传至Google Sheet/Drive功能问题求助

架构老林

2026-7-23

问题：HTML表单文件上传无法对接Google Sheet，需实现图片存Drive并在Sheet生成链接

我制作了一款需用户上传文件及填写其他信息的HTML表单，已成功将表单与Google Sheet关联，但文件上传部分无法同步至Sheet。希望实现上传的图片存入Google Drive并在Sheet中生成对应链接。以下是当前使用的代码：

当前App Script代码

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

当前HTML表单代码

<div class="containers">
  <form action="#" class="form" name="submit-to-google-sheet">
    <div class="input-box">
      <h4>Personal Info</h4>
      <input name="Full name" type="text" placeholder="Full name" required />
    </div>
    <div class="input-box">
      <input name="Phone no" type="number" placeholder="Phone no" required />
    </div>

    <div class="input-box address">
      <div class="column">
        <input name="State" type="text" placeholder="State" required />
        <input name="City" type="text" placeholder="City" required />
      </div>
    </div>
    <div class="input-box address">
      <div class="column">
        <input name="Email" type="text" placeholder="Email" required />
        <input name="Linkedin" type="text" placeholder="Linkedin" />
      </div>
    </div>
    <br />
    <h4>Profession</h4>

    <div class="options">
      <input name="option" type="radio" id="click" class="hide" checked />
      <label for="click">Student</label>

      <input name="option" type="radio" id="touch" class="hide" />
      <label for="touch">Working Professional</label>
    </div>
    <div class="input-box pro">
      <input name="University/Company Name" type="text" placeholder="University/Company Name" required />
    </div>

    <div class="input-box address">
      <h4>About Your Pitch</h4>
      <input name="Website" type="text" placeholder="Website" />
      <input name="Linkedin of Company" type="text" placeholder="Linkedin of Company" />
    </div>
    <div class="input-box address">
      <input name="Idea" type="text" placeholder="Idea" required />
    </div>

    <br />
    <label for="images" class="drop-container">
      <div class="info-icon">
        <i id="mytext" class="fa-solid fa-circle-info" data-toggle="popover" data-trigger="hover" data-placement="bottom" data-content="Upload screenshot of you website or any product"></i>
      </div>

      <span class="drop-title">Drop files here</span>

      or
      <input type="file" name="Files" id="images" accept="image/*" required />
    </label>

    <button type="submit">Submit</button>
  </form>
  <script>
    const scriptURL = "https://docs.google.com/spreadsheets/d/1jRX-yIQ5FUaZ3_zhZaetbkrAJTptD5riF-Qy1vt94ZI/edit#gid=0";
    const form = document.forms["submit-to-google-sheet"];

    form.addEventListener("submit", (e) => {
      e.preventDefault();
      fetch(scriptURL, {method: "POST", body: new FormData(form)})
        .then((response) => console.log("Success!", response))
        .catch((error) => console.error("Error!", error.message));
    });
  </script>
</div>

解决方案：实现文件上传至Google Drive并记录链接

1. 修改App Script代码

核心逻辑：解析表单提交的文件内容，上传到指定Drive文件夹，将文件链接写入Sheet对应列。

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
// 替换为你的Google Drive文件夹ID（从文件夹URL中提取）
var FOLDER_ID = "你的Drive文件夹ID";

function initialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1
    var newRow = new Array(headers.length).fill('')
    var fileUrl = "";

    // 处理多部分表单数据（含文件）
    if (e.postData && e.postData.type === 'multipart/form-data') {
      var boundary = e.postData.contents.split('\n')[0];
      var parts = e.postData.contents.split(boundary);
      
      parts.forEach(function(part) {
        // 处理文件字段
        if (part.includes('Content-Disposition') && part.includes('filename')) {
          var filenameMatch = part.match(/filename="(.*?)"/);
          var filename = filenameMatch ? filenameMatch[1] : 'unknown_image';
          // 提取文件二进制内容
          var contentStart = part.indexOf('\r\n\r\n') + 4;
          var contentEnd = part.lastIndexOf('\r\n--');
          var fileContent = part.substring(contentStart, contentEnd);
          // 转成Blob并上传到Drive
          var blob = Utilities.newBlob(Utilities.base64Decode(fileContent), 'image/*', filename);
          var folder = DriveApp.getFolderById(FOLDER_ID);
          var file = folder.createFile(blob);
          // 设置文件可公开查看（可根据需求调整权限）
          file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
          fileUrl = file.getUrl();
        } 
        // 处理普通文本字段
        else if (part.includes('Content-Disposition') && part.includes('name=')) {
          var nameMatch = part.match(/name="(.*?)"/);
          var fieldName = nameMatch ? nameMatch[1] : '';
          var valueStart = part.indexOf('\r\n\r\n') + 4;
          var valueEnd = part.lastIndexOf('\r\n--');
          var fieldValue = part.substring(valueStart, valueEnd).trim();
          
          var headerIndex = headers.indexOf(fieldName);
          if (headerIndex !== -1) {
            newRow[headerIndex] = fieldValue;
          }
        }
      });
    }

    // 填充时间戳
    var timestampIndex = headers.indexOf('timestamp');
    if (timestampIndex !== -1) {
      newRow[timestampIndex] = new Date();
    }

    // 填充文件链接到指定列（需Sheet表头包含"Files"）
    var filesIndex = headers.indexOf('Files');
    if (filesIndex !== -1) {
      newRow[filesIndex] = fileUrl;
    }

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow, 'fileUrl': fileUrl }))
      .setMimeType(ContentService.MimeType.JSON)
  } catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e.toString() }))
      .setMimeType(ContentService.MimeType.JSON)
  } finally {
    lock.releaseLock()
  }
}

注意事项：

首次运行initialSetup函数，完成授权配置
需确保Sheet表头包含Files列，用于存储文件链接
替换FOLDER_ID为你的目标Drive文件夹ID

2. 调整HTML表单代码

核心修改：将提交URL替换为App Script部署后的Web App地址，优化提交反馈。

<div class="containers">
  <form action="#" class="form" name="submit-to-google-sheet">
    <!-- 原有表单字段保持不变 -->
    <div class="input-box">
      <h4>Personal Info</h4>
      <input name="Full name" type="text" placeholder="Full name" required />
    </div>
    <div class="input-box">
      <input name="Phone no" type="number" placeholder="Phone no" required />
    </div>
    <div class="input-box address">
      <div class="column">
        <input name="State" type="text" placeholder="State" required />
        <input name="City" type="text" placeholder="City" required />
      </div>
    </div>
    <div class="input-box address">
      <div class="column">
        <input name="Email" type="text" placeholder="Email" required />
        <input name="Linkedin" type="text" placeholder="Linkedin" />
      </div>
    </div>
    <br />
    <h4>Profession</h4>
    <div class="options">
      <input name="option" type="radio" id="click" class="hide" checked />
      <label for="click">Student</label>
      <input name="option" type="radio" id="touch" class="hide" />
      <label for="touch">Working Professional</label>
    </div>
    <div class="input-box pro">
      <input name="University/Company Name" type="text" placeholder="University/Company Name" required />
    </div>
    <div class="input-box address">
      <h4>About Your Pitch</h4>
      <input name="Website" type="text" placeholder="Website" />
      <input name="Linkedin of Company" type="text" placeholder="Linkedin of Company" />
    </div>
    <div class="input-box address">
      <input name="Idea" type="text" placeholder="Idea" required />
    </div>
    <br />
    <label for="images" class="drop-container">
      <div class="info-icon">
        <i id="mytext" class="fa-solid fa-circle-info" data-toggle="popover" data-trigger="hover" data-placement="bottom" data-content="Upload screenshot of you website or any product"></i>
      </div>
      <span class="drop-title">Drop files here</span>
      or
      <input type="file" name="Files" id="images" accept="image/*" required />
    </label>
    <button type="submit">Submit</button>
  </form>
  <script>
    // 替换为你的App Script部署后的Web App URL
    const scriptURL = "你的Web App部署地址";
    const form = document.forms["submit-to-google-sheet"];

    form.addEventListener("submit", (e) => {
      e.preventDefault();
      fetch(scriptURL, { method: "POST", body: new FormData(form) })
        .then(response => response.json())
        .then(data => {
          console.log("Success!", data);
          alert("提交成功！文件链接：" + data.fileUrl);
          form.reset();
        })
        .catch(error => console.error("Error!", error.message));
    });
  </script>
</div>

注意事项：

部署App Script为Web App：打开脚本编辑器→右上角「部署」→「新部署」→类型选「Web应用」→执行人为「我」→访问权限选「任何人，甚至匿名」→部署后复制生成的URL替换scriptURL
确保表单中文件字段的name属性为Files，与Sheet表头对应

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

HTML表单文件上传至Google Sheet/Drive功能问题求助

当前App Script代码 #

当前HTML表单代码 #

解决方案：实现文件上传至Google Drive并记录链接 #

1. 修改App Script代码 #

2. 调整HTML表单代码 #

当前App Script代码

当前HTML表单代码

解决方案：实现文件上传至Google Drive并记录链接

1. 修改App Script代码

2. 调整HTML表单代码