Facing issues while using ImportExcel PowerShell module

Ashish_Arya 

The error message indicates that the maximum number of worksheets in an Excel workbook, 255, has been reached. One solution is creating multiple workbooks, each containing fewer than or equal to 255 worksheets. I modified your code so that it creates a new workbook when the number of worksheets reaches 255 and then continues adding worksheets to the next workbook; here's an example implementation:

param(

    [Parameter(Mandatory,

        HelpMessage = "Enter the pattern for filtering groups"

    )]

    $Pattern

)

# Excel file path 
$ExcelFilePath = "$($PSScriptroot)\GroupMembers"
$WorkbookNumber = 0
$WorksheetCount = 0

# Azure AD App details
$ApplicationId = $Env:Azure_CLIENT_ID
$TenantID = $Env:Azure_TENANT_ID
$ClientSecret = $Env:Azure_CLIENT_SECRET | ConvertTo-SecureString -AsPlainText -Force
$ClientSecretCredential = New-Object -TypeName 'System.Management.Automation.PSCredential' -ArgumentList $ApplicationId, $ClientSecret

# Connecting to Microsoft Graph
Connect-MgGraph -TenantId $TenantID -ClientSecretCredential $ClientSecretCredential | Out-Null

# Getting all the groups with displayname starting with the provided pattern
$Groups = Get-MgGroup -filter "startswith(displayname,'$Pattern')" -Top 2000

# Looping through all the filtered groups and exporting their members to the csv files
foreach ($Group in $Groups) {
    $WorksheetCount += 1

    if ($WorksheetCount -eq 256) {
        $WorkbookNumber += 1
        $WorksheetCount = 1
    }

    $WorkSheetName = "Group$($WorksheetCount)" 
    $ExcelFile = "$ExcelFilePath$($WorkbookNumber).xlsx"

    Try{
        (Get-MgGroupMember -GroupId $Group.id -Top 150).AdditionalProperties | `
            Select-Object @{n = "DisplayName"; e = { $_.displayName } }, @{n = "UserprincipalName"; e = { $_.userPrincipalName } } |`
            Export-Excel -path $ExcelFile -WorksheetName $WorkSheetName -Append -TableStyle 'Medium16' `
            -Title $Group.Displayname -TitleSize 14 -TitleBold 
    }

    Catch {
        Write-Host $_.Exception.Message -ForegroundColor 'Red'
        Break
    }
}