You will need an API token so Excel can access your saved report, but you do not need API data references to get the data into Excel.
- Save the report
- Copy the report URL
- Place the report URL and API token in Excel Power Query
- Access Excel Power Query from Excel using the Data > From Web selection
- Use the Advanced setting to place the token in the API request header parameter parameters
- Load the query to get your report
- Save the resulting Excel spreadsheet
- Use Data > Refresh All any time you open the report
- This queries the report for the latest real-time information
The advantages to this process over taking that same report and exporting it to Excel goes far beyond skipping a few steps navigating to the report in your browser.
Power Query can step through data field manipulation so you do it one time and every data refresh automatically performs all of those steps.
Common changes could be data transformation and custom data calculations.
Do it once and let Excel automation through Power Query do it with every data refresh going forward.
Comments
3 comments
Hey Wes
Good Day to you. I was reading through your articles and seen the below link which allows me to get my Project or Tasks related data into Excel using API's.
https://support.projectinsight.net/hc/en-us/articles/360001223823-Get-your-Project-Data-Directly-to-Excel
This works great and i was able to get the desired data into excel.
I seen the other article of getting the saved reports into Excel (this article). I tried the steps and when i link i see a lot of options. One of them is the data list (which is data in the table form). I have a graph in this report. I am not able to see the graph. see screen shots below
Do you have an idea on how we can display the graph?
Is there a way to show both the graph and the data?
Appreciate your response.
Thanks
Mario
Mario,
I did some tests this AM, and we can provide images with the token. However, the challenge is, the graph is a JavaScript output that creates SVG, and not an image (.jpg, .png, etc):
Therefore, a direct link to the image doesn't exist. In order to do it the way you're asking, you'd need to somehow hack the existing data into a graphic format first, and then link to it. Right now, I don't know where an "alternate" is on our roadmap, but I will bring it up at the next developer meeting.
Another thing you could do is download the tabular data and recreate the charts in Excel. Then it will be a lot more seamless.
Thanks for sharing the valuable information.
Please sign in to leave a comment.