Create Multiple Sites in SharePoint Online using Power Automate from Excel

In this Power Automate solution, we aim to automate the bulk creation of SharePoint sites based on data from an Excel file using Power Automate. I will create a complete flow that will run daily and create multiple sites in sharepoint online using Power Automate from Excel.

The Excel file includes columns for various site details, and we will create three types of SharePoint Online sites:

1. SharePoint Online Communication site.
2. SharePoint Online Team site (Group connected)
3. SharePoint Online Team site (Non-group connected)

The automation process will run daily, extracting data from the Excel sheet. It will then check if the site type and site URL are empty. The flow will create the respective SharePoint site if the URL is empty based on the provided site type.

Additionally, we have integrated functionality to set storage limits for the SharePoint site and add members. After implementing these features, notifications will be sent to the site owner and site members to inform them that the site has been created.

If the storage limit of a SharePoint site equals 50GB, an approval request will be sent to the SharePoint administrator. Otherwise, the flow will proceed to create the SharePoint sites.

It’s important to note that SharePoint site owners have full control. In this flow, we designate two types of owners:

1. Temporary owner
2. Permanent owner

Initially, we assigned a temporary owner while creating the SharePoint site. Later, we added the permanent owner. After adding the permanent owner, we remove the temporary owner from the specific SharePoint site.

To address scenarios where the temporary owner and permanent owner are the same individual, we have implemented a condition that checks whether the temporary owner is not equal to the permanent owner before removing the temporary owner.

This flow facilitates the process of SharePoint site creation, storage limit management, member addition, and ownership assignment for efficient collaboration and management of SharePoint sites.

Create Multiple Sites in SharePoint Online using Power Automate from Excel

Here, we will see how to create a SharePoint site from Excel using Power Automate Schedule flow.

We will set up the Excel sheet before creating the flow in Power Automate. Create an Excel with the below columns and format it as a table. Then, store it in a SharePoint document library.

• Title 
• Site owner 
• Alias 
• Site Url
• Site Type
• Site description 
• Site members
• Max Storage limit
• Max storage warning limit

The Excel file with some records looks like below:

Let’s create the flow in 4 different parts in Power Automate.

The first part contains the following steps

• Schedule the flow
• Get the data from an Excel
• Initialize the variables

In the second part, we will create the communication site. Similarly, we will create a third part and 4th part for the Team site (Group connected) and the Team site (Non-Group connected), respectively.

Part-1 (For Bulk Site Creation using Power Automate)

Step 1: Log in to the Power Automate, then click on the +Create icon -> select Schedule cloud flow.

Then, provide the flow name and provide the date and time of the flow. Then click on Create.

You can see that recurrence action is added to the flow page, which will run the flow daily.

Step 2: Now we will get the data from Excel, so click on the +New step -> select List rows present in a table action. Then provide the below information:

• Location: Provide the location of Excel.
• Document Library: Provide the document library of an excel
• File: Select the Excel file.
• Table: Select the table of an excel.

Step 3: Now we will initialize 3 variables to store the temporary owner, Member mails, and Members. So, click on the +New step -> select Initialize variable action. Then provide the below information:

Name	Type	Value
Member	String
Member Email	String
Temporary owner	String	<provide flow owner name>

Part-2 (For Bulk Site Creation using Power Automate)

Step 4: Now we will check the site type = Communication site, and the site URL is blank or not. If it returns true, then we will create the Communication site.

Before that, click on the +new step, and select the Set variable action. Then provide the below information:

• Name: Select the name as Member
• Value: Select the Site member from dynamic content. That will automatically apply to each action

Then click on the Add an action-> then click on Condition action. Then provide the below information:

• Choose a value: Select Site type from dynamic content.
• Operator: Choose equal to.
• Choose a value: Write the ‘Communication site’.

Then click on the Add a row option, provide the below information

• Choose a value: Select Site Url from dynamic content.
• Operator: Choose equal to.
• Choose a value: Leave it as blank.

Step 5: Now we will check the storage limit off the communication site, for this, click on Add an action in the If yes part.

Then provide the below information:

• Choose a value: Provide the below expression:

int(items('Apply_to_each')?['Max Storage limit'])

• Operator: Choose is greater than
• Choose a value: Write the value as 51200

Then, in the if yes part, we will send an approval to the SharePoint administrator; if it is approved, we will create a SharePoint Online Communication site, else, we will send an email is rejected.

In the If no part, we can directly create the communication site, because the storage limit is less than 50 GB.

