|
<# —————————————————————————- |
|
|
|
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())" |
|
} |
