Comparing User Job Title to Role Name in SharePoint Permission Groups

[RyIrv__]

We have users that come and go from our department often, however the job roles stay the same and it simply gets passed from person to person. I'm hoping to automate the process of adding people to a temporary SharePoint permissions group using their name and once they have been assigned the role of their predecessor they will automatically be removed from the SharePoint group. 

I've already mostly planned the flow that will add the users to the temporary group by having users fill out an arrivals form before they start the job, the flow will grab the user's details and add them to the permissions group using a HTTP POST request.

The flow I'm planning to create to remove users looks at a SharePoint Permission Group (or multiple) and compares a user's job title with a job role in the SharePoint Group. In example a user would look like 'John Doe (IT_TECH1)' and the job role in the permission group would just be IT_TECH1.

The flow would ideally check the user's job title, check all permission groups to see if a role of the same type exists and if it does it will remove the user. The user themselves will be unaffected by this change as they will inherit all of their permissions from the job role permissions anyway.

The idea behind this is to try and automate the process of adding and removing users using the HTTP GET and POST requests.

I'm using these two documents as reference:

How to add permissions to SharePoint group with Power Automate (tomriha.com)

Remove user from a SharePoint group with Power Automate flow (tomriha.com)

Apart from having the flow run on a monthly or two weekly schedule I have no idea how to even start this flow, let alone how to compare people's job titles with job roles across permission groups!!

Is this even possible and how would I go about it?

Any help would be greatly appreciated!!

Cheers,

Ry

[RyIrv__]

So. I've made some progress.

I'm able to get the data I need from my two test permission groups using the 'Send an HTTP request to SharePoint' action.

I've currently got two parallel actions that use the GET method to get the user data from two different permission groups with the below Uri:

_api/web/sitegroups(19194)/users

This gets all the user data from the specified permission group and the output looks as below (personal details replaced)

{
  "d": {
    "results": [
      {
        "__metadata": {
          "id": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(5447)",
          "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(5447)",
          "type": "SP.User"
        },
        "Alerts": {
          "__deferred": {
            "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(5447)/Alerts"
          }
        },
        "Groups": {
          "__deferred": {
            "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(5447)/Groups"
          }
        },
        "Id": 5447,
        "IsHiddenInUI": false,
        "LoginName": "i:0#.f|membership|(user email)",
        "Title": "Doe, John (TECH_1)",
        "PrincipalType": 1,
        "Email": "(user email)",
        "Expiration": "",
        "IsEmailAuthenticationGuestUser": false,
        "IsShareByEmailGuestUser": false,
        "IsSiteAdmin": false,
        "UserId": {
          "__metadata": {
            "type": "SP.UserIdInfo"
          },
          "NameId": "10032000a0f46068",
          "NameIdIssuer": "urn:federation:microsoftonline"
        },
        "UserPrincipalName": "(user email)"
      },
      {
        "__metadata": {
          "id": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(2916)",
          "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(2916)",
          "type": "SP.User"
        },
        "Alerts": {
          "__deferred": {
            "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(2916)/Alerts"
          }
        },
        "Groups": {
          "__deferred": {
            "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(2916)/Groups"
          }
        },
        "Id": 2916,
        "IsHiddenInUI": false,
        "LoginName": "i:0#.f|membership|(user email)",
        "Title": "Doe, Jack (TECH_BOSS)",
        "PrincipalType": 1,
        "Email": "(user email)",
        "Expiration": "",
        "IsEmailAuthenticationGuestUser": false,
        "IsShareByEmailGuestUser": false,
        "IsSiteAdmin": false,
        "UserId": {
          "__metadata": {
            "type": "SP.UserIdInfo"
          },
          "NameId": "100320016aeb9c38",
          "NameIdIssuer": "urn:federation:microsoftonline"
        },
        "UserPrincipalName": "(user email)"
      },
      {
        "__metadata": {
          "id": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(12467)",
          "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(12467)",
          "type": "SP.User"
        },
        "Alerts": {
          "__deferred": {
            "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(12467)/Alerts"
          }
        },
        "Groups": {
          "__deferred": {
            "uri": "https://(sharepointURL)/teams/(teamsnumber)/_api/Web/GetUserById(12467)/Groups"
          }
        },
        "Id": 12467,
        "IsHiddenInUI": false,
        "LoginName": "i:0#.f|membership|(user email)",
        "Title": "Doe, James (TECH_2)",
        "PrincipalType": 1,
        "Email": "(user email)",
        "Expiration": "",
        "IsEmailAuthenticationGuestUser": false,
        "IsShareByEmailGuestUser": false,
        "IsSiteAdmin": false,
        "UserId": {
          "__metadata": {
            "type": "SP.UserIdInfo"
          },
          "NameId": "1004200040379619",
          "NameIdIssuer": "urn:federation:microsoftonline"
        },
        "UserPrincipalName": "(user email)"
      }
    ]
  }
}

 and

