Sharepoint User Profile Details in PowerApps - Sha...

AeronC · ‎07-17-2019

Hello Everyone!

I'm trying to retrieve Sharepoint Online (SPO) User Profile properties using the Sharepoint REST API via a custom connector in PowerApps.

For an easy example: the DisplayName property of the current User.

(Note: I am actually trying to retrieve custom defined user profile properties "PrimaryArea", "SecondaryArea" etc... from SPO, due to the fact that 'Term Sets' can be defined for these properties; something that I believe to not be possible for the Azure AD user resource (even through custom schema extensions) or the Office365 user profile.)

Therefore I do not think the Office365Users or AzureAD connectors are a suitable alternative. Please let me know If I am wrong about this as it would solve my problem quickly.

I have a custom connector that gives the following HTTP response to a GET request;

{
  "odata.metadata": "https://example.sharepoint.com/_api/$metadata#Edm.String",
  "value": "Aeron Campbell"
}

with the headers;

{
  "cache-control": "private, max-age=0",
  "content-encoding": "gzip",
  "content-type": "application/json;odata=minimalmetadata;streaming=true;charset=utf-8",
  "dataserviceversion": "3.0",
  "date": "Wed, 17 Jul 2019 23:08:06 GMT",
  "expires": "Tue, 02 Jul 2019 23:08:06 GMT",
  "last-modified": "Wed, 17 Jul 2019 23:08:06 GMT",
  "microsoftsharepointteamservices": "16.0.0.9103",
  "ms-cv": "nvFF46pwAJADMbkfRgFydA.0",
  "p3p": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"",
  "request-id": "e345f19e-70aa-9000-0331-b91f46017274",
  "spclientservicerequestduration": "281",
  "sprequestguid": "e345f19e-70aa-9000-0331-b91f46017274",
  "strict-transport-security": "max-age=31536000",
  "vary": "Accept-Encoding",
  "x-aspnet-version": "4.0.30319",
  "x-content-type-options": "nosniff",
  "x-frame-options": "SAMEORIGIN",
  "x-ms-invokeapp": "1; RequireReadOnly",
  "x-powered-by": "ASP.NET",
  "x-sharepointhealthscore": "0",
  "x-sp-serverstate": "ReadOnly=0"
}

Notice how expires is before the date. Could this be the issue?

The custom connector is defined as an OpenAPI file below:

{
  "swagger": "2.0",
  "info": {
    "version": "1.0",
    "title": "SharePointUsers",
    "description": "Access Sharepoint User details using this API", 
    "contact": {
    "name": "Aeron Campbell",
    "email": "Aeron.Campbell@example.co.uk"
    }
  },
  "host": "example.sharepoint.com",
  "basePath": "/",
  "schemes": [
    "https"
  ],
  "consumes": [
    "application/json;odata=verbose"
  ],
  "produces": [
    "application/json;odata=verbose"
  ],
  "securityDefinitions": {
    "AAD": {
      "type": "oauth2",
      "flow": "implicit",
      "authorizationUrl": "https://login.windows.net/common/oauth2/authorize",
      "scopes": {}
    }
  },
  "paths": {
    "/_api/SP.UserProfiles.PeopleManager/GetMyProperties/DisplayName": {
      "get": {
        "description": "Get current Sharepoint user properties",
        "summary": "Get My Properties",
        "operationId": "GetSharepointUser",
        "x-ms-visibility": "important",
        "consumes": ["application/json;odata=verbose"],
        "produces": ["application/json;odata=verbose"],
        "responses": {
          "200": {
            "description": "OK",
            "schema": {
              "type": "object",
              "properties": {
                "value": {
                  "type": "string"
                }
              }
            }
          },
          "400": {
            "description": "Bad Request"
          },
          "500": {
            "description": "Internal Server Error"
          },
          "default": {
            "description": "Operation Failed."
          }
        }
      }
    }
  }
}

However when using the custom connector within Powerapps I keep getting the following error. (See attachment)

SharePointUsers.GetSharepointUser() failed: The data returned in the response is invalid.

Could this be because of how I have defined the JSON schema in the OpenAPI file? or becasue the response is stale? or just because of how Powerapps expects to recieve data?

