Excel VBA Userform向谷歌表格传输数据无法定位最后已用行求助

你当前采用谷歌表单提交的方式写入谷歌表格，该机制本身默认会自动将新提交的内容追加到响应表的最后一行，不需要额外手动获取最后已用行。出现数据写入位置随机的问题，核心原因有三个：

• 谷歌响应表开启了手动筛选/排序，导致新提交的内容显示位置错乱
• VBA循环提交多个商品时请求发送过快，触发谷歌接口限流，部分请求顺序紊乱
• URL拼接时存在多余空格，导致部分请求参数异常，写入逻辑出错

方案1：修复现有逻辑（无需改动架构，成本最低） #

1. 处理谷歌表格配置：打开绑定该表单的谷歌表格，点击顶部「数据」→「关闭筛选」，再点击「数据」→「排序范围」，勾选「数据包含标题行」，按「提交时间」列升序排序即可，后续新提交内容会自动追加到最后一行，显示顺序正常。
2. 新增请求延迟避免限流：在xmlHTTP.send代码后新增一行VBA.Sleep 1000，每次提交后暂停1秒，防止并发请求导致顺序错乱。
3. 修正URL拼接错误：删除所有参数拼接位置的多余空格，修正后的完整代码如下：

With Inventoryform
    phone = .Textphonenumber
    lastname = .Textlastname
    name = .TextfirstName
    email = .Textemail
    time = .Textcontacttime
    add1 = .Textaddress1
    add2 = .Textaddress2
    add3 = .Textaddress3
    city = .Textcity
    state = .Textstate
    zip = .Textzip
    country = .Textcountry

    For p = 1 To .ListBox1.ListCount - 1
        pname = .ListBox1.List(p, 0)
        pclass = .ListBox1.List(p, 1)
        pdiscreption = .ListBox1.List(p, 2)
        serial = .ListBox1.List(p, 3)
        store = .ListBox1.List(p, 4)
        qty = .ListBox1.List(p, 5)
        price = .ListBox1.List(p, 6)

        xmlHTTPHeader = "content-type"
        xmlID = "application/x-www.form-urlencoded;charset=utf-8"
        URL_first = "https://docs.google.com/forms/d/e/1FAIpQLSe1_FVJjoFUW_SKYr0ttKK_a5FBLPMWgGCvEKNFAXV4T_RJNg/formResponse?ifq"
        ' 已删除所有拼接位置的多余空格
        URL_seccond = "&entry.402760373=" & name & "&entry.8402683=" & lastname & "&entry.2013589844=" & phone & "&entry.952452731=" & time & "&entry.48164907=" & email & "&entry.1356643996=" & zip & "&entry.2002379396=" & add1 & "&entry.1231724023=" & add2 & "&entry.421644231=" & add3 & "&entry.1747336923=" & city & "&entry.1613048973=" & state & "&entry.875063098=" & country & "&entry.319273916=" & pname & "&entry.158999870=" & pclass & "&entry.1321582544=" & pdiscreption & "&entry.418874757=" & serial & "&entry.1230323140=" & store & "&entry.2123202381=" & price & "&entry.1785381471=" & qty & "&submit=Submit"
        googlesheeturl = URL_first & URL_seccond
        Set xmlHTTP = New ServerXMLHTTP60
        xmlHTTP.Open "post", googlesheeturl, False
        xmlHTTP.setRequestHeader xmlHTTPHeader, xmlID
        xmlHTTP.send
        ' 新增延迟避免限流
        VBA.Sleep 1000
    Next p
End With

方案2：自主控制写入位置（可控性更高） #

如果需要完全自定义写入逻辑，避免谷歌表单机制的限制，可以通过谷歌Apps Script部署私有接口实现：

1. 打开目标谷歌表格，点击顶部「扩展程序」→「Apps Script」，输入以下代码：

function doPost(e) {
  // 替换为你的目标工作表名称
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("响应表");
  // 自动获取最后已用行的下一行作为写入起始行
  const writeRow = targetSheet.getLastRow() + 1;
  const reqParams = e.parameter;
  // 按你的字段存储顺序依次写入对应列，列序号从左到右从1开始计数
  targetSheet.getRange(writeRow, 1).setValue(reqParams.name);
  targetSheet.getRange(writeRow, 2).setValue(reqParams.lastname);
  targetSheet.getRange(writeRow, 3).setValue(reqParams.phone);
  // 剩余字段按你需要的存储顺序补充即可
  return ContentService.createTextOutput("ok").setMimeType(ContentService.MimeType.TEXT);
}

2. 点击右上角「部署」→「新建部署」，选择部署类型为「Web应用」，执行身份设置为自己，访问权限设置为所有人（含匿名用户），部署完成后复制生成的接口地址。
3. 替换VBA代码中的URL_first为上述生成的接口地址，参数按你在Apps Script中定义的字段名拼接即可，每次写入都会自动定位到最新空白行，不会覆盖原有数据。

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