Rally REST API - how to call it from Excel to Extract Data

#RallyRESTAPI #Rally #Excel

CA Agile Rally Excel add-in is the easiest way to extract data from Rally to Excel.
But it's it the best method? I doubt that 🙂

In this video, I show how you can call the Rally REST API from Excel to get the data you want.

In this video I will explain how you can easily extract data from Rally to Excel using the Rally REST API, and why you would be interested in using this method instead of the CA Agile Central Add-in.

I will also explain something that is not mentioned clearly in the Rally documentation.
It's about how to get authenticated when calling the APIs.  

{If you'd like me to build an automated report/dashboard based on Rally data, or for any other business inquiries: Excelrati@gmail.com}

Welcome to my channel. My name is Karim and in this channel I try to share with you everything I know about Excel that would make your experience with Excel more efficient and fun.

I’ve been managing and tracking projects in Rally for several years. 
In many situations I got into a roadblock when trying to do some analysis or create complex dashboards. Hence, I had to extract the data to Excel and implement the logic and reports there.

The most obvious way to extract to Excel is using the Excel add-in provided with Rally. But when I realized that I could use the REST API as well, my life completely changed and I used the REST API most of the time.

At the end of the video I explain the pros and cons of using the REST API compared to using the Excel add-on.

Please make sure to hit that subscribe button. And if you have any questions after watching the video, please write them in the comments section below and I will try my best to answer them asap :)

BTW, the video is longer than my other videos because I'm presenting some useful links you should know about the API and explaining some concepts about it.
So feel free to watch it at 1.5x 2x speed or skip to relevant seconds if needed.

So, there is great potential for automation using Power Query and the REST API.
On the other hand, if you need to extract  more than 2000 entities from same query, then you need to implement a query that will handle multiple calls, or what is called pagination.

But me personally, I will always prefer the REST API route 🙂

Thank you for watching this video, I hope you found it useful.

Here is the list of links from the slide presented:
WSAPI Documentation: https://rally1.rallydev.com/slm/doc/webservice/
Endpoint for Users Stories: https://rally.rallydev.com/slm/webservice/v2.0/requirement
Endpoint for Features: https://rally.rallydev.com/slm/webservice/v2.0/portfolioitem/Feature

API Token
https://rally1.rallydev.com/login/accounts/index.html#/keys
API Token Example: _sdf876sdkjhf8s7df6sdjfh

URL Encoder
https://meyerweb.com/eric/tools/dencoder/

And here is the USs query we built:
- Please combine the rows together into one string (Youtube didn't show it well when combined)
- Please replace the text between curly brackets {}

= Json.Document(Web.Contents("https://
rally1.rallydev.com/slm/webservice/v2.0/
requirement?query=(CreationDate%20%3C%20%22today%20-%2010%22)&project=
https://rally1.rallydev.
com/slm/webservice/v2.0/
project/{PROJECT_OBJECT_ID}&fetch=FormattedID,CreationDate&projectScopeUp=false&projectScopeDown=false&pagesize=15&start=1&order=DragAndDropRank", [Headers =[#"ZSESSIONID"="{API_TOKEN}"]]))
If you liked this video or find it useful, a thank in the form of a like/subscribe/comment/share or any combination of them would be much appreciated :)

Good bye and see you in the next video

Видео Rally REST API - how to call it from Excel to Extract Data канала إكسلراتي Excelrati