Rally Software

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

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

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

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

@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