OData for Project Online

odata api rest microsoft project online code

Things taking a ridiculously long time for me to figure out is this month's theme.

I've had a few opportunities to investigate Project Online at work. Currently I've been asked to look into how a dashboard could be built. I started looking at the PowerBI app store and found a couple of candidates, but neither of them could connect to our Project Online data store. Looking up online, I found a lot of people with similar problems and no solutions - just frustration and closed issue tickets. I found very much the same, but a great thing about Microsoft products is the Graph API.

Which exists for SharePoint Online, but not Projects. OK, so the OData end point. OData is more programatic than the Graph one, I feel, with a more structured query builder than the endpoint-availability of Graph. Some gotchas struck me along the way:

  • I used OAuth2 to make this work with the users's access rights, so bear that in mind.
    • When specifying a Scope endpoint for authorization, don't use the microsoft.sharepoint-df.com domain, replace it with your own domain. In fact, rather than specifying all the scopes required, using https://[tenant].sharepoint.com/.default just uses whatever scopes have been granted to the end point.
    • Include the https://[tenant].sharepoint.com URI as an audience parameter to the OAuth2 token endpoint.
    • Send the token endpoint variables in a POST body rather than querystring, as the querystring is too long.
  • The URL you want to talk to for projects https://[tenant].sharepoint.com/sites/pwa/_api/ProjectServer/Projects.
    • Just accessing https://[tenant].sharepoint.com/sites/pwa/_api/ProjectServer/Projects does not show you projects, access https://[tenant].sharepoint.com/sites/pwa/_api/ProjectServer/Projects?$Top=1 to get the first one
  • Add the $expand=IncludeCustomFields in order to get custom fields included in the return values
  • If a Custom Field is a lookup, it makes it faster to create a lookup table of the possible values before looping through returned results
    • https://[tenant].sharepoint.com/sites/pwa/_api/ProjectServer/CustomFields?$expand=LookupTable/Entries&$filter=Name%20eq%20'{0}'&$select=InternalName,LookupTable/Entries/InternalName,LookupTable/Entries/FullValue where {0} is replaced with the name of the custom field to get the values for. The First InternalName field is the name of the custom field used in the next dot point. The LookupTable/Entries fields provides the name and value pair to populate the lookup table with.
    • https://[tenant].sharepoint.com/sites/pwa/_api/ProjectServer/Projects?$Top=20&$select=Description,Name,StartDate,StatusDate,Stage,PercentComplete&$expand=IncludeCustomFields gets you your project and your custom fields. Note that the custom fields are references to the values of the custom fields in the lookup.
      • Once you include the custom fields, you can download at most 20 projects at a time (note the $Top=20), so you'll need to loop through them.

I have now got individual Javascript and PHP apps that Single SignOn, token swap, and can return a list of projects w' custom fields showing the value of the field. Useful for our Project Online where Project Status is a custom field, kinda important for a portal page. This is giving me room to work through what Project Online information can be useful Dashboardified - if our PowerBI team can help get the original stuff working I can work in that environment but until then I can dashboard-practice out in the wilds.

The following links helped a lot to work this out.