502 Bad Gateway错误尝试使用VBA向ANEEL API发出请求时。

huangapple
go评论72阅读模式
英文:

502 Bad Gateway Error when trying to make a request to ANEEL API using VBA

问题

你在Excel VBA宏中遇到502 Bad Gateway错误是因为API请求未能成功。这个错误通常表示服务器无法提供有效的响应。你可以尝试以下方法来解决这个问题：

  1. 检查URL: 确保你的API请求URL没有拼写错误，并且与在浏览器中测试的URL一致。

  2. 网络连接: 确保你的计算机具有可靠的网络连接，能够访问API服务器。

  3. 代理设置: 如果你需要通过代理服务器访问互联网，确保你的Excel VBA宏已配置正确的代理设置。

  4. API服务器状态: 502 Bad Gateway错误可能是API服务器问题。检查API服务器是否正常运行，并且没有计划的维护。

  5. API密钥: 如果API需要密钥或授权，请确保你已正确设置并传递了API密钥。

  6. 超时设置: 在VBA宏中，可以设置HTTP请求的超时时间。如果API响应时间较长，你可能需要增加超时时间。

  7. 错误处理: 在VBA宏中，添加适当的错误处理机制，以便更好地捕获和处理API请求的错误。这有助于你更容易诊断问题。

在确认了这些方面后，如果问题仍然存在，你可能需要联系API提供者以获取更多支持和帮助，以确保你的请求能够正常工作。

英文:

I'm having an issue with the ANEEL API query through a macro in an Excel spreadsheet. Although I can successfully make the request in a browser, I'm unable to write some fields in the Excel table due to a 502 Bad Gateway error. I'm investigating what might be causing this issue.

I'm trying to create a VBA macro to make a request to the ANEEL API and retrieve the desired data. I've written the following function:

Sub ConsultAPI()
    Dim xmlhttp As Object
    Dim url As String
    Dim response As String
    Dim json As Object
    Dim utilityCompany As String
    Dim tariffGroup As String

    ' Get the utility company name from the desired cell
    utilityCompany = Sheets("Start").Range("B21").Value
    
    ' Get the tariff group
    tariffGroup = Sheets("Start").Range("B27").Value
    
    ' Get the tariff class
    tariffClass = Sheets("Start").Range("B19").Value
    
    ' Get the value from cell B20 as the filter for subclass
    Dim subclassFilter As String
    subclassFilter = Sheets("Start").Range("B20").Value
    
    ' Replace whitespace with %20 in the utility company string
    utilityCompany = Replace(utilityCompany, " ", "%20")
    
    ' Replace whitespace with %20 in the tariff group string
    tariffGroup = Replace(tariffGroup, " ", "%20")
    
    ' Replace whitespace with %20 in the class (DscClasse) string
    tariffClass = Replace(tariffClass, " ", "%20")
    
    ' Replace whitespace with %20 in the subclass string
    subclassFilter = Replace(subclassFilter, " ", "%20")
    
    ' Add a filter for the DscSubClasse field in the URL
    Dim filterDscSubClasse As String
    filterDscSubClasse = "%20AND%20%22DscSubClasse%22%20%3D%20%27" & subclassFilter & "%27"
    
    ' Add a filter for the DscModalidadeTarifaria field in the URL
    Dim filterDscModalidadeTarifaria As String
    filterDscModalidadeTarifaria = "%20AND%20%22DscModalidadeTarifaria%22%20%3D%20%27Convencional%27"
    
    ' Add the filter for the NomPostoTarifario field in the URL
    url = "https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%22fcf2906c-7c32-4b9b-a637-054e7a5234f4%22%20WHERE%20%22SigAgente%22%20%3D%20%27" & utilityCompany & "%27%20AND%20%22DscSubGrupo%22%20%3D%20%27" & tariffGroup & "%27%20AND%20%22DscClasse%22%20%3D%20%27" & tariffClass & "%27%20AND%20%22SigAgenteAcessante%22%20IN%20(%27NA%27,%20%27N%C3%A3o%20se%20aplica%27)%20AND%20%22DscBaseTarifaria%22%20%3D%20%27Tarifa%20de%20Aplica%C3%A7%C3%A3o%27" & filterDscSubClasse & filterDscModalidadeTarifaria & "%20AND%20%22NomPostoTarifario%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20ORDER%20BY%20%22DatInicioVigencia%22%20DESC%20LIMIT%201"

    ' Save the request details before making the GET request
    SaveRequestDetails url

    ' Initialize the xmlhttp object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    
    ' Make the GET request to the API
    xmlhttp.Open "GET", url, False
    xmlhttp.send
    
    ' Check the API response status
    If xmlhttp.Status = 200 Then
        ' Parse the returned JSON
        Set json = JsonConverter.ParseJson(xmlhttp.responseText)

        ' Check if the "success" field is true
        If json("success") = True Then
            ' The query was successful, retrieve the result
            Dim records As Object
            Set records = json("result")("records")

            ' Check if there are any returned records
            If records.Count > 0 Then
                ' Get the latest record
                Dim record As Object
                Set record = records(1)

                ' Access the fields and assign the values to Excel cells as needed
                
                Sheets("Start").Range("B23").Value = record("DscREH")
                Sheets("Start").Range("B24").Value = record("DatInicioVigencia")
                Sheets("Start").Range("B25").Value = record("DatFimVigencia")
                
                Sheets("Start").Range("B28").Value = record("VlrTUSD")
                Sheets("Start").Range("B29").Value = record("VlrTE")
                
                ' ...
                ' Assign other fields and cells as needed
            Else
                MsgBox "No records found."
            End If
        Else
            MsgBox "Query failed: " & json("error")
        End If
    Else
        ' The request failed, display the error message
        MsgBox "API request failed! Status: " & xmlhttp.Status & " Response: " & xmlhttp.responseText
    End If
    
    ' Release the xmlhttp object
    Set xmlhttp = Nothing