Step 6: In the If yes part of the condition (Storage limit for communication site), click on Add an action. Then select Start and wait for an approval action and provide the below information:

• Approval type: Select ‘Approve/Reject – First to respond’
• Title: Provide the title.
• Assigned to: Provide SharePoint administrator email.

Step 7: Next, we will check whether the Approval status is approved or rejected; for this, click on Add an action -> select Condition action. Then provide the below information:

• Choose a value: Select Outcome from dynamic content.
• Operator: Choose equal to.
• Choose a value: Write the ‘Approved’.

If the condition is true, then we will create a SharePoint Online Communication site, add members to it, and set the storage limit.

Step 8: Now we will create a communication site in SharePoint Online using the rest API calls. So, click on Add an action -> select Send an Http request to SharePoint action.

Then provide the below information:

• Site address -provide the SharePoint tenant address
• Method– Select method as Post, we are creating the SharePoint communication site
• Uri -We provide the ‘/_api/SPSiteManager/create’ to call the SharePoint API endpoint.
• Headers: Provide the headers like below:

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the essential information below to create a SharePoint communication site.

{
	"request": {
		"Title": "@{items('Apply_to_each')?['Title']}",
		"Url": "https://tsinfotechnologies.sharepoint.com/sites/@{items('Apply_to_each')?['Alias']}",
		"Description": "@{items('Apply_to_each')?['Site description']}",
		"Owner": "@{variables('Temporary owner')}",
		"Lcid": 1033,
		"WebTemplate": "SITEPAGEPUBLISHING#0",
		"SiteDesignId": "@{guid()}",
		"ShareByEmailEnabled": true
	}
} 

Step 9: Parse the previous step output JSON, so click on Add an action -> select parse JSON action. Then provide the below information:

• Content: Select the body(Send an HTTP request to SharePoint) from the dynamic content.
• Schema: To add the schema, click on the ‘Generate from sample’, then add the previous action json code and click on done. Now you can see the Schema.

Step 10: Set the storage limit using two parameters i.e. ‘ StorageMaximumLevel’ and ‘StorageWarningLevel’ using the SharePoint rest API call.

So, click on the +Add an action -> then select the ‘Send an HTTP request to SharePoint’ action, and provide the below information.

• Site Address: Select or provide the SharePoint Admin center address
• Method: Select POST, we are providing the storage limit.
• Uri: Provide the Uri as ‘_api/Microsoft.Online.SharePoint.TenantAdministration.Tenant/Sites(‘_api/Microsoft.Online.SharePoint.TenantAdministration.Tenant/Sites(‘@{body(‘Parse_JSON_3’)?[‘d’]?[‘Create’]?[‘SiteId’]}’)’)’, we are doing API calls to that site to set the storage limit of the site.
• Headers: Provide like the below code example (There are required while you are calling a rest API call)

{
  "Accept": "application/json;odata=nometadata",
  "Content-Type": "application/json;odata=nometadata",
  "If-Match": "*",
  "X-HTTP-Method": "MERGE"

• Body: Provide the below code example (There are required while you are calling a rest API call)to set the storage limit. Also, you can change the storage based on your requirement.

{
"StorageMaximumLevel": "@{items('Apply_to_each')?['Max Storage limit']}",
"StorageWarningLevel": "@{items('Apply_to_each')?['Maximum Storage Warning Limit ']}"
}

Step 11: To get the Group Id of the Member group of the communication site, for this, click on the + Add an action, then -> select ‘Send an HTTP request to SharePoint’, to get the group id.

Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select Get, we need to get the group id.
• Uri: Provide the Uri as ‘_api/web?$select=AssociatedMemberGroup/Id&$expand=AssociatedMemberGroup’, we are doing API calls to SharePoint to get the group id of the SharePoint site member group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "Accept": "application/json;odata=nometadata"
}

Now we will use parse the output of the above action to get the group id, so click on + Add an action -> select Parse json action -> then provide the below information:

• Content: Select the Body of the above action(Send an HTTP request to SharePoint) from the dynamic content
• Schema: To get schema, click on the ‘ Generate from sample’ and then provide the previous action code. You see, the schema is auto-generated.

Next, we will split the members, so, click on ‘Add an action’, then select Compose action, provide the below information:

• Inputs: Provide the below expression:

split(variables('Member'), ',')

We will add members to the SharePoint Communication site, so, click on the Add an action -> select Apply to each action. Then provide the below information:

