如何通过Excel VBA读取本地同步的Google表单关联GSheet数据 无需手动操作

以下两种方案均满足GDPR敏感数据管控要求、无手动转换格式、点击即可自动拉取数据的需求：

方案1：VBA调用Google Sheets API（推荐，稳定性最高） #

该方案无需依赖本地Google Drive同步，全程走私有认证链路，无需公开GSheet访问权限。

前置配置 #

• 进入Google Cloud控制台创建新项目，启用Google Sheets API
• 创建服务账号并生成JSON格式的本地密钥文件
• 打开目标GSheet的共享设置，仅将服务账号邮箱添加为查看者，无需开启公开访问

VBA实现步骤 #

1. 打开Excel VBA编辑器，引用Microsoft Scripting Runtime和Microsoft WinHTTP Services, version 5.1
2. 导入VBA-JSON解析模块到工程中，用于处理接口返回数据
3. 编写JWT生成逻辑，用本地存储的服务账号密钥生成访问令牌
4. 调用Google Sheets API拉取指定范围的数据，转换为数组后写入Excel指定工作表，直接供邮件发送模块调用
   核心代码示例：

' 拉取GSheet数据核心函数
Function PullGSheetData(gSheetID As String, dataRange As String) As Variant
    Dim httpReq As New WinHttp.WinHttpRequest
    Dim accessToken As String
    ' 调用JWT生成函数获取访问令牌，JWT生成逻辑可直接使用通用VBA JWT代码段
    accessToken = GenerateAccessTokenFromServiceAccountKey()
    
    httpReq.Open "GET", "https://sheets.googleapis.com/v4/spreadsheets/" & gSheetID & "/values/" & dataRange, False
    httpReq.SetRequestHeader "Authorization", "Bearer " & accessToken
    httpReq.Send
    
    Dim respJson As Object
    Set respJson = JsonConverter.ParseJson(httpReq.ResponseText)
    PullGSheetData = respJson("values")
End Function

' 按钮点击事件绑定
Sub PullDataBtn_Click()
    Dim rawData As Variant
    ' 替换为你的GSheet ID和要拉取的范围，例如"Sheet1!A:C"
    rawData = PullGSheetData("你的GSheetID", "表单回复!A:E")
    ' 将数据写入当前工作簿的临时数据表
    ThisWorkbook.Sheets("GSheet同步数据").Range("A1").Resize(UBound(rawData, 1), UBound(rawData, 2)).Value = rawData
    ' 自动调用你的邮件发送模块
    Call SendNotificationMails
End Sub

注意：服务账号密钥文件需存储在本地受信任路径，禁止对外分享，避免权限泄露。

方案2：基于本地Google Drive同步的本地读取方案 #

该方案全程数据读取在本地完成，无对外数据传输，适合不想调用云API的场景。

前置配置 #

• 打开Google Drive桌面端设置，勾选「启用Google文档、表格、幻灯片的离线编辑功能」
• 找到同步到本地的目标GSheet，右键勾选「离线可用」，等待同步完成

VBA实现步骤 #

1. 安装Google Drive ODBC驱动，配置本地数据源指向目标GSheet的本地缓存文件
2. 在VBA中通过ADODB连接ODBC数据源，执行SQL查询拉取所需数据
3. 将查询结果写入Excel工作表即可供邮件模块调用

该方案无需对GSheet做任何权限调整，完全沿用你现有同步逻辑，无额外数据泄露风险。

无感知运行配置 #

你可以根据需求将数据拉取逻辑绑定到按钮点击事件，或者配置为Excel打开时自动执行，无需使用人员做任何手动导入、格式转换操作。

内容的提问来源于stack exchange，提问作者Concúbháir O'Nuamain