Forum Discussion
REST API Call with Cookie Session ID
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 jobIdI 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)
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"
])
])
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=0This 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 resultDataThank you very much for the help.
--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)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-resultsAnd 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
myDataI really appreciate you. 😊