What is OData API
OData is an open data access protocol to provide access to a data source on a website. It is used, for example, in Azure and in SharePoint, and by Project Online to access PWA metadata. Because those metadata are accessible through OData, API now can provide a native built-in method to fetch data in Project Online, as opposed to relying on third party products.
Excel can be used to analyse PWA metadata using OData API. When accessing the project metadata using OData API in Excel (see steps explained below), the error message below may appear:
With some investigation we discovered that one of the created enterprise custom field (CF) is named “Project#”. This custom field is conflicting with one of the out-of-the-box field, hence the error message above.
The solution for this error is simply removing this custom field (CF) or renaming it to something else.
If you don’t know how to get the OData API using Excel, here is a step-by-step guide for it:
- In Excel, go to the Data tab (Power Query tab for Excel 2010-2013) and select Get Data > From Other Sources > From OData Feed.
- Enter your PWA URL as follows “https://MyDemo.sharepoint.com/sites/pwa“ and add at the end: “/_api/ProjectData” so the final URL will look like this: “https://MyDemo.sharepoint.com/sites/pwa/_api/ProjectData“, and then click OK. This URL will return all PWA metadata data for that site.
- You’ll then be prompted with the following screen to choose Organizational account for Project Online and Windows for Project Server 2013 and 2016. Sign in by entering your Username and Password..
- You will now get all the tables in Project level, Task level and SharePoint level (Projects, Tasks, Risks, Issues, etc.)