• Select an output from previous steps: Select the output of the compose action from dynamic content.

In the Apply to each action, click on add an action, then select Search for user(v2) action, and provide the below information:

• Search term: Select the current item from dynamic content.

After that, we will add an action ‘ Send an Http request to SharePoint’, then provide the below information

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are adding members to the site.
• Uri: Provide the Uri as ‘/api/web/sitegroups/getById(@{body(‘Parse_JSON-_Group_Id’)?[‘AssociatedMemberGroup’]?[‘Id’]})/users’, we are doing API calls to SharePoint to add members to the SharePoint site member group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the below code to add members to the site, The email we will pass from dynamic content, this will add Apply to each action.

{
    "__metadata": {
        "type": "SP.User"
    },
    "LoginName": "i:0#.f|membership|@{items('Apply_to_each_-add_members_to_site')?['Mail']}"
}

Finally, we will append the member email to the variable so that later, we can send an email to them. So click on Add an action -> select ‘ Append to string variable’ action. Then provide the below information:

• Name: Select the name as Member Email.
• Value: Select the Email from dynamic content

Step 12: Next, we will add the Permanent owner to the SharePoint site, so, click on Add an action-> select Search for user (V2) action. Then provide the below information:

• Search term: Select SIte owner from dynamic content.

Next, we will add a permanent owner to the site, so click on Add an action -> select Send an Http request to SharePoint action. Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are adding owner to the site.
• Uri: Provide the Uri as ‘/_api/web/sitegroups/getById(3)/users’, we are doing API calls to SharePoint to add owner to the SharePoint site owner group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the body like below:

{
    "__metadata": {
        "type": "SP.User"
    },
    "LoginName": "i:0#.f|membership|@{items('Apply_to_each_2')?['Mail']}"
}

Step 13: Now we will send an email to members and owners, click on the + Add an action -> select ‘Send an Email (V2)’ action. Then provide the below information:

• To: Select the Email from dynamic content, this will add apply to each action.
• Subject: Provide the subject of an email.
• Body: Provide the body of an email.

Click on Show Advanced Options.

• Cc: Select the Member emails from dynamic content.

Step 14: Now we will remove the temporary owner, so click on Add an action -> select Search for user (V2) action. Then, provide the below information.

• Search term: Select the Temporary owner variable from dynamic content.

We will check that the temporary owner is not equal to the Permanent owner, and then we will remove the temporary owner. For this, click on Add an action-> select Condition action. Then, provide the below information.

• Choose a value: Select a Temporary owner from the dynamic content
• Operator: Choose is not equal to.
• Choose a value: Select the Site owner from dynamic content.

Then in the If yes part of the condition, click on Add an action -> select Delay action. Then provide the below information:

• Count: Provide count as 45.
• Unit: Provide unit as seconds

We will get the temporary owner id, so click on Add an action -> select ‘Send an Http request to SharePoint’ action. Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select GET, as we are getting the id of owner.
• Uri: Provide the Uri as ‘api/web/SiteGroups(@{body(‘Parse_JSON_2’)?[‘AssociatedOwnerGroup’]?[‘Id’]})/users/GetByEmail(‘@{items(‘Apply_to_each-delete_temp_owner1′)?[‘Mail’]}’)’, we are doing API calls to get the temporary owner id.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Next, add an Compose action and provide the information below:

• Inputs: Provide the below expression:

@{outputs('Send_an_HTTP_request_to_SharePoint-_temporary_owner_id_2')?['body']['d']['id']}

Now we will delete the temporary owner, so, click on the Add an action -> select Send an HTTP request to SharePoint action. Then, provide the information below.

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are deleting the id of owner.
• Uri: Provide the Uri as _api/web/SiteGroups(@{body(‘Parse_JSON_2’)?[‘AssociatedOwnerGroup’]?[‘Id’]})/users/removeById(@{outputs(‘Compose’)})’, we are doing API calls to delete the temporary owner id.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Step 15: Now, we will set the Member mails to null and update the URL in Excel. So, click on Add an action-> select Set variable action. Then provide the below information:

• Name: Select name as Member Email.
• Value: Provide the expression like below:

null()

Next, we will update the URL, so, click on Add an action -> select Update a row action. Then, provide the information below.

• Location: Provide the location of Excel.
• Document Library: Provide the document library of an excel
• File: Select the Excel file.
• Table: Select the table of an excel.
• Key Column: Write ‘Title’.
• Key value: Select the Title from dynamic content.
• Site URL: Select the Site URL from dynamic content.

