时间戳

首页 > 解决方案 > VBA 从 Web API 解析数据

json - VBA 从 Web API 解析数据

问题描述

Public Sub IMPORTMESTER()

Dim xTOK As String
Dim URL As String
Dim httpREQ As Object
Dim JSON As Object
Dim xLINE As Variant

xTOK = "bdj62bzknriy3dd9g561on2xl2"

URL = "https://api.smartsheet.com/2.0/sheets/7352150637471620"

Set httpREQ = CreateObject("MSXML2.XMLHTTP.6.0")
With httpREQ
    .Open "GET", URL, False
    .setRequestHeader "Authorization", "Bearer " & xTOK
    .setRequestHeader "Content-Type", "application/json"
    .Send
End With

xLINE = httpREQ.ResponseText

MsgBox ("Complete!")

End Sub

所以，我返回了我需要的数据，但我尝试了几种方法来解析它并粘贴到 excel 中，但没有成功。这是响应文本的一部分：

"cells":[{"columnId":2400415921792900,"value":"MWP08","displayValue":"MWP08"},{"columnId":6904015549163396,"value":"A-WP-80301D5D10C00","displayValue":"A-WP-80301D5D10C00"},{"columnId":1274516014950276,"value":"MWP0830W27V50KD","displayValue":"MWP0830W27V50KD"},{"columnId":5778115642320772,"value":"WP08 30W,120-277VAC,Ra70 5000K Clear lens,Dark bronze","displayValue":"WP08 30W,120-277VAC,Ra70 5000K Clear lens,Dark bronze"},{"columnId":3526315828635524,"value":"image002.png","displayValue":"image002.png","formula":"=SYS_CELLIMAGE(\"image002.png\",\"vDOY-InMRamvhitNGotKzb\",35,52,\"image.png\")","image":{"id":"vDOY-InMRamvhitNGotKzb","height":35,"width":52,"altText":"image002.png"}},{"columnId":8029915456006020},{"columnId":711566061528964,"value":1884.0,"displayValue":"1884","linkInFromCell":{"status":"INACCESSIBLE","sheetId":4533800614029188,"rowId":null,"columnId":null,"sheetName":"MLC-Inventory扣减(2019)"}},{"columnId":2963365875214212,"value":"https://mesterleds.com/wp-content/uploads/2017/12/WP01-45W70W.png","displayValue":"https://mesterleds.com/wp-content/uploads/2017/12/WP01-45W70W.png"},{"columnId":7466965502584708},{"columnId":1837465968371588},{"columnId":6341065595742084},{"columnId":4089265782056836},{"columnId":8592865409427332},{"columnId":430091084818308,"value":175.0,"displayValue":"175"},{"columnId":4933690712188804},{"columnId":2681890898503556},{"columnId":7185490525874052},{"columnId":1555990991660932},{"columnId":6059590619031428}]},{"id":7080298036914052,"rowNumber":3,"siblingId":2576698409543556,"expanded":true,"createdAt":"2019-01-31T00:06:35Z","modifiedAt":"2019-02-18T16:56:50Z",

我需要的每一行表格都以："cells';[{"而我只需要"displayValue":列！

我从 StackOverflow 的各种线程中尝试了几种解决方案和建议，但是......没有运气！

下面是所需的输出： 最终的 excel 格式（隐藏了不必要的列）

标签： jsonexcelvbaweb-scrapingsmartsheet-api-2.0

解决方案

如果仅在 displayValue 之后，您可以将以下内容与jsonconverter.bas一起使用。您将 .bas 添加到您的项目中，然后 VBE > 工具 > 参考 > 添加对 Microsoft Scripting Runtime 的引用。

Option Explicit

Public Sub IMPORTMESTER()

    Dim xTOK As String
    Dim URL As String
    Dim httpREQ As Object
    Dim json As Object
    Dim xLINE As Variant

    xTOK = "token"
    URL = "https://api.smartsheet.com/2.0/sheets/7352150637471620"

    Set httpREQ = CreateObject("MSXML2.XMLHTTP.6.0")
    With httpREQ
        .Open "GET", URL, False
        .setRequestHeader "Authorization", "Bearer " & xTOK
        .setRequestHeader "Content-Type", "application/json"
        .send
    End With

    xLINE = httpREQ.responseText
    Set json = JsonConverter.ParseJson(xLINE)("rows")
    Dim item As Object, nextitem As Object, i As Long
    For Each item In json
       For Each nextitem In item("cells")
            i = i + 1
           ActiveSheet.Cells(i, 1) = nextitem("displayValue")
        Next
    Next
End Sub

您想要的项目嵌套在 json 中，其中 {} 是字典，[] 是集合。

推荐阅读