Forum Discussion

bdalager's avatar
bdalager
Regular Visitor
1 year ago
Solved

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

Url=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

 

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's avatar
    ZhangKun
    1 year ago

    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)

     

6 Replies

  • 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's avatar
      bdalager
      Regular 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=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's avatar
        ZhangKun
        Super 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)