I am fairly new to PowerApps, OpenAPI, REST API, HTTP Requests and the whole Microsoft Eco System/Platform as a whole so I would greatly apprectiate any advice regarding what I am trying to achieve!

I have used the following resource to create the custom connector and app registrations in Azure and whatnot...

https://powerapps.microsoft.com/en-us/blog/implementing-role-based-permission/

I have also looked at achieving this through a Flow using the information in this thread:

https://powerusers.microsoft.com/t5/General-Discussion/Role-based-security-groups-in-Powerapps-using...
Is this the correct way to produce HTTP requests from Powerapps?

As a sidenote, during the App Registration Step in the Azure Portal I have given the following API Permissions under Sharepoint:

User.Read.All

User.ReadWrite.All

I don't believe this step to be neccessary as I dont think these permissions relate to the Sharepoint REST API. Does any have more info regarding what these permissions are and what they relate to? As google has been no help.

Please let me know if what I am trying to achieve is possible in Powerapps and is the best/optimal way to retrieve SPO User Properties, as even the built in Sharepoint Connector cannot access user profiles.

Also are there plans to depreciate the use of the Sharepoint REST API and User Profiles in the near future? as a lot of the documentation is no longer being updated.

AeronC · ‎07-17-2019

As an update to this,

the reply actually recieved when in the Powerapp is XML.

I know the Sharepoint REST API defaults to XML, but why am I seeing a different result compared to when testing the connector in the powerapps edit connector screen?

<?xml version="1.0" encoding="utf-8"?>
<d:DisplayName xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml">
Aeron Campbell
</d:DisplayName>

Why does powerapps not supply the correct ACCEPT HTTP header with the request?

DeeTronSEAM · ‎09-30-2019

@AeronC I hope this reply doesn't get your hopes up, but I wanted to tell you two things:

1) Your post has to be one of the most clear and well-laid out (and pre-researched before asking) posts I've seen in these forums. I wish people did such a good job explaining their problem (and what they've tried already).

2) I am getting a "The data returned in the response is invalid" error in PowerApps from a Flow I built (which calls the MS Graph API). I get the expected returned JSON back into PApps but PApps still compains and I cannot figure out why?

