Excel VBA WebQuery导入Google Sheets时部分值未获取问题

使用VBA QueryTable方式将Google Sheets数据导入Excel时，若相邻单元格存在数字、文本等混合数据类型，会出现部分值无法正常读取的情况。运行代码完成数据填充后，将导入结果和Google Sheets原始数据、接口返回的网页端数据对比，可发现存在明显的信息缺失。

Sub QueryGoogleSheets()
      Dim qt As QueryTable, url As String, key As String, gid As String
 
      If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
      ActiveSheet.Cells.Clear
 
      key = "1_27rjNQmlXrwVKpLWUbGrJYPJufGRa7Dk-XEKcNAHr0"
      gid = "1628955556"
      url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key _
      & "&gid=" & gid
 
       
      Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & url, _
      Destination:=Range("a1"))
 
      With qt
          .WebSelectionType = xlAllTables
          .WebFormatting = xlWebFormattingNone
          .Refresh
      End With
  End Sub

这个问题和Google Sheets接口无关，直接访问接口地址可以看到完整返回数据，问题完全出在Excel QueryTable的默认解析逻辑上：

• 核心诱因是QueryTable抓取HTML表格时的自动类型推断机制缺陷：它只会扫描每一列前几行的内容来判定整列的数据类型，一旦判定某列为数值/日期类型，同列后面所有不符合这个类型的文本、特殊格式内容都会被直接判定为无效值丢弃，根本不会写入单元格。源表存在大量同列混合数字、文本、空值的情况，自然会出现大量值丢失。
• 代码中使用的xlAllTables抓取模式，对Google Sheets生成的嵌套HTML表格结构兼容度很差，混合数据类型场景下解析容错率极低，会进一步加剧数据丢失的问题。

方案1：改动最小的快速修复 #

不用调整整体逻辑，只要在原有QueryTable配置项中添加参数，强制关闭自动类型推断，让所有内容按纯文本导入，导入完成后再手动调整单元格格式即可，修改后的核心配置段如下：

With qt
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .PreserveFormatting = True
    ' 强制所有列按文本格式读取，跳过自动类型检测
    .TextFileColumnDataTypes = Array(xlTextFormat)
    .Refresh
End With

方案2：稳定性更高的长期方案 #

放弃HTML格式抓取逻辑，将请求URL里的out:html改为out:csv，直接读取CSV格式的返回结果导入。CSV是纯结构化文本，不存在HTML解析的兼容问题，也不会触发QueryTable的类型推断逻辑，只要做好UTF-8编码适配，基本不会出现丢值、乱码问题，稳定性远高于HTML抓取方式。

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