REST API Call with Cookie Session ID

[bdalager]

Hi. I am new to this and am having difficulty figuring out the REST API calls in Power Query. I am able to get the session ID, but when I use it for the ct results query, I get a 500 error. 

DataSource.Error: Web.Contents failed to get contents from 'https://mycompany- wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results?startDate=2025-02-01&endDate=2025-02-15&outputFormat=PIPE&dateFormat=mmddyyyy&timeFormat=24&ctIds=2000' (500):

Details:

DataSourceKind=Web

DataSourcePath=https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results

Url=https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results?startDate=2025-...

I've seen several old posts about using cookies unsuccessfully in Power Query. Should this work?

let
     mySessionId = let
          TokenReq = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/users/v1/login", [
          Headers = [
               #"Content-Type" = "application/json"
          ],
          Content = Json.FromValue([
               customerId = 1,
               userName = UN,
               password = PW,
               locale = "en_US"
          ])

          ]),
          TokenJson = Json.Document(TokenReq),
          sessionId = TokenJson[jsessionId]

     in

     sessionId,

     Source = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results?"&"startDate="&STARTDATE&"&endDate="&ENDDATE&"&outputFormat=PIPE&dateFormat=mmddyyyy&timeFormat=24&ctIds="&CTIDS, [
     Headers = [
          #"Content-Type" = "application/json",
          Cookie = "JSESSIONID="&mySessionId
     ]
     ])

in
Source

Any ideas?

Thanks 🙂

This is really all the documentation shows (with more parameters on the next page).

[ZhangKun]

you'd use POST method for get data.It's like

Source = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results", [
     Headers = [
          #"Content-Type" = "application/json",
          Cookie = "JSESSIONID="&mySessionId
     ], 

     Content = Json.FromValue([
               startDate = "2025-01-15",
               endDate = "2025-01-31", 

               outputFormat = "JSON"
     ])

     ])

[bdalager]

Oh! Thank you so much. That has inched me a little closer to what I need.

Also, I am wondering how to format a couple of things-

--The fields parameter (attached). I've tried quotes and # but it keeps asking for a comma. I'm definitely not doing something right.

--I have some information saved in Power Query under Manage Parameters. I want the CTIDs to be a managed parameter too, but the format in the m code is  ctIds = {"2000","2008"}  How would I enter that into the managed parameters and/or change the m code so it is formatted properly?

And, last, I'm having trouble getting the result. It's a process to get the session ID, then a JobID, then the status, then the result. I'm good to the status, but I can't get the resulting data out of it. 

I'm getting:

DataFormat.Error: We found an unexpected character in the JSON input.
Details:
Value=#
Position=0

This is what I have so far:

let
mySessionId = let
TokenReq = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/users/v1/login", [
Headers = [
#"Content-Type" = "application/json"
],
Content = Json.FromValue([
customerId = 1,
userName = UN,
password = PW,
locale = "en_US"
])

]),
TokenJson = Json.Document(TokenReq),
sessionId = TokenJson[jsessionId]
in sessionId,

myJobId = let
Source = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results", [
Headers=[
#"Content-Type"="application/json",
Cookie = "JSESSIONID="&mySessionId
],
Content=Json.FromValue([
startDate = STARTDATE,
endDate = ENDDATE,
outputFormat = "PIPE",
dateFormat = "mmddyyyy",
timeFormat = 24,
ctIds = {"2000","2008"}
])]),
jobJson = Json.Document(Source),
apijobId = jobJson[jobId]

in apijobId,

myStatus = let
apiStatus = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/status/"&myJobId, [
Headers=[
#"Content-Type"="application/json",
Cookie = "JSESSIONID="&mySessionId
]]),
statusCode = Json.Document(apiStatus)

in statusCode,

myResult = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/result/"&myJobId, [
Headers=[
Accept="text/plain",
#"Accept-Encoding"="gzip",
Cookie="JSESSIONID="&mySessionId
]]),
resultData = Json.Document(myResult)
in resultData

Thank you very much for the help.

[ZhangKun]

--The fields parameter (attached). I've tried quotes and # but it keeps asking for a comma. I'm definitely not doing something right.

You'd know that the text show ABC in screen or paper, when you write "ABC" in code. so, {"name": "date"},{"name":"period"} should write be "{""name"": ""date""},{""name"":""period""}" or Text.FromBinary(Json.FromValue(List.Transform({"date", "period"}, each [name = _])))(may to removed the [ and ] at start and end the result string).

--I have some information saved in Power Query under Manage Parameters. I want the CTIDs to be a managed parameter too, but the format in the m code is  ctIds = {"2000","2008"}  How would I enter that into the managed parameters and/or change the m code so it is formatted properly?

The Parameters can't show the list, so you must revise the code in Advanced Editor. maybe you can enter text, then split it.

And, last, I'm having trouble getting the result. It's a process to get the session ID, then a JobID, then the status, then the result. I'm good to the status, but I can't get the resulting data out of it. 

myResult = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/result/" & myJobId, [
        Headers=[
            Accept="text/plain",
            #"Accept-Encoding"="gzip",
            Cookie="JSESSIONID=" & mySessionId
        ]
    ]),
    // you'd use Accept="application/json" in Headers, because you use Json.Document to parse result
    resultData = Json.Document(myResult)

[bdalager]

Thank you again for the help!

The solution for the CTIDs worked using the list and Text.Split (yay!)

I'm still struggling with the fields parameter:

fields = "{""name"":""date""},{""name"":""period""},{""name"":""ctID""},{""name"":""ctName""},{""name"":""actContactsReceived""}"

But I get:

DataSource.Error: Web.Contents failed to get contents from 'https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results' (400):
Details:
DataSourceKind=Web
DataSourcePath=https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results
Url=https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results

