Rally Software

Expand all | Collapse all

SSO authentication, API Key and Excel Power Query

Jump to Best Answer
  • 1.  SSO authentication, API Key and Excel Power Query

    Posted 08-22-2017 09:42 AM

    Hello,

     

    I am a user with SSO authentification.

    I tried to access Milestones with the CA Excel addin, but I discovered this is not yet implemented.

     

    I then tried to build my own queries with the WS, and it seems to work.

    I implemented a API KEY in the CA Agile Central App management.

    I have now a functionning query and a API KEY.

     

    I tried to have it automated with Excel Power Query, but it keep failing in authenticating.

    My query is :

    let
    Source = Web.Contents("https://rally1.rallydev.com/slm/webservice/v2.0/milestone?{...},[ApiKeyName="Mykey"])
    in
    Source

    It tells me I have to enter authentication information, I enter the API Key, and it fails... same question again...

     

    Would you have an idea please ?

     

    My best regards,

     

    Greg



  • 2.  Re: SSO authentication, API Key and Excel Power Query

    Posted 08-22-2017 04:53 PM

    Hi Greg,

     

    I am not familiar with Excel Power Query and it is not a supported integration. That said, the APIKey needs to be sent as a key:value pair with ZSESSIONID as the key. This key:value pair can be sent in a cookie or in the request header. If there is a way to do that within Excel Power Query then that should work. 

     

    Let me know if that helps,

    -Sean Davis



  • 3.  Re: SSO authentication, API Key and Excel Power Query
    Best Answer

    Posted 08-29-2017 02:47 AM

    Hello,

     

    Just to let everyone know I managed in getting it working using the following query :

    let

    Source = Json.Document(Web.Contents(url_to_query, [Headers=[APIToken=THEAPIToken, ZSESSIONID=THEZSESSIONID]]))

    in

    Source

     

    The only limitation I have identified is the 2000 limit in API results.

     

    Have a nice day!

     

    Grégoire



  • 4.  Re: SSO authentication, API Key and Excel Power Query

    Posted 08-29-2017 11:20 AM

    Hi Grégoire,

     

    You can use the start URL parameter in combination with pagesize in subsequent queries to get the results beyond 2000. For example:

     

    <URL>?pagesize=2000&start=2001

     

    This will return the results starting at 2001 up to 4000.

     

    Hope that helps.

    Sean Davis



  • 5.  Re: SSO authentication, API Key and Excel Power Query

    Posted 02-04-2018 11:29 AM

    @GregZveg

    Thank you so much for your question and answers, they have helped me a lot.

    I'm trying to create an automated report based on Rally daya using Power Query.

    I managed to make it work with the info you provided above.

    But I would like to distribute it so that my managers can run it alone. However, I would like them to set their own API Key using the Power Query credentials UI instead of going into the M code.

     

    Did you manage to find a way to pass the API Key name "ZSESSIONID" as parameter so the Key can be input in credentials UI?

     

    Thanks a million,

    Karim