{
  "d": {
    "results": [
      {
        "__metadata": {
          "id": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9840)",
          "uri": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9840)",
          "type": "SP.User"
        },
        "Alerts": {
          "__deferred": {
            "uri": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9840)/Alerts"
          }
        },
        "Groups": {
          "__deferred": {
            "uri": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9840)/Groups"
          }
        },
        "Id": 9840,
        "IsHiddenInUI": false,
        "LoginName": "c:0t.c|tenant|0e130255-5413-4d94-941b-510e7ceb33b1",
        "Title": "TECH_1",
        "PrincipalType": 4,
        "Email": "",
        "Expiration": "",
        "IsEmailAuthenticationGuestUser": false,
        "IsShareByEmailGuestUser": false,
        "IsSiteAdmin": true,
        "UserId": null,
        "UserPrincipalName": null
      },
      {
        "__metadata": {
          "id": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9841)",
          "uri": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9841)",
          "type": "SP.User"
        },
        "Alerts": {
          "__deferred": {
            "uri": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9841)/Alerts"
          }
        },
        "Groups": {
          "__deferred": {
            "uri": "https://(sharepointurl)/teams/(teamsnumber)/_api/Web/GetUserById(9841)/Groups"
          }
        },
        "Id": 9841,
        "IsHiddenInUI": false,
        "LoginName": "c:0t.c|tenant|47356ff2-0203-7df4-3dfa-0993929f7cbf",
        "Title": "TECH_2",
        "PrincipalType": 4,
        "Email": "",
        "Expiration": "",
        "IsEmailAuthenticationGuestUser": false,
        "IsShareByEmailGuestUser": false,
        "IsSiteAdmin": false,
        "UserId": null,
        "UserPrincipalName": null
      }
    ]
  }
}

From this I hope to filter out all the gunk to have only the following information from each http request; ID, Title & PrincipalType (The PrincipalType shows whether the entry is a User (1) or Security Group/Role (4))

I then want to place that information into an array / query that would look like the below:

For HTTP Request 1

ID	Title	PrincipalType
5447	Doe, John (TECH_1)	1
2616	Doe, Jack (TECH_BOSS)	1
12467	Doe, James (TECH_2)	1

For HTTP Request 2

ID	Title	Principal Type
9840	TECH_1	4
9841	TECH_2	4

From there I can compare the arrays from request 1 and request 2 for any duplicate. In this case there would be two duplicates - TECH_1 and TECH_2. That being the case I would then delete the two entries from the first permission group; Doe, John (TECH_1) and Doe, James (TECH_2) using the HTTP Post requests that I mentioned in my first post.

However! And it is quite a big however, I am struggling to filter the HTTP request and place it in an array. I was attempting to use the Compose action to only output the required data using the below uri (I was testing with just the key 'Id' and if it worked I'd do it with 'Title' and 'PrincipalType' too)

body('Send_an_HTTP_request_to_SharePoint')?['__metadata']?['Id']

However although the flow is successful it shows a blank input and output. I'm not sure where to go from here as attempting to use the Select or Filter array actions fail due to the HTTP Request being an object not an array.

Once again, any help would be greatly appreciated!!

All the best,

Ry

[RyIrv__]

Another breakthrough!! This thread is just turning into my own little journal but for those of you who are enjoying watching my coffee fuelled power automate riddled descent into madness, enjoy!!

So I've finally managed to get the data I require, I've grabbed all the data using a HTTP request, I've parsed that data as a JSON and I've used the compose action to grab each iteration of the JSON with the data I require.

My flow now looks like this:

and the data outputted looks like this (personal details, once again, removed):

HTTP Request 1

- Compose 1

5447; Doe, John (TECH_1); 1

- Compose 2

2916; Doe, Jack (TECH_BOSS); 1

- Compose 3

12467; Doe, James (TECH_2); 1

HTTP Request 2

- Compose 1

9840; TECH_1; 4

- Compose 2

9841; TECH_2; 4

So, looking good right? Right! I can grab the data I require and I can scrape off all the gunk so I'm only left with the data I need.

Now the question is, how can I take each of the compose outputs and input them into an array so all the compose data from each HTTP request is stored in a nice, neat package?

Once I've got that all I need to do is take my two arrays from each HTTP Request, compare them to each other and check for duplicates.

I look forward to any suggestions!!

Until the next cup of coffee,

Ry