Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Teams Private Channels with Azure Automation Report
<# ----------------------------------------------------------------------------
Produces a report in Export Excel and Saves to SharePoint
Created: Paul Bullock
Date: 18/01/2021
Disclaimer:
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
This script uses the following samples as a reference:
Connect to the SharePoint Online using Application Permissions -
https://github.com/pnp/powershell/tree/dev/samples/Connect.AzureAutomation
With thanks to for the great modules:
Doug Finke for Import Excel Module - https://github.com/dfinke/ImportExcel
Erwin Van Hunen for PnP PowerShell - https://github.com/pnp/powershell
---------------------------------------------------------------------------- #>
[CmdletBinding()]
Param
(
[string]$DefaultSite = "SS-Demo"
)
# Retrieves from the Azure Automation variables and certificate stores
# the details for connecting to SharePoint Online
$azureAutomateCreds = Get-AutomationPSCredential -Name 'AzureAppCertPassword'
$appId = Get-AutomationVariable -Name "AppClientId"
$appAdTenant = Get-AutomationVariable -Name "AppAdTenant"
$app365Tenant = Get-AutomationVariable -Name "App365Tenant"
$appCert = Get-AutomationCertificate -Name "AzureAppCertificate"
# Addresses for the tenant
$adminUrl = "https://$app365Tenant-admin.sharepoint.com"
$baseSite = "https://$app365Tenant.sharepoint.com/sites/$DefaultSite"
try {
Write-Verbose "Running Script..."
#-------------------------------------------------
# Connections to SharePoint
#-------------------------------------------------
# Export the certificate and convert into base 64 string
$base64Cert = [System.Convert]::ToBase64String($appCert.Export([System.Security.Cryptography.X509Certificates.X509ContentType]::Pkcs12, $azureAutomateCreds.Password))
# Connect to the standard SharePoint Site
$siteConn = Connect-PnPOnline -ClientId $appId -CertificateBase64Encoded $base64Cert `
-CertificatePassword $azureAutomateCreds.Password `
-Url $baseSite -Tenant $appAdTenant -ReturnConnection
# Connect to the SharePoint Online Admin Service
$adminSiteConn = Connect-PnPOnline -ClientId $appId -CertificateBase64Encoded $base64Cert `
-CertificatePassword $azureAutomateCreds.Password `
-Url $adminUrl -Tenant $appAdTenant -ReturnConnection
# SharePointy Adminy Stuff here
Write-Verbose "Connected to SharePoint Online Admin Centre"
#-------------------------------------------------
# Gather Reporting Data
#-------------------------------------------------
# Gets all Team Private Channels based on the template
$teamPrivateChannels = Get-PnPTenantSite -Template "TEAMCHANNEL#0" -Connection $adminSiteConn
#-------------------------------------------------
# Produce and Save Reporting Data
#-------------------------------------------------
$now = [System.DateTime]::Now.ToString("yyyy-mm-dd_hh-MM-ss")
$reportFileName = "teams-private-channels-$($now).xlsx"
$ExcelReportSettings = @{
Path = $reportFileName
Title = "Teams Private Channel Report"
WorksheetName = "Teams Private Channels"
AutoFilter = $true
AutoSize = $true
}
Write-Verbose "Creating Excel File $reportFileName"
$teamPrivateChannels | Select-Object Title,Url,StorageUsage,Owner,SiteDefinedSharingCapability `
| Export-Excel @ExcelReportSettings
# Save to SharePoint
$file = Add-PnPFile -Path $reportFileName -Folder "Shared Documents" -Connection $siteConn
Write-Verbose "Uploaded Excel File $reportFileName to SharePoint"
}
catch {
# Script error
Write-Error "An error occurred: $($PSItem.ToString())"
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment