Power Query M中如何处理If-Modified-Since响应头及相关问题

问题1：获取Last-Modified响应头 #

Power Query默认调用Web.Contents时不会返回完整响应头，需启用手动响应处理模式才能拿到包含Last-Modified在内的所有响应头。具体实现如下：

let
    // 读取上次缓存的时间戳（可存储在Excel单元格中）
    LastCachedTime = Excel.CurrentWorkbook(){[Name="LastModifiedTimestamp"]}[Content]{0}[Column1],
    // 构造请求头，带上If-Modified-Since
    RequestHeaders = if LastCachedTime <> null then 
        [
            #"If-Modified-Since" = DateTime.ToText(LastCachedTime, "R")
        ] 
        else [],
    // 启用手动处理调用API
    Response = Web.Contents("https://your-api-url.com/data", [
        Headers = RequestHeaders,
        ManualHandling = true
    ]),
    // 从响应头提取Last-Modified
    ResponseHeaders = Response[Headers],
    NewLastModified = if Record.HasFields(ResponseHeaders, "Last-Modified") then 
        DateTime.FromText(ResponseHeaders[#"Last-Modified"], "R") 
        else null,
    // 更新Excel中的缓存时间戳
    UpdateTimestamp = Excel.CurrentWorkbook(){[Name="LastModifiedTimestamp"]}[Content]{0}[Column1] = NewLastModified,
    // 仅在状态码200时解析响应体
    Data = if Response[StatusCode] = 200 then Json.Document(Response[Content]) else null
in
    Data

说明：Response[Headers]会返回所有响应头字段，而Value.Metadata(response)仅返回元数据子集，因此必须通过手动处理模式获取完整头信息。

问题2：优雅处理304 Not Modified响应 #

Power Query内部处理 #

在手动处理模式下，API返回304时Response[StatusCode]会直接等于304，不会抛出错误。可通过状态码分支逻辑返回本地缓存数据：

let
    // 读取本地缓存数据（存储在Excel隐藏工作表）
    CachedData = Excel.CurrentWorkbook(){[Name="CachedDataTable"]}[Content],
    // 执行请求逻辑
    LastCachedTime = Excel.CurrentWorkbook(){[Name="LastModifiedTimestamp"]}[Content]{0}[Column1],
    RequestHeaders = if LastCachedTime <> null then [ #"If-Modified-Since" = DateTime.ToText(LastCachedTime, "R") ] else [],
    Response = Web.Contents("https://your-api-url.com/data", [ Headers = RequestHeaders, ManualHandling = true ]),
    // 根据状态码处理结果
    Result = if Response[StatusCode] = 200 then
        let
            NewData = Json.Document(Response[Content]),
            // 更新缓存数据到工作表
            UpdateCache = Excel.CurrentWorkbook(){[Name="CachedDataTable"]}[Content] = NewData,
            // 更新时间戳
            UpdateTimestamp = Excel.CurrentWorkbook(){[Name="LastModifiedTimestamp"]}[Content]{0}[Column1] = Response[Headers][#"Last-Modified"]
        in
            NewData
        else if Response[StatusCode] = 304 then
            // 返回缓存数据，避免报错
            CachedData
        else
            // 处理其他错误状态码
            error "API请求失败，状态码: " & Text.From(Response[StatusCode])
in
    Result

VBA外部处理 #

不要依赖错误描述关键词，而是让Power Query返回带状态标识的结构，再在VBA中判断：

1. 调整Power Query返回结构：

Result = if Response[StatusCode] = 200 then
    [Status = "Updated", Data = NewData]
else if Response[StatusCode] = 304 then
    [Status = "Cached", Data = CachedData]
else
    [Status = "Error", Message = "API请求失败，状态码: " & Text.From(Response[StatusCode])]

2. VBA中读取状态并处理：

Sub RefreshAPIQuery()
    Dim qry As WorkbookQuery
    Dim statusVal As String
    
    Set qry = ThisWorkbook.Queries("YourQueryName")
    qry.Refresh
    
    ' 读取返回的状态标识
    statusVal = ThisWorkbook.Worksheets("ResultSheet").ListObjects("ResultTable").ListColumns("Status").DataBodyRange(1).Value
    
    Select Case statusVal
        Case "Updated"
            MsgBox "数据已更新"
        Case "Cached"
            MsgBox "数据未变化，使用缓存"
        Case "Error"
            Dim errMsg As String
            errMsg = ThisWorkbook.Worksheets("ResultSheet").ListObjects("ResultTable").ListColumns("Message").DataBodyRange(1).Value
            MsgBox "请求错误: " & errMsg
    End Select
End Sub

若需捕获异常，可直接检查查询的Error属性而非错误描述：

Sub SafeRefresh()
    Dim qry As WorkbookQuery
    Set qry = ThisWorkbook.Queries("YourQueryName")
    
    On Error Resume Next
    qry.Refresh
    On Error GoTo 0
    
    If qry.Error Is Not Nothing Then
        If InStr(qry.Error.Message, "304") > 0 Then
            MsgBox "数据未更新，使用缓存"
        Else
            MsgBox "请求错误: " & qry.Error.Message
        End If
    Else
        MsgBox "数据刷新完成"
    End If
End Sub

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