Odata feed to excel permissions errors

Kinetic ERP
1

Followed this guide for setting up a BAQ feed to Excel: Pull REST data into Excel based on values on excel? - #24 by amurdock

However, when I try to pull the data I get the following error:

image
image1047×218 29.4 KB

Seems like a potential permissions issue but I’m not sure how. the username/pw combo I’m authenticating with has admin access

3 Likes
2

Welcome to EpiUsers @remyk !

Check this: Excel Parameter To Filter Epicor BAQ - #25 by CSmith
and this: Using Excel to Pull Data With REST and Multiple BAQs - #19 by NateS

These should help with access to the OData feed using excel and the Power M Query needed to access your data properly. If you need more information beyond those we will try to assist, but more details like API v1/v2 access? Can you access the swagger help area(s) using the same Epicor Login?
V1: https://{SERVER}/{INSTANCE}/api/help/v1/
V2: https://{SERVER}/{INSTANCE}/api/help/v2/

2 Likes
3

I got this error recently and I had to rebuild my calls to limit the amount of data being parsed into the URL. See the chunking section here: Using Epicor API with Excel - Chunking URLs - Code Review / Sharing - Epicor User Help Forum

1 Like
4

Epicor REST Basic auth wants UserName, Password, and APIKey.

Excel PowerQuery (and PowerBI, Fabric, etc) seems to have issues with doing all three using the authentication methods dialogs. Basic allows for username & password but not X-API-Key header while API Key says ‘anonymous only’. The Auth UX differs depending on OData.Feed vs Web.Contents sometimes you can get it to work and it’ll stick - until it doesn’t. :face_vomiting:

I’ve found Get Data > From Web allows you to set headers manually which seems to work and not break. The drawback is you need to Base64Encode the Authorization header value - means a tad of vba (more below).

The key bit is skip OData and go with this:

Source = Json.Document(Web.Contents(pURL, [Headers=[Authorization=pAuth, #"X-API-Key"=pAPIKey]])),

where

pAuth = "Basic " & EncodeBase64("UserName:Password")

Here’s our minimal viable BAQ Excel template.
PowerQueryBAQ.zip (23.9 KB)

ERP-POWERQ2

M-Query:

let
    Params = Excel.CurrentWorkbook(){[Name="Params"]}[Content],
    pURL = Params{List.PositionOf(Params[NAME],"APIUrl")}[VALUE],
    pAPIKey = Params{List.PositionOf(Params[NAME],"API-Key")}[VALUE],
    pAuth = Params{List.PositionOf(Params[NAME],"Auth")}[VALUE],
    Source = Json.Document(Web.Contents(pURL, [Headers=[Authorization=pAuth, #"X-API-Key"=pAPIKey]])),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

VBA:
Called from Params.Auth table cell like so:

="Basic " & EncodeBase64("UserName:Password")

Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As Variant
  Dim objNode As Variant

  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = Replace(objNode.text, vbLf, "")

  Set objNode = Nothing
  Set objXML = Nothing
End Function

Alternatively, you can scrape the Auth header out of your browser tools or use PowerShell, like:

$Base64Auth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("$($Username):$($Password)"))

Better yet, do this:

Base64Auth = "Basic " & Binary.ToText(Text.ToBinary("datapuller:mydatapullerpassword"), BinaryEncoding.Base64),

Hope this helps. and hope MSFT will fix the auth UX someday.

Josh

1 Like
5

You can do the same thing with OData feed.

3 Likes
6

You can do the same thing with OData feed.

Yeah, similar but different as far how OData.Feed takes in headers and APIKeys.

I stay with Web.Contents cuz I can POST with it like:

let
    url = ...,
    headers = [#"Content-Type" = "application/json"],
    postData = Json.FromValue([x =1, y = 2]),
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = postData
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse
2 Likes