Forum Discussion
REST API Call with Cookie Session ID
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
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).
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)
6 Replies
- ZhangKunSuper User
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"
])])
- bdalagerRegular Visitor
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.
- ZhangKunSuper User
--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)