NetSuite REST API Post working in Postman but battling in PowerBI

[icassiem]

Hi, 

Using Rest API Post via Postman works using Bearer Token as a test within Postman but tried a lot of things with PowerBI

Can anyone please guide me what I'm doing wrong with the below code, got errrors (400, 405 bad request, credentials for ananymous is incorrect even though the refreshed token works in Postman)

Tried JSON document & fromValue

/*shared NetsuiteConnector.Contents = (url as text) =>*/
let
        url = "https://XXX-sb1.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql",
        vToken =  "Bearer eyJraWQiOiJjLjQ5NTA5OT.....",
        suiteapiquery = "SELECT * FROM transactionline",
        body = "SELECT * FROM transactionline tl WHERE tl.transaction = 1788003",
 //source = Json.Document(Web.Contents(url,[Headers = headers, Query = [q = body]]))

// source = Json.Document(Web.Contents(url ,
   //[RelativePath = "reports/rows?auth=" & vToken,   
     //Headers = [#"Prefer" = "transient", #"Content-Type"="application/json", 
     Authorization=vToken],  //, Query = [q = body]]
  //Content= Text.ToBinary(body)
// ]))

Headers = [#"Prefer" = "transient", #"Content-Type"="application/json", Authorization=vToken],  /*, Query = [q = body]]*/
postData = Json.FromValue([query = suiteapiquery ]), 
response = Web.Contents(url, [Headers = Headers, Content = postData])

in
    //source
    response

[lbendlin · Accepted solution]

Best to follow the actual documentation.  Web.Contents - PowerQuery M | Microsoft Learn

[icassiem]

Hi @lbendlin

Thank You but unfortunately no progress

Please Any ideas?

Regards

[lbendlin]

What have you tried and where are you stuck?

[icassiem]

Hi @lbendlin 

The link assisted me in being able to connect, now i need to figure out how to dynamically generate the Token

/*shared NetsuiteConnector.Contents = (url as text) =>*/
let
        url = "https://XXXXXX-sb1.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql",
        vToken =  "Bearer eyJraWQiOiJjLjQ5NTA5OTJfU0Ix",
        body = "SELECT * FROM transactionline",
        postData = Json.FromValue([q = body]),  
        source = Json.Document
(Web.Contents(url ,
 [
 Headers = [#"Prefer" = "transient", #"Content-Type"="application/json", Authorization=vToken], 
 Content = postData                                                                              
 ]
)),
    #"Converted to Table" = Record.ToTable(source)
in
    #"Converted to Table"

Please help, How do i get started?

[icassiem]

hI @lbendlin 

I'm stuck, somehow to dynamically generate the Nestuite Post for tokens I don't find a solution.

Not sure where to use or get the values for:

• NS OAuth self-signed certificate ID?
• PRIVATE KEY?
• NSTokenId?
• NSTokenSecret?
• client_assertion_type?
• JWT, not sure how and what to do here?

Power Query/PowerBI
let            
    api_url = https://XXX.suitetalk.api.netsuite.com/services/rest/auth/oauth2/v1/token,   //4950992-sb1 = NetSuite CompanyID    
    consumerkey = "a0ad2d9..", 
    consumersecret = "1b3c33...", 
    tokenid = NSTokenId, 
    tokensecret = NSTokenSecret, 
    grant_type="client_credentials",
    client_assertion_type="urn:...",
    ClientID = "a0ad...",
    Secret = "1b3c...",    

    body = "grant_type=" & grant_type & "&client_assertion_type=" & client_assertion_type & "&client_id=" & ClientID & "&client_secret=" & Secret,
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),    
    Token_Response  = Json.Document(Web.Contents(api_url,
    [ 
       Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],    
       Content = Text.ToBinary(body)  
    ]
    )),
     access_token = Token_Response[access_token]
in
     access_token

[lbendlin]

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons).  Good luck.