Step 1

Create an Excel document for storing the Group Names, Users and Permissions to be added to your SharePoint site.

Columns used: Group Name, Site Permissions, Users. Once the data is populated, it needs to be converted into a Table.

Step 2

Custom group names can be assigned a permission level using SharePoint Role Definitions. The role definition is the permission levels associated with a role. The Role ID that is associated with the Role Definition can be found from the REST API, appending the url of your site: /_api/web/roledefinitions

In this sample, the Full Control permission level is associated with the Role ID 1073741829. We use this Role ID number to assign the Full Control permission level to the new SharePoint group.

Step 3

For the Users of the team site, you must follow the login syntax that is configured in the tenant. This can be found in the REST API siteusers properties /_api/web/siteusers

Users are comma separated on the same row (no spacing), and will later be split into an array using the Flow operations

Step 4

Convert the excel data to a Table in order for the flow connector to read it.

Step 5

In Flow, we use the ‘List rows present in a table’ action, then connect it to where your excel file is stored and select the Table that you created in the previous step to access the Group, Permission, and User columns in the excel file.

Step 6

Create a variable to store the Users from Excel into Flow. Use the ‘Initialize variable‘ action.

Step 7

Next, we use the ‘Apply to Each‘ action and group our 3 operations – (1) create group, (2) set levels for the SharePoint permission groups, and (3) add users to the SharePoint groups.

For the output, we want to select ‘value‘ or the list of items from the excel table.

Step 8

Next, we use the ‘Send an HTTP request to SharePoint’ action. This action is used to perform REST API functions.

For this step, we use the REST API format to create a SharePoint Group:

Uri: /_api/Web/SiteGroups
Method: POST
Header: {content-type:’application/json; odata=verbose’}
Body:{
“__metadata”: {
“type”: “SP.Group”
},
“Title”: “Demo Group – Contribute”,
“Description”: “Created through API in Flows.”
}

The method is POST to create the groups on the site. The ‘Body’ is using standard JSON formatting to update the metadata for the SP.Group property. The ‘Group Name’ comes from the excel column in our earlier steps, as it’s pulling the information from the excel Table.

Step 9

Next, we use the action ‘Send an HTTP request to SharePoint’ to set the permission levels (Role ID) to the SharePoint Groups.

Here is the format we use to assign permission levels using REST API:

Uri: /_api/web/roleassignments/addroleassignment(principalid=<>, roledefid=<>)
Method: POST

From the REST API sample, we can use it to assign the principal ID and the Role ID in the Flow. To apply the site permission level from the excel table and set them as the principle id, use this expression:

items(‘Apply_to_Each_Group’)?[‘Permissions’]

It is important to note that where you see the SharePoint icon next to d.Id, Flow has changed the icon after saving the workflow. It must first be created using the Expression option.

/_api/web/roleassignments/addroleassignment(principalid=<>, roledefid=<>)

Step 10

Next, we create a variable to store the Users. We want to choose the split(…) function, so it can extract the comma separated values from the users in the excel row

split(items(‘Apply_to_Each_Group’)?[‘Users’],’,’)

Step 11

For this step, we use the ‘Apply to each‘ action which will loop through all the users and permissions and add them to the SharePoint group. In this example I have renamed the action name in the header.

Here we use the variable ‘Users‘ from the previous step, then we use the ‘Send an HTTP request to SharePoint‘ action to perform a REST API operation to add users to the groups.

Step 12

Here is the REST API example to set site Permission levels for a group / user:

Uri: /_api/web/roleassignments/addroleassignment(principalid=<>, roledefid=<>)
Method: POST

1.) In the Uri, we want to use an expression:

body(‘Send_an_HTTP_request_to_SharePoint_-_Create_Group’)?[‘d’]?[‘Id’]

2.) In this step, the Headers are also important, as it tells the Flow what to expect for what kind of REST API operation it is about to perform. Here we are using ‘content-type‘ and ‘application/json; odata=verbose‘

3.) The body uses the standard JSON format:

{
“__metadata”: {
“type”: “SP.User”
},
“LoginName”: “@{concat(‘i:0#.f|membership|’,items(‘Each_User’))}”
}

The membership syntax can be found from the REST API  /_api/web/siteusers

Uri: /_api/web/sitegroups/GetById(<<GroupID>>)/users
Method: POST
Header: {content-type:’application/json; odata=verbose’}

Body: {
“__metadata”: {
“type”: “SP.User”
},
“LoginName”: “<<LoginName of User>>”
}