let
  Token = Excel.CurrentWorkbook(){[Name="Token"]}[Content]{0}[Column1],
  Codigo = Text.From(Excel.CurrentWorkbook(){[Name="Codigo"]}[Content]{0}[Column1]),
  UF = Excel.CurrentWorkbook(){[Name="UF"]}[Content]{0}[Column1],
  Referencia = Excel.CurrentWorkbook(){[Name="Referencia"]}[Content]{0}[Column1],
  Regime = Excel.CurrentWorkbook(){[Name="Regime"]}[Content]{0}[Column1],
  Url = "http://127.0.0.1:7705/v1/inputs/" & Codigo & "/latest",
  Fonte = Json.Document(Web.Contents(Url,
    [
      Query=[uf=UF, reference=Referencia],
      Headers=[Authorization="Bearer " & Token]
    ]
  )),
  Resultado = #table({"Codigo", "UF", "Referencia", "Sem encargos", "Com desoneracao", "Sem desoneracao"}, {{
    Fonte[codigo]{0}, Fonte[uf]{0}, Fonte[referencia]{0},
    Fonte[preco_sem_encargos]{0}, Fonte[preco_com_desoneracao]{0}, Fonte[preco_sem_desoneracao]{0}
  }})
in
  Resultado
                            

                              let
  Token = Excel.CurrentWorkbook(){[Name="Token"]}[Content]{0}[Column1],
  UF = Excel.CurrentWorkbook(){[Name="UF"]}[Content]{0}[Column1],
  Referencia = Excel.CurrentWorkbook(){[Name="Referencia"]}[Content]{0}[Column1],
  Regime = Excel.CurrentWorkbook(){[Name="Regime"]}[Content]{0}[Column1],
  BaseUrl = "http://127.0.0.1:7705",
  Fonte = Json.Document(Web.Contents(BaseUrl,
    [
      RelativePath="v1/charges",
      Query=[reference=Referencia, uf=UF, regime=Regime],
      Headers=[Authorization="Bearer " & Token]
    ]
  )),
  Resultado = #table({"Referencia", "UF", "Regime", "Horista (%)", "Mensalista (%)", "Localidade"}, {{
    Fonte[reference]{0}, Fonte[uf]{0}, Fonte[regime]{0},
    Fonte[data][h]{0}, Fonte[data][mes]{0}, Fonte[data][localidade]{0}
  }})
in
  Resultado
                            

                              let
  Token = Excel.CurrentWorkbook(){[Name="Token"]}[Content]{0}[Column1],
  Lista = Excel.CurrentWorkbook(){[Name="ListaSINAPI"]}[Content],
  BaseUrl = "http://127.0.0.1:7705",

  ConsultarInsumo = (Codigo as any, UF as text, Referencia as text, Regime as text) as record =>
    let
      Fonte = Json.Document(Web.Contents(BaseUrl,
        [
          RelativePath="v1/inputs/" & Text.From(Codigo) & "/latest",
          Query=[uf=UF, reference=Referencia],
          Headers=[Authorization="Bearer " & Token]
        ]
      )),
      Preco = if Regime = "sem" then Fonte[preco_sem_encargos]{0}
        else if Regime = "sem_desoneracao" then Fonte[preco_sem_desoneracao]{0}
        else Fonte[preco_com_desoneracao]{0},
      Resultado = [
        Descricao=Fonte[descricao]{0},
        Unidade=Fonte[unidade]{0},
        Classificacao=Fonte[classificacao]{0},
        Preco=Preco,
        SemEncargos=Fonte[preco_sem_encargos]{0},
        ComDesoneracao=Fonte[preco_com_desoneracao]{0},
        SemDesoneracao=Fonte[preco_sem_desoneracao]{0}
      ]
    in
      Resultado,

  ComConsulta = Table.AddColumn(Lista, "DadosSINAPI", each
    try ConsultarInsumo(Text.From([Codigo]), Text.Upper([UF]), Text.From([Referencia]), Text.From([Regime]))
    otherwise [Descricao="ERRO NA CONSULTA", Unidade=null, Classificacao=null, Preco=null, SemEncargos=null, ComDesoneracao=null, SemDesoneracao=null]
  ),
  Expandido = Table.ExpandRecordColumn(ComConsulta, "DadosSINAPI",
    {"Descricao", "Unidade", "Classificacao", "Preco", "SemEncargos", "ComDesoneracao", "SemDesoneracao"}
  ),
  Tipos = Table.TransformColumnTypes(Expandido, {
    {"Codigo", type text}, {"UF", type text}, {"Referencia", type text},
    {"Preco", Currency.Type}, {"SemEncargos", Currency.Type},
    {"ComDesoneracao", Currency.Type}, {"SemDesoneracao", Currency.Type}
  })
