Table of Contents
Create SharePoint List from Data Extracted from Teams
The article discussing a PowerShell script to generate a Teams directory explains how to create output files in different formats that can be used to make the directory available to users. For instance, you could post a HTML format version of the directory in a SharePoint Online site. Discussion about the post generated some nice ideas, amongst which was the suggestion to output the directory as a SharePoint list (aka Microsoft Lists).
I haven’t done much to manage SharePoint lists with PowerShell, so this seemed like a nice opportunity to explore the idea and increase my knowledge.
Choosing the Right Module to Create SharePoint List
The first order of business is to choose a PowerShell module for the task. I started off with the Microsoft Graph PowerShell SDK, which includes cmdlets like New-MgSiteList and Get-MgSiteList. Unhappily, I ran into several problems with SDK cmdlets (V2.8) that I’ve reported to Microsoft. The documentation and examples for these SDK site cmdlets are not as good as other areas covered by the SDK, so the problems could be due to misunderstanding on my part.
This brought me to the Pnp.PowerShell module (aka “Microsoft 365 Patterns and Practices PowerShell Cmdlets”). PnP is a community effort to create resources that help people to build app on the Microsoft 365 platform. The big advantage of PnP is that its cmdlets can interact with SharePoint Online content like list items where the Microsoft SharePoint management module is limited to tenant and site settings.
Basic Steps in the Script to Add Teams Directory Records and Create SharePoint List
The basic steps in the script are:
- Connect to the site that stores the list. I created a communications site for this purpose.
- Look for the list and if found, remove it because it’s easier to create and populate a new list instead of attempting to synchronize changes since the last update for the team directory.
- Create the list and the columns used to store team directory information. Many templates are available for Lists. I used the Links template and removed one of the two default columns.
- Populate the list with new items. To do this, the script reads the information in from the CSV file created by the original script and writes them as new list items.
PnP.PowerShell Cmdlets Used to Create SharePoint List
Translating the above into PnP PowerShell, the script uses the following cmdlets:
- Connect-PnpOnline to connect to the target site. PnP supports different forms of authentication. For the purpose of this demonstration, the script prompts for credentials of a site administrator and uses those to connect.
- Get-PnPList to check if the target list already exists and Remove-PnPList to remove the list if found.
- New-PnPList to create the target list.
- Add-PnPField to define the set of fields used to store directory information.
- Remove-PnPField to remove the standard Notes field inherited from the Links template. Here’s how the script creates the list and the fields used to store Teams directory information:
New-PnpList -Title $ListName -Template Links -EnableVersioning -Connection $Connection | Out-Null # Add fields Add-PnpField -List $ListName -DisplayName 'Team Name' -Internalname TeamName -Type Text -AddToDefaultView | Out-Null Add-PnpField -List $ListName -DisplayName 'Description' -Internalname Description -Type Text -AddToDefaultView | Out-Null Add-PnpField -List $ListName -DisplayName 'Owner' -Internalname Owner -Type Text -AddToDefaultView | Out-Null Add-PnpField -List $ListName -DisplayName 'Owner SMTP Address' -Internalname OwnerSMTP -Type Text -AddToDefaultView | Out-Null Add-PnpField -List $ListName -DisplayName 'Member count' -Internalname MemberCount -Type Number -AddToDefaultView | Out-Null Add-PnpField -List $ListName -DisplayName 'External count' -Internalname ExternalCount -Type Number -AddToDefaultView | Out-Null Add-PnpField -List $ListName -DisplayName 'Access' -Internalname AccessMode -Type Text -AddToDefaultView | Out-Null # Remove the Notes field inherited from the Links template Remove-PnPField -List $ListName -Identity Notes -Force
- Add-PnPListItem to populate the list with items imported from the CSV file. Here’s how the script populates the list:
[array]$TeamsData = Import-CSV -Path $CSVFile
[int]$i = 0
ForEach ($Team in $TeamsData) {
$i++
Write-Host ("Adding record for team {0} {1}/{2}" -f $Team.Team, $i, $TeamsData.count)
Add-PnPListItem -List $ListName -Values @{
"URL" = $($Team.Deeplink);
"TeamName" = $($Team.Team);
"Description" = $($Team.Description);
"Owner" = $($Team.Owner);
"OwnerSMTP" = $($Team.OwnerSMTP);
"MemberCount" = $($Team.Members);
"ExternalCount" = $($Team.ExternalGuests);
"AccessMode" = $($Team.Access);
} | Out-Null
}
The original version of the Teams Directory script generates a directory record for each team including a clickable deeplink to allow users to open Teams in the selected team. They can then join the team (public teams) or request the team owner to join (private teams). The deeplink generated by the script is formatted to make it clickable when exported to a HTML report. I updated the script to include a simple deeplink because SharePoint list entries don’t need the formatting.
Figure 1 shows the Teams directory records in a SharePoint Online list. I’m sure that the visual appearance of the list could be improved by tweaking the columns, but what’s here is sufficient to demonstrate the principles behind creating and populating a list.
You can download a copy of the full script from GitHub.
Lots to Explore in Lists
The SharePoint community understands and takes full advantage of lists (here’s an example). Others in the Microsoft 365 world might not. Perhaps this example of extracting information from one area of Microsoft to create a SharePoint list and populate the list with Teams directory information might get your creative juices flowing.
Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.