如何在Excel查询数据前取消共享Google Sheet的筛选？

你当前使用的QueryTable通过tq接口获取谷歌表格数据时，接口默认返回表格的当前视图数据（包括筛选后的结果），无法直接通过Excel VBA的方法取消谷歌表格的筛选。下面提供两种可行的解决方案：

方案一：修改查询URL，强制获取全部数据 #

谷歌表格的tq查询接口支持添加&tq=select *参数，强制返回整张表的所有数据，不受筛选状态影响。直接修改URL构造逻辑即可：

Sub QueryGoogleSheets()
    Dim qt As QueryTable
    Dim url As String, key As String, gid As String
    Dim ws As Worksheet, lrow As Long
    Set ws = ThisWorkbook.Worksheets("Master")
    lrow = ws.UsedRange.Rows.Count

    ws.Range("A5:Q" & lrow).ClearContents
    key = "你的谷歌表格Key"
    gid = "目标工作表GID"
    ' 添加tq=select *参数强制获取全部数据
    url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key _
        & "&gid=" & gid & "&tq=select *"

    Set qt = ws.QueryTables.Add(Connection:="URL;" & url, Destination:=ws.Range("A5"))

    With qt
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
        ' 刷新后删除QueryTable，避免重复创建
        .Delete
    End With
End Sub

这个方案无需额外权限，仅修改URL参数即可实现需求，简单直接。

方案二：通过Google Sheets API先清除筛选再获取数据 #

如果需要确保谷歌表格本身的筛选被清除（不止是自己获取全量数据），可以使用Google Sheets API操作目标表格：

步骤1：前置配置 #

• 前往Google Cloud Console创建项目，启用Google Sheets API
• 创建服务账号密钥，下载JSON格式的凭据文件
• 给服务账号分配目标谷歌表格的编辑权限

步骤2：VBA代码实现 #

Sub ClearGoogleSheetFilterAndQuery()
    Dim ws As Worksheet, lrow As Long
    Dim apiUrl As String, serviceAccountKeyPath As String
    Dim authToken As String, jsonPayload As String
    
    Set ws = ThisWorkbook.Worksheets("Master")
    lrow = ws.UsedRange.Rows.Count
    ws.Range("A5:Q" & lrow).ClearContents
    
    ' 配置核心参数
    Dim spreadsheetId As String, sheetId As String
    spreadsheetId = "你的谷歌表格ID"
    sheetId = "目标工作表ID（即gid）"
    serviceAccountKeyPath = "C:\你的服务账号密钥文件.json"
    
    ' 1. 获取OAuth2访问令牌
    authToken = GetGoogleAuthToken(serviceAccountKeyPath, "https://www.googleapis.com/auth/spreadsheets")
    
    ' 2. 发送请求清除工作表筛选
    apiUrl = "https://sheets.googleapis.com/v4/spreadsheets/" & spreadsheetId & ":batchUpdate"
    jsonPayload = "{""requests"": [{""clearBasicFilter"": {""sheetId"": " & sheetId & "}}]}"
    
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "POST", apiUrl, False
        .SetRequestHeader "Authorization", "Bearer " & authToken
        .SetRequestHeader "Content-Type", "application/json"
        .Send jsonPayload
        If .Status <> 200 Then
            MsgBox "清除筛选失败：" & .ResponseText
            Exit Sub
        End If
    End With
    
    ' 3. 执行原有的数据查询逻辑
    QueryGoogleSheets ' 调用你原来的查询子程序
End Sub

' 获取Google服务账号的访问令牌（需依赖VBA-JSON模块解析JSON）
Function GetGoogleAuthToken(keyPath As String, scope As String) As String
    Dim jsonKey As String, tokenUrl As String
    Dim payload As String, response As String
    
    ' 读取服务账号密钥文件
    Open keyPath For Input As #1
    jsonKey = Input$(LOF(1), 1)
    Close #1
    
    tokenUrl = "https://oauth2.googleapis.com/token"
    payload = "grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=" & CreateJWT(jsonKey, scope)
    
    With CreateObject("MSXML2.XMLHTTP.6.0")
        .Open "POST", tokenUrl, False
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .Send payload
        response = .ResponseText
        GetGoogleAuthToken = ParseJson(response)("access_token")
    End With
End Function

说明：该方案需要添加VBA-JSON模块处理JSON解析，可通过VBA编辑器的“工具-引用”或导入模块实现。

方案对比 #

• 方案一：无需额外配置，仅获取全量数据，不改变谷歌表格的实际筛选状态
• 方案二：可清除谷歌表格的筛选，恢复默认视图，但需要配置API权限和依赖模块

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