create sites and assign AD Groups to the site to manage permissions. Since the SPO or PnP tools don't have methods to add AD groups to SharePoint sites, we had to get creative. The solution for this was to use site scripts to assign the roles with a site design. The basic structure of the script is:
- Connect to SharePoint
- Load the CSV
- Create a site script based on the information in the CSV
- Set our site design to use that script
- Provision the new site
- Apply the updated site design
- Remove the site script to make room for the next
If you ask me, it's less straight forward than it should be to add AD groups to SharePoint permission groups. That said, here's code that will do it (read on if you want explanations for pieces of the code - that will be below):
# Connecting to NMDP - will use Credential Manager to pull in Admin Credentials
Connect-PnPOnline -Url https://[YourOrg].sharepoint.com -Credentials:SharePointAdmin
# Location of the CSV
$csv = Import-Csv -path "C:\path\to\your.csv"
# Set global variables - hardcoding the admin account. Will appear as "Company Admin" in the admin center
$AdminUser = '[YOU]@[yourorg].org'
# Hubbing this site will help us sort in the admin center - this is hardcoded
$version = 1
# This command loops the site creation for each row in the CSV
foreach ($row in $csv)
{
# Start a timer - more for testing than anything
$StartTime = $(get-date)
$version ++
# The CSV has the headers of Center and Num, so we set the site variables on those columns
$SiteName = $row."Center"
$SiteNum = $row."Num"
# Complete the rest of the variables
$SiteURL = "NRD-$SiteNum"
$group = "APP-SP-NRD-$SiteName-ViewOnly"
$fullURL = "https://[YourOrg].sharepoint.com/sites/$siteURL"
# Set a site design script that includes the AD group that was created for this specific site
$SiteDesignScript =@"
{
'$schema': 'schema.json',
'actions': [
{
'verb': 'removeNavLink',
'displayName': 'Home',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'displayName': 'Pages',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'displayName': 'Documents',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'displayName': 'Notebook',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'url': '/_layouts/15/viewlsts.aspx',
'displayName': 'Site contents',
'isWebRelative': true
},
{
'verb': 'createSPList',
'listName': 'Reports',
'templateType': 101
},
{
'verb': 'addPrincipalToSPGroup',
'principal': 'SharePoint Page Admin',
'group': 'Owners'
},
{
'verb': 'addPrincipalToSPGroup',
'principal': '$group',
'group': 'Visitors'
}
],
'bindata': { },
'version': '$version'
};
"@
# Add the script to our tennant
Add-PnPSiteScript -Title "For Loop" -Content $SiteDesignScript -Description "To be Removed"
# Pull that script ID - it should be the only one
$Script = Get-PnPSiteScript
$ScriptID = $Script.ID
# Set the script to our specific design called later in the script
Set-PnPSiteDesign -Identity 0265ec5f-7ba0-4807-8d14-e07803e21e4c -Title $SiteNum -SiteScriptIds $ScriptID
"Create"
# Creating the Team Site, 500MB in size
New-PnPTenantSite `
-Title "Network Partner $SiteName" `
-Url "https://[YourOrg].sharepoint.com/sites/$siteURL" `
-Owner "$AdminUser" `
-TimeZone 11 `
-Template "STS#3" `
-Lcid 1033 `
-RemoveDeletedSite `
-StorageQuota 500 `
-StorageQuotaWarningLevel 400 `
-Wait
# Pause for the site to assign proper security - without this the connect command fails
"Created"
Start-Sleep -s 20
# Connect to the newly created site
Connect-PnPOnline -Url https://[YourOrg].sharepoint.com/sites/$siteURL -Credentials:SharePointAdmin
# Add the hub association
Add-PnPHubSiteAssociation -Site https://[YourOrg].sharepoint.com/sites/$siteURL -HubSite https://[YourOrg].sharepoint.com/sites/[Hub-URL]
"Hubbed"
# Design creates Reports doc library, gives admin permissions to the AD group SharePoint page admin, removes nav links, and adds the center group
Invoke-PnPSiteDesign -Identity 0265ec5f-7ba0-4807-8d14-e07803e21e4c
# Allow the site design to settle in - this step is for stability
Start-Sleep -s 20
"Designed"
# Adds a single section - this is the workaround for not being able to use the Get-PnPClientSideComponent.
# When you add the page section, it tricks the page. The template doesn't recognize anything yet on the page
# so when we run this, SharePoint believes it's adding the section to an empty page and builds that, which
# removes all the predefined but not yet completely associated web parts.
Add-PnPClientSidePageSection -Page Home -SectionTemplate OneColumn -Order 1
"Sectioned"
Start-Sleep -s 20
# Get the GUID for the Reports document library
$list=Get-PnPList -Identity "Reports"
$listID=$list.ID
# Add the Document Library web part to the section we added earlier.
Add-PnPClientSideWebPart -Page Home -DefaultWebPartType "List" -Section 1 -Column 1 -WebPartProperties @{isDocumentLibrary="true";selectedListId="$listID"}
"Library Part"
Start-Sleep -s 20
# Remove the site design we created
Connect-PnPOnline -Url https://[YourOrg].sharepoint.com -Credentials:SharePointAdmin
$Scripts = Get-PnPSiteScript | foreach {Remove-PnPSiteScript -Identity $_ -Force}
# Get our "Lap" time for this loop
$elapsedTime = $(get-date) - $StartTime
$totalTime = "{0:HH:mm:ss}" -f ([datetime]$elapsedTime.Ticks)
"$totalTime"
}
Step 1 is pretty obvious in the code. The important thing here is that we use the account that when you set the variable for admin user, it's the same account, otherwise the management pieces of the script after the initial spin-up break.
$AdminUser = '[YOU]@[yourorg].org'
The versioning is probably optional since we remove the script at the end - I just like setting versions. I set a timer for troubleshooting and estimating how long the script would take. They are the first and last lines of the loop.
$StartTime = $(get-date) ...
$elapsedTime = $(get-date) - $StartTime
$totalTime = "{0:HH:mm:ss}" -f ([datetime]$elapsedTime.Ticks)
"$totalTime"
Again, totally optional and I actually removed it from my production run of the code. It was just nice to know when I had to launch this on Sunday for it to be done by the time I got to work on Monday.
Next I set my variables from the CSV
$SiteName = $row."Center"
$SiteNum = $row."Num"
$SiteURL = "NRD-$SiteNum"
$group = "APP-SP-NRD-$SiteName-ViewOnly"
$fullURL = "https://[YourOrg].sharepoint.com/sites/$siteURL"
The next variable, however, is my JSON site script. The variables I tossed in here were the version (which I was adding one two every loop) and my AD Group name. This script removes all left nav links, gives owner permissions to our Admin AD group, and adds a new doc library called Reports.
$SiteDesignScript =@"
{
'$schema': 'schema.json',
'actions': [
{
'verb': 'removeNavLink',
'displayName': 'Home',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'displayName': 'Pages',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'displayName': 'Documents',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'displayName': 'Notebook',
'isWebRelative': true
},
{
'verb': 'removeNavLink',
'url': '/_layouts/15/viewlsts.aspx',
'displayName': 'Site contents',
'isWebRelative': true
},
{
'verb': 'createSPList',
'listName': 'Reports',
'templateType': 101
},
{
'verb': 'addPrincipalToSPGroup',
'principal': 'SharePoint Page Admin',
'group': 'Owners'
},
{
'verb': 'addPrincipalToSPGroup',
'principal': '$group',
'group': 'Visitors'
}
],
'bindata': { },
'version': '$version'
};
"@
Just a heads up - I wrote this in Visual Studio code. It wouldn't close my '@ unless it was at the start of the line below the end of the JSON statement. YMMV on that one, but heads up that it was picky there.
After we get that set, we add the script to our site and set it as a design.
Add-PnPSiteScript -Title "For Loop" -Content $SiteDesignScript -Description "To be Removed"
$Script = Get-PnPSiteScript
$ScriptID = $Script.ID
Set-PnPSiteDesign -Identity 0265ec5f-7ba0-4807-8d14-e07803e21e4c -Title $SiteNum -SiteScriptIds $ScriptID
$ScriptID = $Script.ID
The identity of my script was set ahead of time. I recommend doing that by creating a simple design before starting your script. It's way easier to set that GUID rather than setting it on the fly.
From there it's a pretty basic site creation script to get the site actually up and running.
I had to use the 20 second pauses to give the site time to catch up with the script. Without these (and even a couple of times with these) things like the hub association would fail. Once that passes, I connect to our new site, hub the site, and apply the updated site design.
Connect-PnPOnline -Url https://[YourOrg].sharepoint.com/sites/$siteURL -Credentials:SharePointAdmin
Add-PnPHubSiteAssociation -Site https://[YourOrg].sharepoint.com/sites/$siteURL -HubSite https://[YourOrg].sharepoint.com/sites/[Hub-URL]
Invoke-PnPSiteDesign -Identity 0265ec5f-7ba0-4807-8d14-e07803e21e4c
Invoke-PnPSiteDesign -Identity 0265ec5f-7ba0-4807-8d14-e07803e21e4c
This now has taken care of permissions for the site. If that's all you want to do, congrats, you're almost done, all you need to do is remove your site design script to make room for when you generate it on the next loop. Make sure you connect back to the root site to manage your Site Scripts.
Connect-PnPOnline -Url https://[YourOrg].sharepoint.com -Credentials:SharePointAdmin
$Scripts = Get-PnPSiteScript | foreach {Remove-PnPSiteScript -Identity $_ -Force}
I have one extra section that adds the Reports web part to the page. In order to do this, I had to create a new section on the top and add the reports web part to that section. Note, this gets rid of all the other sections on the page.
$list=Get-PnPList -Identity "Reports"
$listID=$list.ID
Add-PnPClientSideWebPart -Page Home -DefaultWebPartType "List" -Section 1 -Column 1 -WebPartProperties @{isDocumentLibrary="true";selectedListId="$listID"}
And that's the script! Hopefully this helps some of you in your SharePoint and PowerShell quest!
Comments