End Sub

This macro is making the following API request:

https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%22fcf2906c-7c32-4b9b-a637-054e7a5234f4%22%20WHERE%20%22SigAgente%22%20%3D%20%27Equatorial%20AL%27%20AND%20%22DscSubGrupo%22%20%3D%20%27B3%27%20AND%20%22DscClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22SigAgenteAcessante%22%20IN%20(%27NA%27,%20%27N%C3%A3o%20se%20aplica%27)%20AND%20%22DscBaseTarifaria%22%20%3D%20%27Tarifa%20de%20Aplica%C3%A7%C3%A3o%27%20AND%20%22DscSubClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22DscModalidadeTarifaria%22%20%3D%20%27Convencional%27%20AND%20%22NomPostoTarifario%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20ORDER%20BY%20%22DatInicioVigencia%22%20DESC%20LIMIT%201

When testing the request in a browser, I can verify that it is working as expected and returning the following response:

// 20230605160415
// https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%22fcf2906c-7c32-4b9b-a637-054e7a5234f4%22%20WHERE%20%22SigAgente%22%20%3D%20%27Equatorial%20AL%27%20AND%20%22DscSubGrupo%22%20%3D%20%27B3%27%20AND%20%22DscClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22SigAgenteAcessante%22%20IN%20(%27NA%27,%20%27N%C3%A3o%20se%20aplica%27)%20AND%20%22DscBaseTarifaria%22%20%3D%20%27Tarifa%20de%20Aplica%C3%A7%C3%A3o%27%20AND%20%22DscSubClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22DscModalidadeTarifaria%22%20%3D%20%27Convencional%27%20AND%20%22NomPostoTarifario%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20ORDER%20BY%20%22DatInicioVigencia%22%20DESC%20LIMIT%201

