Use PowerShell to generate Angular HTML report to show SP group members

In one of SharePoint 2016 farm I maintain, there is a custom solution to regular create new SP groups, update group members and remove un-used groups. With such large change everyday I need a daily report to list the group members with filtering.

I have 2 ideas in mind (1) create a SQL Server Reporting Service (SSRS) report; (2) generate a HTML page with PowerShell and upload to specific library. Finally I choose option (2) because SSRS report is too limited and old school. Let’s try something new this time.

We will need these scripts:
a) Loop thru entire site collection and export SP groups names and members in a HTML table
b) Add Angular related JS & CSS at the header of HTML
c) Upload the HTML file to target SharePoint library

Below are part of the PowerShell script for a,b & c. I do not paste all script because it contains some sensitive information. So you will need some PowerShell experience to make it work. Please let me know if you need assistant.

a. Loop thru entire site collection and export SP groups names and members in a HTML table

$Sites = Get-SPSite “https://sharepoint.com/sites/portal"
$html = “”
ForEach ($Site in $Sites) {
$web = Get-SPWeb $Site.URL
$sitePreContent = “<h2>” + $web.Title + “ (URL: “ + $web.Url + “)</h2><p>Last Modified: “ + (Get-Date -Format ‘d MMM yyyy’) + “</p></br>”
$sitePostContent = “ — — — — — Report End — — — — — — “
$groups = $web.SiteGroups | Select-Object Name, LoginName, Owner, ID, @{Name=”UserMember”;Expression={“”}}, Users
ForEach ($group in $groups) {
$uMember = “”
ForEach ($u in $group.Users) {
if ($uMember -eq “”) {
$uMember = $uMember + $u.DisplayName
} else {
$uMember = $uMember + “; “ + $u.DisplayName
}
}
$group.UserMember = $uMember
}
#@{N=’Group Owner’; E={$_.Owner}}
$tmpHtml = $groups | Sort-Object -Property ID | Select-Object ID, @{N=’SP Group Name’; E={$_.Name}}, LoginName, UserMember | ConvertTo-Html -PreContent $sitePreContent -PostContent $sitePostContent | Out-String
$html = $html + $tmpHtml
}
$result = ConvertTo-AngularHTML -PreContent $preContent -HTMLFragments $html | Out-File -FilePath $FilePath

b. Add Angular related JS & CSS at the header of HTML

function ConvertTo-AngularHTML {
<#
ref: https://ben.neise.co.uk/2012/08/22/formatting-powershell-tables-with-jquery-and-datatables.html,
https://devops-collective-inc.gitbook.io/creating-html-reports-in-powershell/building-the-html
#>
[CmdletBinding()]
[OutputType([string])]
param(
[string[]]$JavascriptUri = @(‘https://code.jquery.com/jquery-1.10.2.min.js', ‘https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js'),
[string[]]$CssStyleSheet,
[string[]]$CssUri = @(‘https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css'),
[string]$Title = ‘Report’,
[string]$PreContent,
[string]$PostContent,
[Parameter(Mandatory=$True)][string[]]$HTMLFragments
)

$stylesheet = “”
if ($CssUri) {
foreach ($uri in $CssUri) {
$stylesheet += “<link rel=`”stylesheet`” href=`”$uri`” type=`”text/css`” />”
}
}
if ($CssStyleSheet) {
$stylesheet += “<style>”
foreach ($css in $CssStyleSheet) {
$stylesheet += “$css`r`n”
}
$stylesheet += “</style>”
}

$titletag = “”
if ($PSBoundParameters.ContainsKey(‘title’)) {
$titletag = “<title>$title</title>”
}

if ($JavascriptUri) {
foreach ($uri in $JavascriptUri) {
$script += “<script type=`”text/javascript`” src=`”$uri`”></script>`n”
}
}

$body = $HTMLFragments | Out-String

if ($PSBoundParameters.ContainsKey(‘precontent’)) {
$body = “$PreContent`n$body”
}
if ($PSBoundParameters.ContainsKey(‘postcontent’)) {
$body = “$body`n$PostContent”
}

$datatable = “”
$datatable = “<script type=`”text/javascript`”>”
$datatable += ‘$(document).ready(function () {‘
$datatable += ‘$(“table:last-child”).remove();’
$datatable += ‘$(“table”).addClass(“hover compact stripe cell-border”).each(function(){‘
$datatable += ‘var tHead = $(this).find(“tr:first”).html();’
$datatable += ‘$(this).find(“colgroup”).remove();’
$datatable += ‘$(this).find(“tr:first”).remove();’
$datatable += ‘$(this).find(“tbody”).before(“<thead>” + tHead + “</thead>”); });’
$datatable += ‘$(“table”).dataTable( {‘
$datatable += ‘paging: false,’
$datatable += ‘“order”: [[ 1, “asc” ]]’
$datatable += “} );”
$datatable += ‘} );’
$datatable += “</script>”

ConvertTo-HTML -Head “$stylesheet`n$titletag`n$script`n$datatable” -Body $body
}

c. Upload the HTML file to target SharePoint library

$ToSite = Get-SPWeb $TargetSiteURL
$ToFolderPath = “/Report”

$r = UploadDoc -SourceFile “report.aspx” -TargetSite $ToSite -TargetFolderPath $ToFolderPath
$TargetLibrary = $TargetSite.GetFolder($TargetFolderPath)
$Stream = $file.OpenRead()
$TargetLibrary.Files.Add($TargetFolderPath + “/” + $SourceFile, $Stream, $true)
$Stream.Close()