And I didn't understand the solution you suggested:

Text.FromBinary(Json.FromValue(List.Transform({"date", "period"}, each [name = _])))

Another new issue, as I get further into this, more things pop up. 

I get the sessionID, then the JobID, and when I check the status, sometimes it replies with WAITING so I added a 30 second delay. Now, most of the time, it replies with COMPLETED_SUCCESSFULLY. I wanted to add a loop where it wouldn't continue until it was successful, but I couldn't figure that out, so the delay seems to work okay.

The issue, though, is when I get the result (data), it returns {"message":"Your Job isn't completed yet. Try again later","status":206}

I added a delay to this as well, but it doesn't make a difference. I can't find a lot of information about what this means or how to resolve it. Do you have any ideas?

This is all the documentation has - 

Code: 206 Partial content
{"message": "Your Job isn't completed yet. Try again later",
"status": 206}

let
// Step 1: Login API to authenticate and get JSESSIONID
GetSessionId = () as text =>
let
TokenReq = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/users/v1/login", [
Headers = [#"Content-Type" = "application/json"],
Content = Json.FromValue([
customerId = 1,
userName = UN,
password = PW,
locale = "en_US"
])
]),
TokenJson = Json.Document(TokenReq),
sessionId = TokenJson[jsessionId]
in
sessionId,

// Step 2: Call the ct-results API to get Job ID
GetJobId = (mySessionId as text) as text =>
let
ctResultsReq = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results", [
Headers = [
Cookie = "JSESSIONID=" & mySessionId,
#"Content-Type"="application/json"
],
Content=Json.FromValue([
startDate = STARTDATE,
endDate = ENDDATE,
outputFormat = "PIPE",
dateFormat = "mmddyyyy",
timeFormat = 24,
ctIds = Text.Split(CTIDS, ",")
/*ctIds = {"2000", "2008"},*/
/*fields = "{""name"":""date""},{""name"":""period""},{""name"":""ctID""},{""name"":""ctName""},{""name"":""actContactsReceived""}"*/
])
]),
ctResultsJson = Json.Document(ctResultsReq),
jobId = ctResultsJson[jobId]
in
jobId,

// Step 3: Function to check the status
CheckStatus = (myJobId as text, mySessionId as text) as text =>
let
apiStatus = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/status/"&myJobId, [
Headers=[
#"Content-Type"="application/json",
Cookie = "JSESSIONID="&mySessionId
]]),
statusJson = Json.Document(apiStatus),
statusCode = statusJson[status]&" "&Text.From(statusJson[totalRecords])&" records"
in
statusCode,

// Step 4: Function to get result data
GetResultData = (myJobId as text, mySessionId as text) as table =>
let
myResult = Csv.Document(Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/result/" & myJobId, [
Headers = [
Accept = "text/plain",
#"Accept-Encoding" = "gzip",
Cookie = "JSESSIONID=" & mySessionId
]
]), [Delimiter = "|", Columns = 47])
in
myResult,

// Main execution steps: Authenticate, get Job ID, check status, and get result
mySessionId = GetSessionId(),
myJobId = GetJobId(mySessionId),
myStatus = Function.InvokeAfter( () => CheckStatus(myJobId, mySessionId),#duration(0,0,0,30)),
myData = Function.InvokeAfter( () => GetResultData(myJobId, mySessionId),#duration(0,0,1,0))
in
myData

I really appreciate you.  😊

[ZhangKun · Accepted solution]

About fields:

Since you didn't provide complete documentation, I can't give you a more accurate answer. Based on my previous experience, it may be used in the following way:

// Step 2: Call the ct-results API to get Job ID
GetJobId = (mySessionId as text) as text =>
    let
        ctResultsReq = Web.Contents("https://mycompany-wfm.nicecloudsvc.com/SMARTSync/services/rs/exporters/v1/ct-results", [
            Headers = [
                Cookie = "JSESSIONID=" & mySessionId,
                #"Content-Type"="application/json"
            ],
            Content=Json.FromValue([
                startDate = STARTDATE,
                endDate = ENDDATE,
                outputFormat = "PIPE",
                dateFormat = "mmddyyyy",
                timeFormat = 24,
                ctIds = Text.Split(CTIDS, ",")
                // This is an array object, it should be surrounded by [].
                fields = "[{""name"":""date""},{""name"":""period""},{""name"":""ctID""},{""name"":""ctName""},{""name"":""actContactsReceived""}]"
            ])
        ]),
        ctResultsJson = Json.Document(ctResultsReq),
        jobId = ctResultsJson[jobId]
    in
        jobId

I think you should read the documentation about fields carefully and try to solve it.

About loops:

Since I am not familiar with the workflow of the server, I cannot tell you exactly what to do. I guess the possible problem is that your data volume is large, which takes longer time, and you need to add loops to perform multiple accesses. Perhaps you can refer to the following code (if you don't have a better code):

let
    // An example function
    GetData = (arg) => 
        Number.Random(), 
    // Example function parameters, maximum number of retries, interval between each retry (default 5 seconds)
    fx = (arg, MaxCount, optional sec) =>
        List.Accumulate(
            {1..MaxCount}, 
            [status = false, result = null], 
            (s, v) => 
                // If status is true, the program will loop again, but without delay.
                if s[status] then s 
                else 
                    let
                        r1= Function.InvokeAfter(() => GetData(arg), #duration(0, 0, 0, sec ?? 5)), 
                        // If the result meets the requirements, set status to true and get the result
                        r2 = if r1 <= 0.1 then [status = true, result = r1] else s
                    in 
                        r2
    )
in
    fx("arg_test", 3)

[bdalager]

Thank you so much. You were a great help!