in
  Tipos
                            

                              Function SINAPI_PRECO(Codigo As String, UF As String, Regime As String, Token As String) As Variant
  Dim Http As Object, Url As String, Texto As String
  Dim Chave As String, Inicio As Long, Fim As Long

  Url = "http://127.0.0.1:7705/v1/inputs/" & Codigo & "/latest?uf=" & UF
  Set Http = CreateObject("MSXML2.XMLHTTP")
  Http.Open "GET", Url, False
  Http.setRequestHeader "Authorization", "Bearer " & Token
  Http.Send

  If Http.Status <> 200 Then
    SINAPI_PRECO = CVErr(xlErrValue)
    Exit Function
  End If

  Texto = Http.responseText
  Select Case Regime
    Case "sem": Chave = """preco_sem_encargos"":["
    Case "sem_desoneracao": Chave = """preco_sem_desoneracao"":["
    Case Else: Chave = """preco_com_desoneracao"":["
  End Select

  Inicio = InStr(Texto, Chave) + Len(Chave)
  Fim = InStr(Inicio, Texto, "]")
  SINAPI_PRECO = CDbl(Replace(Mid(Texto, Inicio, Fim - Inicio), ".", Application.DecimalSeparator))
End Function
                            

                              =SINAPI_PRECO(A2;B2;C2;$F$1)

A2 = código | B2 = UF | C2 = regime | F1 = token
                            

                              let
  Token = "snp_SEU_TOKEN",
  Codigo = "6111",
  UF = "SP",
  Referencia = "2026-04",
  BaseUrl = "http://127.0.0.1:7705",
  Caminho = "v1/inputs/" & Codigo & "/latest",
  Fonte = Json.Document(Web.Contents(BaseUrl,
    [
      RelativePath=Caminho,
      Query=[uf=UF, reference=Referencia],
      Headers=[Authorization="Bearer " & Token]
    ]
  )),
  Tabela = #table({"Codigo", "UF", "Referencia", "Sem encargos", "Com desoneracao", "Sem desoneracao"}, {{
    Fonte[codigo]{0}, Fonte[uf]{0}, Fonte[referencia]{0},
    Fonte[preco_sem_encargos]{0}, Fonte[preco_com_desoneracao]{0}, Fonte[preco_sem_desoneracao]{0}
  }})
in
  Tabela
                            

                              /**
 * Retorna o preço SINAPI.
 * @customfunction
 */
function SINAPI_PRECO(codigo, uf, regime, token, referencia) {
  const base = "http://SERVIDOR:7705";
  let url = `${base}/v1/inputs/${codigo}/latest?uf=${encodeURIComponent(uf)}`;
  if (referencia) url += `&reference=${encodeURIComponent(referencia)}`;
  const response = UrlFetchApp.fetch(url, {
    headers: {Authorization: `Bearer ${token}`},
    muteHttpExceptions: true
  });
  if (response.getResponseCode() !== 200) return `ERRO ${response.getResponseCode()}`;
  const data = JSON.parse(response.getContentText());
  if (regime === "sem") return data.preco_sem_encargos[0];
  if (regime === "sem_desoneracao") return data.preco_sem_desoneracao[0];
  return data.preco_com_desoneracao[0];
}
                            

                              =SINAPI_PRECO(A2;B2;C2;$F$1;D2)

A2 = código | B2 = UF | C2 = regime | D2 = referência | F1 = token