So, even though I am not using a custom connector (I'm using the Action menu in PApps to bind to my Flow), I'm doubly disappointed that there has been no answers to your post. I.e. not only do you deserve one, but it might shed some light on my problem. Best wishes!

Anonymous · ‎04-28-2020

Hi,

Did you ever resolve this issue?

ceylon · ‎07-24-2020

Hello all!

First, I just wanted to also say thank you @AeronC. I am in agreement with @DeeTronSEAM about how your post was very clear and researched. You did such an amazing job on your post, it ultimately helped me to solve the problem!

Where your post helps the most is that I also needed custom defined UserProfileProperties as stated:

@AeronC wrote:

(Note: I am actually trying to retrieve custom defined user profile properties "PrimaryArea", "SecondaryArea" etc... from SPO, due to the fact that 'Term Sets' can be defined for these properties; something that I believe to not be possible for the Azure AD user resource (even through custom schema extensions) or the Office365 user profile.)

To achieve this I needed to modify the SharePoint REST API call to:

https://<site url>/_api/SP.UserProfiles.PeopleManager/GetMyProperties/UserProfileProperties

This returns a list of only the UserProfileProperties of the currently logged-in user in XML format

To retrieve this as JSON (which is required by PowerApps) an Accept header parameter needed to be sent along with the request. You can see this in the code section called "parameters". But you also need to set this header in PowerApps when you request the data. This is addressed later in my reply.

To be fair I started with your excellent Swagger file and made a few modifications to the "paths", "responses" -> "200", and "parameters" sections.

"paths"

"paths": {
    "/_api/SP.UserProfiles.PeopleManager/GetMyProperties/UserProfileProperties":

"responses" -> "200"

"200": {
            "description": "OK",
            "schema": {
              "type": "object",
              "properties": {
                "d": {
                  "type": "object",
                  "properties": {
                    "UserProfileProperties": {
                      "type": "object",
                      "properties": {
                        "__metadata": {
                          "type": "object",
                          "properties": {
                            "type": {
                              "type": "string",
                              "description": "type"
                            }
                          },
                          "description": "__metadata"
                        },
                        "results": {
                          "type": "array",
                          "items": {
                            "type": "object",
                            "properties": {
                              "Key": {
                                "type": "string",
                                "description": "Key"
                              },
                              "Value": {
                                "type": "string",
                                "description": "Value"
                              },
                              "ValueType": {
                                "type": "string",
                                "description": "ValueType"
                              }
                            }
                          },
                          "description": "results"
                        }
                      },
                      "description": "UserProfileProperties"
                    }
                  },
                  "description": "d"
                }
              }
            }
          },

"parameters"

"parameters": [
          {
            "name": "Accept",
            "in": "header",
            "required": true,
            "type": "string",
            "x-ms-visibility": "important",
            "default": "application/json;odata=verbose"
          }
        ]

Once these changes had been made I was able to successfully retrieve the data from SharePoint in a JSON format that was readable by PowerApps.

In my PowerApp I connected this custom connector and was able to access the data by using the following formula:

SharePointUsers.GetSharepointUser("application/json;odata=verbose").d.UserProfileProperties.results

Where:

SharePointUsers = the custom connection to SharePoint

GetSharepointUser("application/json;odata=verbose") = the connection Action plus the required Accept header parameter

d = the returned data element

UserProfileProperties = the profile properties collection including metadata

results = array of profile property items

As you can see from the snip bellow, I was able to load this data into a gallery. You can also see that my custom defined properties, "StatusCode", "ApproverGroup", and "MinuteWriter" are part of the results.

I have posted my full Swagger file below so that others may benefit. Thanks again @AeronC for your post.

{
  "swagger": "2.0",
  "info": {
    "version": "1.0",
    "title": "SharePointUsers",
    "description": "Access Sharepoint User details using this API",
    "contact": {
      "name": "Ceylon",
      "email": "ceylon@example.com"
    }
  },
  "host": "example.sharepoint.com",
  "basePath": "/",
  "schemes": [
    "https"
  ],
  "consumes": [
    "application/json;odata=verbose"
  ],
  "produces": [
    "application/json;odata=verbose"
  ],
  "paths": {
    "/_api/SP.UserProfiles.PeopleManager/GetMyProperties/UserProfileProperties": {
      "get": {
        "description": "Get current Sharepoint user properties",
        "summary": "Get My Properties",
        "operationId": "GetSharepointUser",
        "x-ms-visibility": "important",
        "consumes": [
          "application/json;odata=verbose"
        ],
        "produces": [
          "application/json;odata=verbose"
        ],
        "responses": {
          "200": {
            "description": "OK",
            "schema": {
              "type": "object",
              "properties": {
                "d": {
                  "type": "object",
                  "properties": {
                    "UserProfileProperties": {
                      "type": "object",
                      "properties": {
                        "__metadata": {
                          "type": "object",
                          "properties": {
                            "type": {
                              "type": "string",
                              "description": "type"
                            }
                          },
                          "description": "__metadata"
                        },
                        "results": {
                          "type": "array",
                          "items": {
                            "type": "object",
                            "properties": {
                              "Key": {
                                "type": "string",
                                "description": "Key"
                              },
                              "Value": {
                                "type": "string",
                                "description": "Value"
                              },
                              "ValueType": {
                                "type": "string",
                                "description": "ValueType"
                              }
                            }
                          },
                          "description": "results"
                        }
                      },
                      "description": "UserProfileProperties"
                    }
                  },
                  "description": "d"
                }
              }
            }
          },
          "400": {
            "description": "Bad Request"
          },
          "500": {
            "description": "Internal Server Error"
          },
          "default": {
            "description": "Operation Failed."
          }
        },
        "parameters": [
          {
            "name": "Accept",
            "in": "header",
            "required": true,
            "type": "string",
            "x-ms-visibility": "important",
            "default": "application/json;odata=verbose"
          }
        ]
      }
    }
  },
  "definitions": {},
  "parameters": {},
  "responses": {},
  "securityDefinitions": {
    "oauth2_auth": {
      "type": "oauth2",
      "flow": "accessCode",
      "authorizationUrl": "https://login.windows.net/common/oauth2/authorize",
      "tokenUrl": "https://login.windows.net/common/oauth2/authorize",
      "scopes": {}
    }
  },
  "security": [
    {
      "oauth2_auth": []
    }
  ],
  "tags": []
}