Step 16: Likewise, by following Steps 8-15, you can also create a communication site IF No part of the conditions related to storage limits apply.

Part-3 (For Bulk Site Creation using Power Automate)

Next, we will see how we will create a group connected team site in SharePoint using Power Automate. As we know when we create a group in Office 365, the SharePoint team site is associated with it.

Step 17: We will add the condition in the If no part of the Condition (Site type = Communication site), where we will check the Site type = Team site-Group connected.

For this, click on Add an Action in IF no part, select Condition action. Then provide the below information:

• Choose a value: Select the ‘Site type’ from the dynamic content.
• Operator: Choose an operator as ‘ is equal to’ from the dropdown.
• Choose a value: Here provide the value as ‘Team site-Group connected’.

Click on Add a row, provide the information

• Choose a value: Select Site Url from dynamic content.
• Operator: Choose equal to.
• Choose a value: Leave it as blank .

Now, if the condition true, we create a group-connected team site, else, we will create a non-group-connected team site. Let’s create true or If yes part of the condition.

Step 18: Now, we will check the storage limit off the Group-connected team site. For this, click on Add an action in the If yes part.

Then provide the below information:

• Choose a value: Provide the below expression:

int(items('Apply_to_each')?['Max Storage limit'])

• Operator: Choose is greater than
• Choose a value: Write the value as 51200

Then, in the if yes part, we will send an approval to the SharePoint administrator, if it is approved, we will create a SharePoint Online Group connected team site, else, we will send an email is rejected.

If no part, we can directly create the Group connected Team site, because the storage limit is less than 50 GB.

Step 19: In the If yes part of the condition(Storage limit for communication site), click on Add an action. Then select Start and wait for an approval action and provide the below information:

• Approval type: Select ‘Approve/Reject – First to respond’
• Title: Provide the title.
• Assigned to: Provide SharePoint administrator email.

Step 20: Next, we will check whether the Approval status is approved or rejected, for this, click on Add an action -> select Condition action. Then provide the below information:

• Choose a value: Select Outcome from dynamic content.
• Operator: Choose equal to.
• Choose a value: Write the ‘Approved’.

If the condition is true, then we will create a group connected team site, else, send an email to the requestor if a site greater than 50 Gb is rejected.

Step 21: Now, in the If yes part, click on Add an action, select ‘Send an HTTP request‘ -Office 365 group action, then provide the below information:

• Uri: Provide the API as ‘ https://graph.microsoft.com/v1.0/groups’, as we are creating an office 365 group using Graph api.
• Method: Select the POST, as we are creating the group
• Body: In body provide the json that contains information about group.

{
  "displayName": "@{items('Apply_to_each')?['Title']}",
  "mailEnabled": true,
  "mailNickname": "@{items('Apply_to_each')?['Alias']}",
  "securityEnabled": false,
  "groupTypes": [
    "Unified"
  ]
}

• Content-Type: As it is required for API calls so, the content type will be ‘ application/JSON

Step 22: Now we will get the site URL, so, click on Add an action -> select parse json action. Then provide the below information:

• Content: Select the Body(parse json) from the dynamic content
• Schema: To add schema, click on ‘Generate from sample’, then add the output json code from the above HTTp request action. Then click on Done. You can see the generated schema.

Next, click on add an action -> select Delay action, then provide the below information:

• Count: Select the count as 30
• Unit: Select the unit as seconds

Now we will get the site URL using Group ID, so click on Add an action-> select Send an HTTP request to SharePoint action. Then provide the below information:

• Site address: Provide the SharePoint administrator URL address
• Method: Select the Get method, as we get the Site URL of the SharePoint site.
• Uri: Provide the API to get the Site URL ‘_api/SP.Directory.DirectorySession/Group(‘@{body(‘Parse_JSON_5’)?[‘id’]}’)/SiteUrl’, to get the site URL.

Step 23: Now we will add members to the SharePoint site, so, click on Add an action, -> select Parse Json action. Provide the below information

• Content: Select the Body(parse json) from the dynamic content
• Schema: To add schema, click on ‘Generate from sample’, then add the output json code from the above Send an HTTp request to SharePoint action. Then click on Done. You can see the generated schema.

Next, click on Add an action -> select Compose action -> then provide the below information:

• Inputs: Provide the below expression:

split(variables('Member'), ',')

We will add members to the SharePoint Group connected team site, so, click on the Add an action -> select Apply to each action. Then provide the below information:

• Select an output from previous steps: Select the output of compose action from dynamic content.

In the Apply to each action click on add an action, then select Search for user(v2) action, provide the below information:

• Search term: Select the current item from dynamic content.

After that, we will add an action ‘ Send an Http request to SharePoint’, then provide the below information

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are adding members to the site.
• Uri: Provide the Uri as ‘/_api/web/sitegroups/getById(5)/users’, we are doing API calls to SharePoint to add members to the SharePoint site member group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the below code to add members to the site, the email we will pass from dynamic content, this will add Apply to each action.

{
    "__metadata": {
        "type": "SP.User"
    },
    "LoginName": "i:0#.f|membership|@{items('Apply_to_each_-add_member_to_group')?['Mail']}"
}

Finally, we will append the member email to the variable, so later we can send email to them. So click on Add an action -> select ‘ Append to string variable’ action. Then provide the below information:

• Name: Select the name as Member Email.
• Value: Select the Email from dynamic content

Step 24: Next, we will add owners to the site, so click on Add an action -> select Search for user action. Provide the information below:

• Search term: Select Site owner from dynamic content.

Similarly, create the action and provide the search term as Temporary owner.

Next, add Apply to each action, select Apply to each action for temporary owner, select the value from dynamic content like below image.

Similarly, add another Apply to each action for permanent owner, select the value from dynamic content

Inside both the Apply to each action -> add a Compose action, then provide the below information:

• Inputs: Provide the Inputs like below:

[
  @{items('Apply_to_each_-temp_owner')?['Mail']},
  @{items('Apply_to_each_-permanent_owner')?['Mail']}
]

We will add the owner to the site, so, click on Add an action -> select Apply to each action. Then provide the below information:

• Select an output from previous steps: Select an output of the compose action:

We will add owner to the site, so click on Add an action -> select Send an HTTP request to SharePoint action. Then provide the below information:

• Site address: Provide the SharePoint administrator URL address.
• Method: Select the Post method, as we add owners to the SharePoint site.
• Uri: Provide the API ‘/_api/web/sitegroups/getById(3)/users’ to add owners to the SharePoint site.
• Headers: Provide the header as shown below, as it is required for the rest API calls.

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the body like below to add owner to the site:

{
    "__metadata": {
        "type": "SP.User"
    },
    "LoginName": "i:0#.f|membership|@{items('Apply_to_each_-add_owner_to_the_site')}"
}

Step 25: Next, we will get the site id, and set the storage limit so click on Add an action -> select ‘Send an HTTP request to SharePoint’. Then provide the information below

• Site address: Provide the SharePoint administrator URL address.
• Method: Select the Get method, as we get the Site URL of the SharePoint site.
• Uri: Provide the API ‘_api/site?$select=Id’ to get the Site URL
• Headers: Provide the header as shown below, as it is required for the rest API calls.

{
  "accept": "application/json;odata=nometadata",
  "content-type": "application/json;odata=nometadata"
}

We will set the storage limit of the group connected team site. For this task, click on the Add an action, then select ‘Send an HTTP request to SharePoint‘. Then provide the following details:

• Site address: Provide the site URL from the dynamic content.
• Method: Select the POST method, as we will set the storage limit of the SharePoint site.
• Uri: Provide the API ‘api/Microsoft.Online.SharePoint.TenantAdministration.Tenant/Sites(‘@{outputs(‘Send_an_HTTP_request_to_SharePoint-_Get_site_id’)?[‘body’][‘Id’]}’)’ to set the storage limit.
• Headers: Provide the header as shown below, as it is required for the rest API calls.

{
  "Accept": "application/json;odata=nometadata",
  "Content-Type": "application/json;odata=nometadata",
  "X-HTTP-Method": "MERGE"
}

Body: Here, we will provide the Storage Maximum Limit and Storage Warning Limit. The sample body related to this is shown below.

{
"StorageMaximumLevel": "@{items('Apply_to_each')?['Max Storage limit']}",
"StorageWarningLevel": "@{items('Apply_to_each')?['Maximum Storage Warning Limit ']}"
}

Step 26: Next, we will send an email to the member and owner, so click on Add an action -> select ‘Send an Email (V2)’ action. Then provide the information below

• To: Select the Email from dynamic content, this will apply to each action.
• Subject: Provide the subject of an email.
• Body: Provide the body of an email.

Click on Show Advanced Options.

• Cc: Select the Member emails from dynamic content.

