如何在Google Apps Script中接收XLSM VBA传递的批量数据块？

核心思路 #

通过Excel VBA批量读取数据为二维数组，分块序列化为JSON后发送到Google Apps Script（GAS）部署的Web App，GAS接收后用setValues批量写入Google Sheets，大幅减少Drive API调用次数，提升同步效率。

1. Google Apps Script 接收端实现 #

首先在目标Google Sheets中编写接收函数并部署为Web App：

function doPost(e) {
  try {
    // 解析VBA发送的JSON数据
    const data = JSON.parse(e.postData.contents);
    const sheetId = "替换为你的Google Sheets文档ID";
    const sheetName = "替换为目标工作表名称";
    
    const ss = SpreadsheetApp.openById(sheetId);
    const targetSheet = ss.getSheetByName(sheetName);
    
    // 清空现有数据（全量同步需求）
    targetSheet.clearContents();
    
    // 批量写入数据块（setValues是单次API调用，效率极高）
    if (data.length > 0) {
      targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    }
    
    return ContentService.createTextOutput(JSON.stringify({status: "success"}))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (err) {
    return ContentService.createTextOutput(JSON.stringify({status: "error", msg: err.toString()}))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

部署Web App步骤 #

1. 打开目标Google Sheets → 点击「扩展程序」→「Apps 脚本」
2. 粘贴上述代码，替换sheetId和sheetName
3. 点击「发布」→「部署为Web应用」：
   • 版本：选择「新建」
   • 执行权限：选择「我（当前账号）」
   • 谁可以访问：选择「任何人，甚至匿名」（若数据敏感，需调整为「仅限我自己」并处理OAuth2认证）
4. 复制生成的Web App URL，后续VBA会用到

2. Excel VBA 发送端实现 #

在Excel中编写VBA代码，批量读取数据、分块发送：

Sub BatchSyncToGoogleSheets()
    Dim sourceWs As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim fullDataArr As Variant
    Dim chunkSize As Integer
    Dim startRow As Long, endRow As Long
    Dim chunkArr As Variant
    Dim jsonStr As String
    Dim httpObj As Object
    Dim webAppUrl As String
    
    ' 配置参数
    Set sourceWs = ThisWorkbook.Sheets("替换为你的Excel源工作表名称")
    chunkSize = 100 ' 每次同步行数，可根据网络/数据大小调整（建议50-200）
    webAppUrl = "替换为之前复制的Web App URL"
    
    ' 获取全量数据转为二维数组
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, 1).End(xlUp).Row
    lastCol = sourceWs.Cells(1, sourceWs.Columns.Count).End(xlToLeft).Column
    fullDataArr = sourceWs.Range(sourceWs.Cells(1, 1), sourceWs.Cells(lastRow, lastCol)).Value
    
    ' 初始化HTTP请求对象
    Set httpObj = CreateObject("MSXML2.XMLHTTP")
    
    ' 分块处理并发送
    For startRow = 1 To lastRow Step chunkSize
        endRow = Application.Min(startRow + chunkSize - 1, lastRow)
        
        ' 截取当前数据块
        ReDim chunkArr(1 To endRow - startRow + 1, 1 To lastCol)
        Dim r As Long, c As Long
        For r = startRow To endRow
            For c = 1 To lastCol
                chunkArr(r - startRow + 1, c) = fullDataArr(r, c)
            Next c
        Next r
        
        ' 转换为JSON字符串
        jsonStr = ArrayToJson(chunkArr)
        
        ' 发送POST请求
        With httpObj
            .Open "POST", webAppUrl, False
            .setRequestHeader "Content-Type", "application/json"
            .Send jsonStr
            
            ' 同步结果日志
            If .Status = 200 Then
                Debug.Print "块 " & startRow & "-" & endRow & " 同步成功"
            Else
                Debug.Print "块 " & startRow & "-" & endRow & " 同步失败，状态码：" & .Status
            End If
        End With
    Next startRow
    
    ' 清理对象
    Set httpObj = Nothing
    MsgBox "全量数据同步完成", vbInformation
End Sub

' 二维数组转JSON字符串（简化版，适配基本数据类型）
Function ArrayToJson(arr As Variant) As String
    Dim json As String
    Dim i As Long, j As Long
    json = "["
    For i = LBound(arr, 1) To UBound(arr, 1)
        json = json & "["
        For j = LBound(arr, 2) To UBound(arr, 2)
            Select Case VarType(arr(i, j))
                Case vbString
                    json = json & """" & Replace(arr(i, j), """", "\""") & """"
                Case vbEmpty
                    json = json & "null"
                Case Else
                    json = json & arr(i, j)
            End Select
            If j < UBound(arr, 2) Then json = json & ","
        Next j
        json = json & "]"
        If i < UBound(arr, 1) Then json = json & ","
    Next i
    json = json & "]"
    ArrayToJson = json
End Function

关键注意事项 #

• Chunk Size调整：若数据包含大量长文本，建议减小chunkSize（比如50），避免单请求体积超过Google的10MB限制；纯数字数据可增大到200+。
• 权限安全：若同步敏感数据，Web App不要设置为「匿名访问」，需在VBA中实现OAuth2认证（可参考GAS的OAuth2库）。
• 空值处理：VBA中的空单元格会转为Empty，上述ArrayToJson函数将其转为JSON的null，可根据需求调整为""（空字符串）。
• 错误重试：可在VBA中增加失败重试逻辑，应对临时网络波动。

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