{
  "help": "https://dadosabertos.aneel.gov.br/api/3/action/help_show?name=datastore_search_sql",
  "success": true,
  "result": {
    "sql": "SELECT * FROM \"fcf2906c-7c32-4b9b-a637-054e7a5234f4\" WHERE \"SigAgente\" = 'Equatorial AL' AND \"DscSubGrupo\" = 'B3' AND \"DscClasse\" = 'Não se aplica' AND \"SigAgenteAcessante\" IN ('NA', 'Não se aplica') AND \"DscBaseTarifaria\" = 'Tarifa de Aplicação' AND \"DscSubClasse\" = 'Não se aplica' AND \"DscModalidadeTarifaria\" = 'Convencional' AND \"NomPostoTarifario\" = 'Não se aplica' ORDER BY \"DatInicioVigencia\" DESC LIMIT 1",
    "records": [
      {
        "_id": 221157,
        "_full_text": "'-02':22 '-05':3,18,21 '-06':2 '-28':19 '12272084000100':16 '19':47 '2023':1,13,17 '2024':20 '24':9 '3.203':7 '304':46 '561':44 '83':45 'al':15 'aplica':30,33,36,39,43 'aplicação':25 'b3':26 'convencion':27 'de':8,10,12,24 'equatori':14 'homologatória':5 'maio':11 'mwh':40 'nº':6 'não':28,31,34,37,41 'resolução':4 'se':29,32,35,38,42 'tarifa':23",
        "DatGeracaoConjuntoDados": "2023-06-05",
        "DscREH": "RESOLUÇÃO HOMOLOGATÓRIA Nº 3.203, DE 24 DE MAIO DE 2023",
        "SigAgente": "Equatorial AL",
        "NumCNPJDistribuidora": "12272084000100",
        "DatInicioVigencia": "2023-05-28",
        "DatFimVigencia": "2024-05-02",
        "DscBaseTarifaria": "Tarifa de Aplicação",
        "DscSubGrupo": "B3",
        "DscModalidadeTarifaria": "Convencional",
        "DscClasse": "Não se aplica",
        "DscSubClasse": "Não se aplica",
        "DscDetalhe": "Não se aplica",
        "NomPostoTarifario": "Não se aplica",
        "DscUnidadeTerciaria": "MWh",
        "SigAgenteAcessante": "Não se aplica",
        "VlrTUSD": "561,83",
        "VlrTE": "304,19"
      }
    ],
    "fields": [
      {
        "id": "_id",
        "type": "int4"
      },
      {
        "id": "_full_text",
        "type": "tsvector"
      },
      {
        "id": "DatGeracaoConjuntoDados",
        "type": "text"
      },
      {
        "id": "DscREH",
        "type": "text"
      },
      {
        "id": "SigAgente",
        "type": "text"
      },
      {
        "id": "NumCNPJDistribuidora",
        "type": "text"
      },
      {
        "id": "DatInicioVigencia",
        "type": "text"
      },
      {
        "id": "DatFimVigencia",
        "type": "text"
      },
      {
        "id": "DscBaseTarifaria",
        "type": "text"
      },
      {
        "id": "DscSubGrupo",
        "type": "text"
      },
      {
        "id": "DscModalidadeTarifaria",
        "type": "text"
      },
      {
        "id": "DscClasse",
        "type": "text"
      },
      {
        "id": "DscSubClasse",
        "type": "text"
      },
      {
        "id": "DscDetalhe",
        "type": "text"
      },
      {
        "id": "NomPostoTarifario",
        "type": "text"
      },
      {
        "id": "DscUnidadeTerciaria",
        "type": "text"
      },
      {
        "id": "SigAgenteAcessante",
        "type": "text"
      },
      {
        "id": "VlrTUSD",
        "type": "text"
      },
      {
        "id": "VlrTE",
        "type": "text"
      }
    ]
  }
}

However, when I try to use the macro in Excel through the VBA editor, I receive this message:

<html>
  <head>
    <title>502 Bad Gateway </title>
  </head>
  <body>
     <center>
       <h1>502 Bad Gateway</h1>
     </center>
       <hr>
     <center>nginx/1.18.0(Ubuntu)
     </center>
  </body>