Step 27: We will check temporary owner is not equal to the Permanent owner, and then we will remove the temporary owner. For this click on Add an action-> select Condition action. Then provide the information below.

• Choose a value: Select a Temporary owner from the dynamic content
• Operator: Choose is not equal to.
• Choose a value: Select the Site owner from dynamic content.

Then in the If yes part of the condition, click on Add an action -> select Delay action. Then provide the below information:

• Count: Provide count as 45.
• Unit: Provide unit as seconds

We will get the temporary owner id, so click on Add an action -> select ‘Send an Http request to SharePoint’ action. Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select GET, as we are getting the id of owner.
• Uri: Provide the Uri as ‘api/web/SiteGroups(@{body(‘Parse_JSON_2’)?[‘AssociatedOwnerGroup’]?[‘Id’]})/users/GetByEmail(‘@{items(‘Apply_to_each-delete_temp_owner1′)?[‘Mail’]}’)’, we are doing API calls to get the temporary owner id.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Next, add an Compose action and provide the information below:

• Inputs: Provide the below expression:

@{outputs('Send_an_HTTP_request_to_SharePoint-_temporary_owner_id_2')?['body']['d']['id']}

Now we will delete the temporary owner, so, click on the Add an action -> select Send an HTTP request to SharePoint action. Then, provide the below information.

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are deleting the id of owner.
• Uri: Provide the Uri as _api/web/SiteGroups(@{body(‘Parse_JSON_2’)?[‘AssociatedOwnerGroup’]?[‘Id’]})/users/removeById(@{outputs(‘Compose’)})’, we are doing API calls to delete the temporary owner id.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Step 28: Now, we will set the Member mails to null and update the URL in Excel. So, click on Add an action-> select Set variable action. Then provide the below information:

• Name: Select name as Member Email.
• Value: Provide the expression like below:

null()

Next, we will update the URL, so, click on Add an action -> select Update a row action. Then, provide the information below.

• Location: Provide the location of Excel.
• Document Library: Provide the document library of an excel
• File: Select the Excel file.
• Table: Select the table of an excel.
• Key Column: Write ‘Title’.
• Key value: Select the Title from dynamic content.
• Site URL: Select the Site URL from dynamic content.

Step 29: Likewise, by following Steps 21-28, you can also create a Group connected team site, in the IF No part of the conditions related to storage limits apply.

Part-4 (For Bulk Site Creation using Power Automate)

Next, we will see how we will create non-group connected team site. For this, follow the below steps:

Step 30: We will add the condition in the If no part of the Condition (Site type = Team site -group connected site), where we will check the Site URL is empty. If the condition is true, check the storage limit.

For this, click on Add an action, in the If No part, click on Add an action -> select Condition action. Then provide the below information:

• Choose a value: Select the site URL from dynamic content.
• Operator: Select is equal to operator.
• Choose a value: Leave it blank.

If the condition is true, we will check the Storage limit, based on that, we will create Non-group connected team site.

Step 31: Next, in the IF yes part, click on Add an action -> select Condition action. Then provide the below information:

• Choose a value: Provide the below expression:

int(items('Apply_to_each')?['Max Storage limit'])

• Operator: Choose is greater than
• Choose a value: Write the value as 51200

Then in the if yes part, we will send an approval to SharePoint Adminstrator, if it is approved we will create a SharePoint Online Non-Group connected site, else, we will send an email is rejected.

In the If no part we can directly create the communication site, because storage limit is less than 50 GB.

Step 32: In the If yes part of the condition(Storage limit for Non group connected team site), click on Add an action. Then select Start and wait for an approval action and provide the below information:

• Approval type: Select ‘Approve/Reject – First to respond’
• Title: Provide the title.
• Assigned to: Provide SharePoint administrator email.

Step 33: Next, we will check the Approval status is approved or rejected, for this, click on Add an action -> select Condition action. Then provide the below information:

• Choose a value: Select Outcome from dynamic content.
• Operator: Choose equal to.
• Choose a value: Write the ‘Approved’.

If the condition true, then we will create SharePoint Online non-group connected team site, add members to it and set the storage limit.

Step 34: Now we will create a non-group connected team site in SharePoint Online using rest API call. So, click on Add an action -> select Send an Http request to SharePoint action.

Then provide the below information:

• Site address -Provide the SharePoint tenant address
• Method– Select method as Post, we are creating the SharePoint non-group connected team site
• Uri -We provide the ‘/_api/SPSiteManager/create’ to call the SharePoint API endpoint.
• Headers: Provide the headers like below:

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the essential information below to create a SharePoint communication site.

