Hi,
I would like to use the web call in Excel's Power Query in order to call an Infor LN availabe endpoint.
But I need an API key for that.
Did someone managed to do something similiar?
I had dabbled in this previously, and resurrected what I did that may help you.. Curious as to your progress.
let
// Replace these values with your ionapi credentials file. api_ti = "{mytenant}_DEV", api_cn = "KH Utility BE using KHeiman 27Mar24", api_ci = "{mytenant}_DEV~EwJ_oq0jan<<removed>>KK-axDGvKIeIg", api_cs = "fUtsw8zD_oCcuNIKlKA5eQQTxS7NXYNYe9ansU6<<removed>>S0g0owTwx69UWAsw", api_iu = ">xxmingle-ionapi.inforcloudsuite.com", api_pu = ">xxmingle-sso.inforcloudsuite.com:443/.../as/", api_oa = "authorization.oauth2", api_ot = "token.oauth2", api_or = "revoke_token.oauth2", api_ev = "U1478358101", api_saak = "{mytenant}_DEV#5a47or6Z4bve1b1otZxpIDoQf-<<removed>>Pi_7v249k6XpgpYUA", api_sask ="VVEmy<<removed>>N4n8Hnr8s7gOA",//// get a bearer token token_url = api_pu & api_ot, api_base_url = api_iu & api_ti & "OSPORTAL/admin/v1/info/status", api_qry_str = "", // example "?myparameter"
body="grant_type=password&client_id=" & api_ci & "&client_secret=" & api_cs & "&username=" & api_saak & "&password=" & api_sask , Source = Json.Document(Web.Contents(token_url, [ Headers = [#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body) ] ) ), bearer_token = Source[access_token],
refresh_token = Source[refresh_token] , token_type = Source[token_type] , expires_in = Source[expires_in] ,// API trace references trace_parent = Text.NewGuid() as text, trace_datetime = DateTime.FixedLocalNow() as datetime, trace_id = DateTime.ToText(trace_datetime,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]),//// Call the URL and get response data= Json.Document(Web.Contents(api_iu & api_qry_str, [ Headers = [ #"Authorization"="Bearer "&bearer_token,#"Content-Type"="application/json", #"User-Agent"="KHPowerExcel",#"parent-id"=trace_parent,#"trace-id"=trace_id] ] ) ) in data //
I meant to add, that if you are doing repeated calls, you should use the refresh token once you get the initial bearer token.
In this example I was just calling once to get status as an example.
Hi. I'm pretty new to this. Are you using this in production? Were there any business security concerns of including the ionapi content inside the Power Query source?
This script is awesome! I was able to get IDO information from it. I did do some changes I wanted to share. One thing to note was in power query I had to go to "Data source settings" so that the web call is "Anonymous" that way I can handle authentication in the script. Also there is some security things that we had to set about a web data source each time to "Organizational" in Excel. I also added to the script to revoke the token once it is done if that is helpful as example to anyone. Thank you @Kevin Heiman for sharing the initial script. let
// Replace these values with your ionapi credentials file. api_ti = "{mytenant}_DEV", api_cn = "KH Utility BE using KHeiman 27Mar24", api_ci = "{mytenant}_DEV~EwJ_oq0jan<<removed>>KK-axDGvKIeIg", api_cs = "fUtsw8zD_oCcuNIKlKA5eQQTxS7NXYNYe9ansU6<<removed>>S0g0owTwx69UWAsw", api_iu = ">xxmingle-ionapi.inforcloudsuite.com", api_pu = ">xxmingle-sso.inforcloudsuite.com:443/.../as/", api_oa = "authorization.oauth2", api_ot = "token.oauth2", api_or = "revoke_token.oauth2", api_ev = "U1478358101", api_saak = "{mytenant}_DEV#5a47or6Z4bve1b1otZxpIDoQf-<<removed>>Pi_7v249k6XpgpYUA", api_sask ="VVEmy<<removed>>N4n8Hnr8s7gOA", //
// get a bearer token token_url = api_pu & api_ot,
refresh_token = Source[refresh_token] , token_type = Source[token_type] , expires_in = Source[expires_in] , token_header = Source[token_type] & " " & bearer_token, //data = token_header // API trace references //trace_parent = Text.NewGuid() as text, //trace_datetime = DateTime.FixedLocalNow() as datetime, //trace_id = DateTime.ToText(trace_datetime,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]), //
//extra stuff for IDO Queries //Change this to what ever site your company has ido_site = "US", //Change this to what ever IDO you want to pull from ido = "SLCoitems", rowcap_num = "0", //0 is unlimited, -1 is default, or what ever number for the max rows you would like back api_mon_config = api_ti & "_" & ido_site, api_url_start = api_iu & "/" & api_ti & "/CSI/IDORequestService/MGRestService.svc/js/" & ido & "/adv?",
//Change this to add a filter based on the column you want to query filter_str = "Item like '%'", //NOTE if there is one bad column or subcollection in the IDO then you will need to list all the columns you need so that Syteline doesn't throw an error. props = "Item,CoNum,CoOrderDate,CoCustNum,Adr0Name,CoLine,Description,QtyOrderedConv,ue_UfPlannedShipDate,ShipDate,PriceConv,DerNetPrice,CostConv,Stat,RefRelease,CblContPriceConv,ReleaseDate", //exampe "&props=Item,Description"
data = Json.Document(Web.Contents(api_url_start, [ Headers=[#"Content-Type"="application/json", #"Authorization"=token_header,#"X-Infor-MongooseConfig"=api_mon_config], Query=[rowcap=rowcap_num, props=props, filter=filter_str] ])), //data = Xml.Tables(Web.Contents(api_doc_start & api_qry_str, [Headers=[#"Content-Type"="application/json", Authorization=token_header, #"X-Infor-MongooseConfig"=api_mon_config]])),
//Clean-up to revoke token revoke_token_endpoint = api_pu & api_or, revoke_auth = "Basic " & Binary.ToText(Text.ToBinary(api_ci & ":" & api_cs, BinaryEncoding.Base64)), revoke_result = Json.Document(Web.Contents(revoke_token_endpoint, [Headers=[#"Content-Type"="application/x-www-form-urlencoded", Authorization=revoke_auth], Query=[token=refresh_token,#"token_type_hint"="refresh_token"], Content=Text.ToBinary("")])),
data_to_return = data in data_to_return //
keep a watch out for a native driver for JDBC and MS connectors. Check the roadmap, as some of this is in early adopter phase and moving along.
