Find All Lists And Libraries Using InfoPath forms In SharePoint Online using PowerShell script via API

 This script can identify all the lists and form libraries that use InfoPath forms and it generate excel report with detailed information as well.

This script developed for get InfoPath form details from single site collection only, if you want get detail from multiple site collections or from tenant level, then you have modify the script to loop through all site collections.

Note: we are not fetching simply all form library using base template 115, along with we are checking whether form library associated with InfoPath forms or not, and same logic has been applied for generic list (base template 100) to identify associated InfoPath forms.

Note: Please check my previous post how to generate API secrets, then you will understand how PowerShell script is authenticating even though your tenant had multifactor authentication or without admin credentials.

#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
[System.Reflection.Assembly]::LoadWithPartialName("OfficeDevPnP.Core.dll")
clear-host


#Encrypted Graph API
$EClientID = "XXXXXXXXXXXXXXXXXXXXXXX"
$ETenantID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
$EThubprintID = "XXXXXXXXXXXXXXXx"


#Decrypted function
Function Decode ($guid){
$ID1 = [System.Text.Encoding]::ASCII.GetString([System.Convert]::FromBase64String($guid))
return $ID1
}


#Function to Get Form Libraries-Lists
Function Get-PnPFormLibraries-Lists
{
    [cmdletbinding()]
    param(
    [parameter(Mandatory = $true, ValueFromPipeline = $True)]$Web,
    [parameter(Mandatory = $true, ValueFromPipeline = $False)][String] $CSVPath
    )
   
    Try {
         
        #Get Current Context
        $pnpcontext = Get-PnPContext

        #Get All Lists with InfoPath Forms
        $i=0;
        foreach ($List in ($Lits = Get-PnPList -Web $Web))
        {
      
            #Please use ($List.BaseTemplate -eq 100) for checking only Generic lists
            if($List.BaseTemplate -eq 100)
            #if($List.BaseTemplate -ne 101)
            {
                $pnpcontext.Load($List.ContentTypes)
                $pnpcontext.ExecuteQuery()


                if ($List.ContentTypes[0].EditFormUrl.Contains("editifs.aspx"))
                {
                    #$LastItemCreated = ((Get-PnPListItem -List $List | select -Last 1).FieldValues.Created).tostring("m/d/yyyy h:mm")          
                    $List | Select Title, DefaultViewUrl, BaseType, BaseTemplate, Created, @{label='LastItemCreated';expression={((Get-PnPListItem -List $_.Title | select -Last 1).FieldValues.Created).tostring("M/d/yyyy h:mm")}} | Export-Csv -Path $CSVPath -NoTypeInformation -Append
                                   
                    $i++;
                }
            }

        }
   
        #Get All Form libraries
        $FormLibraries = Get-PnPList -Web $Web | Where-Object {$_.BaseTemplate -eq 115 -and $_.Hidden -eq $false -and $_.DocumentTemplateUrl.ToString().EndsWith("xsn") -eq $true }
 
        #Export Form Libraries Inventory to CSV
        If($FormLibraries.count -gt 0)
        {
            Write-host "Identified Web '$($Web.URL)'" -f Yellow
            Write-host "`tFound '$($FormLibraries.count)' Form Librarie(s)!" -f Green
            $FormLibraries | Select Title, DefaultViewUrl, BaseType, BaseTemplate, Created, @{label='LastItemCreated';expression={((Get-PnPListItem -List $_.Title | select -Last 1).FieldValues.Created).tostring("M/d/yyyy h:mm")}} | Export-Csv -Path $CSVPath -NoTypeInformation -Append
            
            if($i -ne 0) {
            Write-host "`tFound '$($i)' InfoPath List(s)!" -f Green }
        }else{

            if($i -ne 0) {
            Write-host "Identified Web '$($Web.URL)'" -f Yellow
            Write-host "`tFound '$($i)' InfoPath List(s)!" -f Green }

        }

        #Get All Subwebs
        $SubWebs = Get-PnPSubWebs -web $Web
        Foreach ($SubWeb in $SubWebs)
        {
            #Connect to PnP Online for subweb
            Connect-PnPOnline -Url $SubWeb.Url -ClientId $(Decode $EClientID) -Tenant $(Decode $ETenantID) -Thumbprint $(Decode $EThubprintID)
            
            #Call the function recursively
            Get-PnPFormLibraries-Lists -Web $SubWeb -CSVPath $CSVPath
        }
    }
    catch {
        write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
    }
}


#Set Variables
$Web = Read-Host -Prompt "Enter Site Collection URL" 
$CSVPath = "C:\InfoPathFormDetails.csv"


#Delete the Output Report if exists
If (Test-Path $CSVPath) { Remove-Item $CSVPath }


#Connect to PnP Online
Connect-PnPOnline -Url $Web -ClientId $(Decode $EClientID) -Tenant $(Decode $ETenantID) -Thumbprint $(Decode $EThubprintID)
$Web = Get-PnPWeb


#Call the function
Get-PnPFormLibraries-Lists -Web $Web -CSVPath $CSVPath

#Importnant: Delete the variables and its value from memory
Get-Variable | ForEach-Object { Remove-Variable -Name $_.Name -Force -ErrorAction SilentlyContinue -WarningAction SilentlyContinue}

Execute above script and enter site collection address then you may see below out put.

It also generate detailed information regarding InfoPath form library or list details along with last item created info which helps whether the library/list has been actively using or not using in tenant.

Thanks for visiting blog.