{
	"request": {
		"Title": "@{items('Apply_to_each')?['Title']}",
		"Url": "https://tsinfotechnologies.sharepoint.com/sites/@{items('Apply_to_each')?['Alias']}",
		"Description": "@{items('Apply_to_each')?['Site description']}",
		"Owner": "@{variables('Temporary owner')}",
		"Lcid": 1033,
		"WebTemplate": "STS#3",
		"SiteDesignId": "@{guid()}",
		"ShareByEmailEnabled": true
	}
}

Step 35: Parse the previous step output JSON, so click on Add an action -> select parse JSON action. Then provide the below information:

• Content: Select the body(Send an HTTP request to SharePoint) from the dynamic content.
• Schema: To add the schema, click on the ‘Generate from sample’, then add the previous action JSON code and click on done. Now you can see the Schema.

Step 36: To set the storage limit using two parameters i.e. ‘ StorageMaximumLevel’ and ‘StorageWarningLevel’ using the SharePoint rest API call.

So, click on the +Add an action -> then select the ‘Send an HTTP request to SharePoint’ action, and provide the below information.

• Site Address: Select or provide the SharePoint Admin center address
• Method: Select POST, we are providing the storage limit.
• Uri: Provide the Uri as ‘_api/Microsoft.Online.SharePoint.TenantAdministration.Tenant/Sites(‘@{body(‘Parse_JSON_5’)?[‘d’]?[‘Create’]?[‘SiteId’]}’)’, we are doing API calls to that site to set the storage limit of the site.
• Headers: Provide like the below code example (There are required while you are calling a rest API call)

{
  "Accept": "application/json;odata=nometadata",
  "Content-Type": "application/json;odata=nometadata",
  "If-Match": "*",
  "X-HTTP-Method": "MERGE"

• Body: Provide the below code example (There are required while you are calling a rest API call)to set the storage limit. Also, you can change the storage based on your requirement.

{
"StorageMaximumLevel": "@{items('Apply_to_each')?['Max Storage limit']}",
"StorageWarningLevel": "@{items('Apply_to_each')?['Maximum Storage Warning Limit ']}"
}

Step 37: To get the Group Id of Member group of non group connected team site, for this click on the + Add an action, then -> select ‘Send an HTTP request to SharePoint’, to get the group id.

Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select Get, we need to get the group id.
• Uri: Provide the Uri as ‘_api/web?$select=AssociatedMemberGroup/Id&$expand=AssociatedMemberGroup’, we are doing API calls to SharePoint to get the group id of the SharePoint site member group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "Accept": "application/json;odata=nometadata"
}

Now we will use parse the output of above action to get the group id, so click on + Add an action -> select Parse json action -> then provide the below information:

• Content: Select the Body of the above action(Send an HTTP request to SharePoint) from the dynamic content
• Schema: To get schema, click on the ‘ Generate from sample’ and then provide the previous action code. You see the schema is auto-generated.

Next, we will split the members, so, click on ‘Add an action’, then select compose action, provide the below information:

• Inputs: Provide the below expression:

split(variables('Member'), ',')

We will add members to the SharePoint Non group connected team site, so, click on the Add an action -> select Apply to each action. Then provide the below information:

• Select an output from previous steps: Select the output of compose action from dynamic content.

In the Apply to each action click on add an action, then select Search for user(v2) action, and provide the below information:

• Search term: Select the current item from dynamic content.

After that, we will add an action ‘ Send an Http request to SharePoint’, then provide the below information

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are adding members to the site.
• Uri: Provide the Uri as ‘/api/web/sitegroups/getById(@{body(‘Parse_JSON-_Group_Id’)?[‘AssociatedMemberGroup’]?[‘Id’]})/users’, we are doing API calls to SharePoint to add members to the SharePoint site member group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the below code to add members to the site, the email we will pass from dynamic content, this will add Apply to each action.

{
    "__metadata": {
        "type": "SP.User"
    },
    "LoginName": "i:0#.f|membership|@{items('Apply_to_each_-add_members_to_site')?['Mail']}"
}

Finally, we will append the member email to the variable, so later we can send email to them. So click on Add an action -> select ‘ Append to string variable’ action. Then provide the below information:

• Name: Select the name as Member Email.
• Value: Select the Email from dynamic content