</html>

How can I fix this?

答案1

得分: 2

这对我有用 - 重新排列以更易读：

Sub ConsultAPI()
    Dim xmlhttp As Object
    Dim url As String, qs As String
    Dim response As String
    Dim json As Object
    Dim utilityCompany As String
    Dim tariffGroup As String, tariffClass As String
    Dim subclassFilter As String, wsStart As Worksheet
    
    Set wsStart = ThisWorkbook.Sheets("Start")
    utilityCompany = wsStart.Range("B21").Value ' 公用事业公司名称
    tariffGroup = wsStart.Range("B27").Value ' 费率组
    tariffClass = wsStart.Range("B19").Value ' 费率类别
    subclassFilter = wsStart.Range("B20").Value
    
    url = "https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql="
    qs = "SELECT * FROM ""fcf2906c-7c32-4b9b-a637-054e7a5234f4"" WHERE " & _
            """SigAgente"" = '" & utilityCompany & "' AND " & _
            """DscSubGrupo"" = '" & tariffGroup & "' AND " & _
            """DscClasse"" = '" & tariffClass & "' AND " & _
            """SigAgenteAcessante"" IN ('NA', 'Não se aplica') AND " & _
            """DscBaseTarifaria"" = 'Tarifa de Aplicação' AND " & _
            """DscSubClasse"" = '" & subclassFilter & "' AND " & _
            """DscModalidadeTarifaria"" = 'Convencional' AND " & _
            """NomPostoTarifario"" = 'Não se aplica' ORDER BY ""DatInicioVigencia"" DESC LIMIT 1"

    url = url & WorksheetFunction.EncodeURL(qs) ' 编码SQL（Excel 2013）
    
    ' 在进行GET请求之前保存请求详细信息
    ' SaveRequestDetails url

    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", url, False
    xmlhttp.send ' 从这点开始正常...
英文:

This works for me - re-arranged to be a little more readable:

Sub ConsultAPI()
    Dim xmlhttp As Object
    Dim url As String, qs As String
    Dim response As String
    Dim json As Object
    Dim utilityCompany As String
    Dim tariffGroup As String, tariffClass As String
    Dim subclassFilter As String, wsStart As Worksheet
    
    Set wsStart = ThisWorkbook.Sheets("Start")
    utilityCompany = wsStart.Range("B21").Value ' utility company name
    tariffGroup = wsStart.Range("B27").Value ' tariff group
    tariffClass = wsStart.Range("B19").Value ' tariff class
    subclassFilter = wsStart.Range("B20").Value
    
    url = "https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql="
    qs = "SELECT * FROM ""fcf2906c-7c32-4b9b-a637-054e7a5234f4"" WHERE " & _
            " ""SigAgente"" = '" & utilityCompany & "' AND " & _
            " ""DscSubGrupo"" = '" & tariffGroup & "' AND " & _
            " ""DscClasse"" = '" & tariffClass & "' AND " & _
            " ""SigAgenteAcessante"" IN ('NA', 'Não se aplica') AND " & _
            " ""DscBaseTarifaria"" = 'Tarifa de Aplicação' AND " & _
            " ""DscSubClasse"" = '" & subclassFilter & "' AND " & _
            " ""DscModalidadeTarifaria"" = 'Convencional' AND " & _
            " ""NomPostoTarifario"" = 'Não se aplica' ORDER BY ""DatInicioVigencia"" DESC LIMIT 1"

    url = url & WorksheetFunction.EncodeURL(qs) 'encode the SQL (Excel 2013 on)
    
    ' Save the request details before making the GET request
    'SaveRequestDetails url

    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", url, False
    xmlhttp.send 'OK from this point...

huangapple
  • 本文由 发表于 2023年6月6日 03:21:00
  • 转载请务必保留本文链接：https://go.coder-hub.com/76409427.html
  • api
  • excel
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定