Excel循环调用Torn API获取全量数据的技术求助

要解决Excel里调用Torn API获取全量数据的问题，不用VBScript，靠Power Query的M代码或者Excel VBA就能实现，两种方案如下：

首先得确认Torn API的分页规则——这类日志接口一般支持page页码参数，或者按时间戳范围（from/to）分批拉取。以下是两种常见场景的实现代码：

场景1：API支持page参数 #

直接生成页码列表，循环调用每页数据并合并：

let
    // 替换成你的真实API密钥
    apiKey = "fullaccesskeyhere",
    baseUrl = "https://api.torn.com/user/?selections=log&log=8395,8396&comment=TornAPI&key=" & apiKey,
    // 定义获取单页数据的函数
    getSinglePage = (pageNum as number) =>
        let
            requestUrl = baseUrl & "&page=" & Text.From(pageNum),
            rawJson = Json.Document(Web.Contents(requestUrl)),
            logRecords = rawJson[log],
            toTable = Record.ToTable(logRecords),
            expandValue = Table.ExpandRecordColumn(toTable, "Value", {"log", "title", "timestamp", "category", "data"}, {"log", "title", "timestamp", "category", "data"}),
            expandData = Table.ExpandRecordColumn(expandValue, "data", {"opponent", "pot"}, {"opponent", "pot"})
        in
            expandData,
    // 循环拉取数据直到返回空表
    pageDataList = List.Generate(
        () => [Page = 1, Data = getSinglePage(1)],
        each Table.RowCount([Data]) > 0,
        each [Page = [Page] + 1, Data = getSinglePage([Page] + 1)],
        each [Data]
    ),
    // 合并所有页面数据
    fullData = Table.Combine(pageDataList)
in
    fullData

场景2：API仅支持时间范围分页 #

如果API不支持页码，可通过日志的timestamp字段分批拉取（每次取上一页最早时间之前的数据）：

let
    apiKey = "fullaccesskeyhere",
    baseUrl = "https://api.torn.com/user/?selections=log&log=8395,8396&comment=TornAPI&key=" & apiKey,
    // 定义按时间范围获取数据的函数
    getPageByTime = (endTime as nullable number) =>
        let
            requestUrl = if endTime = null then baseUrl else baseUrl & "&to=" & Text.From(endTime),
            rawJson = Json.Document(Web.Contents(requestUrl)),
            logRecords = rawJson[log],
            toTable = Record.ToTable(logRecords),
            expandValue = Table.ExpandRecordColumn(toTable, "Value", {"log", "title", "timestamp", "category", "data"}, {"log", "title", "timestamp", "category", "data"}),
            expandData = Table.ExpandRecordColumn(expandValue, "data", {"opponent", "pot"}, {"opponent", "pot"}),
            // 获取当前页最早的时间戳，用于下一页的截止时间
            minTime = if Table.RowCount(expandData) > 0 then List.Min(expandData[timestamp]) else null
        in
            [Data = expandData, NextEndTime = minTime],
    // 循环拉取直到无数据
    dataList = List.Generate(
        () => getPageByTime(null),
        each Table.RowCount([Data]) > 0,
        each getPageByTime([NextEndTime] - 1), // 减1避免重复抓取同一条数据
        each [Data]
    ),
    fullData = Table.Combine(dataList)
in
    fullData

如果Power Query的逻辑不够灵活（比如需要更精细的错误处理或频率控制），用VBA实现更直接：

Sub GetFullTornLogs()
    Dim apiKey As String, baseUrl As String
    Dim page As Integer, lastRow As Long
    Dim response As String
    Dim jsonObj As Object, logDict As Object, logItem As Variant
    Dim ws As Worksheet
    
    ' 配置基础参数
    apiKey = "fullaccesskeyhere"
    baseUrl = "https://api.torn.com/user/?selections=log&log=8395,8396&comment=TornAPI&key=" & apiKey
    Set ws = ThisWorkbook.Sheets("日志数据") ' 替换成你的工作表名
    page = 1
    lastRow = 2
    
    ' 写入表头
    ws.Range("A1:F1").Value = Array("日志ID", "标题", "时间戳", "分类", "对手", "奖池")
    
    Do
        ' 构建请求URL
        Dim reqUrl As String
        reqUrl = baseUrl & "&page=" & page
        
        ' 发送HTTP请求
        With CreateObject("MSXML2.XMLHTTP")
            .Open "GET", reqUrl, False
            .Send
            response = .responseText
        End With
        
        ' 解析JSON（需要先导入VBA-JSON模块）
        Set jsonObj = JsonConverter.ParseJson(response)
        Set logDict = jsonObj("log")
        
        ' 无数据时退出循环
        If logDict.Count = 0 Then Exit Do
        
        ' 写入数据到工作表
        For Each logItem In logDict
            ws.Cells(lastRow, 1) = logItem("log")
            ws.Cells(lastRow, 2) = logItem("title")
            ws.Cells(lastRow, 3) = logItem("timestamp")
            ws.Cells(lastRow, 4) = logItem("category")
            ws.Cells(lastRow, 5) = logItem("data")("opponent")
            ws.Cells(lastRow, 6) = logItem("data")("pot")
            lastRow = lastRow + 1
        Next
        
        page = page + 1
        ' 加1秒延迟，避免触发API频率限制
        Application.Wait Now + TimeValue("00:00:01")
    Loop
End Sub

注意：VBA需要导入VBA-JSON模块（可在VBA编辑器中通过导入.bas文件实现），同时要启用宏并注意API的调用频率限制。

1. 先查Torn API官方文档确认分页参数：上述代码是基于通用分页逻辑的示例，要根据实际API支持的参数调整。
2. 严格遵守API频率限制：Torn API有调用次数上限，循环时必须加延迟，否则可能被临时封禁。
3. 加错误处理：可以在代码中加入On Error Resume Next或On Error GoTo逻辑，处理网络故障、密钥无效等异常情况。

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