Step 38: Next, we will add the Permanent owner to the SharePoint site, so, click on Add an action-> select Search for user (V2) action. Then provide the below information:

• Search term: Select SIte owner from dynamic content.

Next, we will add a permanent owner to the site, so click on Add an action -> select Send an Http request to SharePoint action. Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are adding owner to the site.
• Uri: Provide the Uri as ‘/_api/web/sitegroups/getById(3)/users’, we are doing API calls to SharePoint to add owner to the SharePoint site owner group.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

• Body: Provide the body like below:

{
    "__metadata": {
        "type": "SP.User"
    },
    "LoginName": "i:0#.f|membership|@{items('Apply_to_each_2')?['Mail']}"
}

Step 39: Now we will send an email to member and owner, click on the + Add an action -> select ‘Send an Email (V2)’ action. Then provide the below information:

• To: Select the Email from dynamic content; this will add apply to each action.
• Subject: Provide the subject of an email.
• Body: Provide the body of an email.

Click on Show Advanced Options.

• Cc: Select the Member emails from dynamic content.

Step 40: Now we will remove the temporary owner, so click on Add an action -> select Search for user (V2) action. Then, provide the information below.

• Search term: Select the Temporary owner variable from dynamic content.

We will check that the temporary owner is not equal to the Permanent owner, and then we will remove the temporary owner. For this, click on Add an action-> select Condition action. Then, provide the information below.

• Choose a value: Select a Temporary owner from the dynamic content
• Operator: Choose is not equal to.
• Choose a value: Select the Site owner from dynamic content.

Then in the If yes part of the condition, click on Add an action -> select Delay action. Then provide the below information:

• Count: Provide count as 45.
• Unit: Provide unit as seconds

We will get the temporary owner id, so click on Add an action -> select ‘Send an Http request to SharePoint’ action. Then provide the below information:

• Site Address: Select or provide the site address from the dynamic content
• Method: Select GET, as we are getting the id of owner.
• Uri: Provide the Uri as ‘api/web/SiteGroups(@{body(‘Parse_JSON_2’)?[‘AssociatedOwnerGroup’]?[‘Id’]})/users/GetByEmail(‘@{items(‘Apply_to_each-delete_temp_owner1′)?[‘Mail’]}’)’, we are doing API calls to get the temporary owner id.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Next, add an Compose action and provide the information below:

• Inputs: Provide the below expression:

@{outputs('Send_an_HTTP_request_to_SharePoint-_temporary_owner_id_2')?['body']['d']['id']}

Now we will delete the temporary owner, so, click on the Add an action -> select Send an HTTP request to SharePoint action. Then, provide the information below.

• Site Address: Select or provide the site address from the dynamic content
• Method: Select POST, as we are deleting the id of owner.
• Uri: Provide the Uri as _api/web/SiteGroups(@{body(‘Parse_JSON_2’)?[‘AssociatedOwnerGroup’]?[‘Id’]})/users/removeById(@{outputs(‘Compose’)})’, we are doing API calls to delete the temporary owner id.
• Headers: Provide like the below code example (There are required while you are calling a rest API call).

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Step 41: Now, we will set the Member mails to null and update the URL in Excel. So, click on Add an action-> select Set variable action. Then provide the below information:

• Name: Select name as Member Email.
• Value: Provide the expression like below:

null()

Next, we will update the URL, so, click on Add an action -> select Update a row action. Then, provide the information below.

• Location: Provide the location of Excel.
• Document Library: Provide the document library of an excel
• File: Select the Excel file.
• Table: Select the table of an excel.
• Key Column: Write ‘Title’.
• Key value: Select the Title from dynamic content.
• Site URL: Select the Site URL from dynamic content.

Step 42: Likewise, by following Steps 34-41, you can also create a Non-group connected team site IF No part of the conditions related to storage limits apply.

Step 43: Now run the flow and click on Save. You can see the site URL is updated once the site in SharePoint is created successfully.

Conclusion

In this Power Automate tutorial, we saw a real example how to create bulk sites or multiple sites in SharePoint Online using Power Automate from an Excel file.

The power automate flow, which will automatically create 3 different SharePoint Online sites is listed below from Excel.

• Communication site
• Group connected Team site
• Non-Group connected team site

You may also like:

• Send Email Reminders from a SharePoint List using Power Automate
• How to Send an Email with Attachments in Power Automate?
• Send an Email with Attachments from Local Folder using Power Automate
• How to loop through XML data and insert into Excel using Power Automate?