Functions/Public/New-CT365DataEnvironment.ps1
|
<# .SYNOPSIS Creates a new Excel workbook with predefined worksheets for Office 365 data. .DESCRIPTION The New-CT365DataEnvironment function creates a new Excel workbook with predefined worksheets. The workbook contains a "Users" worksheet and a "Groups" worksheet, and additional worksheets based on the provided JobRole parameter. .PARAMETER WorkbookName The name of the workbook file to be created. It must be a .xlsx file. .PARAMETER FilePath The path where the workbook will be created. The provided path must exist. .PARAMETER JobRole An array of strings specifying additional worksheets to be created in the workbook. Each string will be used as the name of a new worksheet. .EXAMPLE New-CT365DataEnvironment -WorkbookName "myworkbook.xlsx" -FilePath "C:\temp" -JobRole "Manager","Employee" This command creates an Excel workbook named "myworkbook.xlsx" in the "C:\temp" directory. The workbook will contain the "Users" and "Groups" worksheets, as well as a "Manager" and "Employee" worksheet. .EXAMPLE New-CT365DataEnvironment -WorkbookName "365DataEnvironment.xlsx" -FilePath "C:\365DevEnvironment" -JobRole "NY-ITManager","CA-AccountsPayable","FL-HumanResources" This command creates an Excel workbook named "365DataEnvironment.xlsx" in the "C:\365DevEnvironment" directory. The workbook will contain the "Users" and "Groups" worksheets, as well as a "NY-ITManager", "CA-AccountsPayable", and "FL-HumanResources" worksheet. .INPUTS System.String .OUTPUTS None. This cmdlet does not return any output. .NOTES The JobRole Parameter can be a single job role or a location and job role. If you are a company with only one location, you do not need to put a location in. #> function New-CT365DataEnvironment { [CmdletBinding()] param ( [Parameter(Mandatory)] [ValidatePattern('^.*\.(xlsx)$')] [string]$WorkbookName, [Parameter(Mandatory)] [ValidateScript({ if (Test-Path -Path $_ -PathType Container) { $true } else { throw "Folder path $_ does not exist, please confirm path does exist" } })] [string]$FilePath, [Parameter(Mandatory)] [string[]]$JobRole ) begin { # Import Required Modules $ModulesToImport = "ImportExcel","PSFramework" Import-Module $ModulesToImport $Path = Join-Path -Path $filepath -ChildPath $workbookname Write-PSFMessage -Level Output -Message "Creating workbook $WorkbookName" -Target $WorkbookName #helper function function New-EmptyCustomObject { param ( [string[]]$PropertyNames ) $customObject = [PSCustomObject]@{} $customObject | Select-Object -Property $PropertyNames } } process { # Define properties for custom objects $propertyDefinitions = @{ Users = @( "FirstName", "LastName", "UserName", "Title", "Department", "StreetAddress", "City", "State", "PostalCode", "Country", "PhoneNumber", "MobilePhone", "UsageLocation", "License" ) Groups = @( "DisplayName", "PrimarySMTP", "Description", "Owner", "Type" ) JobRole = @( "DisplayName", "PrimarySMTP", "Description", "Type" ) } # Define custom objects for each worksheet $usersObject = New-EmptyCustomObject -PropertyNames $propertyDefinitions.Users $groupsObject = New-EmptyCustomObject -PropertyNames $propertyDefinitions.Groups # Export each worksheet to the workbook $usersObject | Export-Excel -Path $Path -WorksheetName "Users" -ClearSheet $groupsObject | Export-Excel -Path $Path -WorksheetName "Groups" -Append foreach($JobRoleItem in $JobRole){ $RoleObject = New-EmptyCustomObject -PropertyNames $propertyDefinitions.JobRole $RoleObject | Export-Excel -Path $Path -WorksheetName $JobRoleItem -Append } } end { Write-PSFMessage -Level Output -Message "Workbook $WorkbookName created successfully" -Target $WorkbookName } } |