如何用VBA删除谷歌表格中已批量上传的数据？

核心说明 #

你之前的代码是通过Google Forms的POST请求上传数据，这种方式仅支持新增数据，无法直接通过表单接口删除数据。要实现批量删除功能，必须借助Google Sheets API，并完成以下前置准备：

1. 启用Google Sheets API服务
2. 创建服务账号并下载JSON密钥文件
3. 给服务账号授予目标谷歌表格的编辑权限

VBA实现代码 #

首先需在VBA编辑器中启用Microsoft Scripting Runtime和Microsoft XML, v6.0两个引用（路径：工具 → 引用）。推荐搭配VBA-JSON和VBA-JWT开源库简化JSON解析与令牌生成逻辑。

Sub DeleteAllGoogleSheetData()
    Dim jsonPath As String, sheetId As String, targetRange As String
    Dim http As MSXML2.XMLHTTP60, authDict As Dictionary
    Dim accessToken As String, clearApiUrl As String
    
    ' 配置参数，替换为你的实际信息
    jsonPath = "C:\你的服务账号密钥文件.json"
    sheetId = "你的谷歌表格ID（从表格URL中提取）"
    targetRange = "Sheet1!A2:Z" ' 假设表头在第一行，仅删除数据行
    
    ' 获取API访问令牌
    Set http = New MSXML2.XMLHTTP60
    Set authDict = GetGoogleAuthToken(jsonPath, http)
    accessToken = authDict("access_token")
    
    ' 发送清空数据请求
    clearApiUrl = "https://sheets.googleapis.com/v4/spreadsheets/" & sheetId & "/values/" & targetRange & ":clear"
    With http
        .Open "POST", clearApiUrl, False
        .SetRequestHeader "Authorization", "Bearer " & accessToken
        .SetRequestHeader "Content-Type", "application/json"
        .Send "{}"
        
        If .Status = 200 Then
            MsgBox "数据删除成功！"
        Else
            MsgBox "删除失败：" & .responseText
        End If
    End With
    
    ' 释放对象
    Set http = Nothing
    Set authDict = Nothing
End Sub

' 获取Google API访问令牌（依赖VBA-JSON和VBA-JWT库）
Function GetGoogleAuthToken(jsonKeyPath As String, http As MSXML2.XMLHTTP60) As Dictionary
    Dim jsonContent As String, postPayload As String
    Dim keyDict As Dictionary, jwtToken As String
    
    ' 读取服务账号JSON密钥
    Open jsonKeyPath For Input As #1
    jsonContent = Input$(LOF(1), 1)
    Close #1
    
    ' 解析JSON密钥
    Set keyDict = JsonConverter.ParseJson(jsonContent)
    ' 生成JWT令牌
    jwtToken = JWT.CreateToken(keyDict("private_key"), keyDict("client_email"), "https://www.googleapis.com/auth/spreadsheets")
    
    ' 请求访问令牌
    postPayload = "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=" & jwtToken
    With http
        .Open "POST", "https://oauth2.googleapis.com/token", False
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .Send postPayload
        Set GetGoogleAuthToken = JsonConverter.ParseJson(.responseText)
    End With
End Function

关键注意事项 #

• 权限配置：必须确保服务账号已被添加为目标谷歌表格的编辑者，否则会返回权限错误。
• 范围准确性：targetRange需精准指定要删除的数据范围，避免误删表头或其他重要内容。
• 库依赖：手动实现JSON解析和JWT生成逻辑复杂度极高，强烈建议使用VBA-JSON和VBA-JWT开源库简化开发。

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