Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum Report Saved Search to excel, with custom attributes
    • Sign In

    • State Not Answered
    • Replies 0 replies
    • Subscribers 65 subscribers
    • Views 121 views
    • Users 0 members are here
    • powershell
    • ProjectWise
    • pwps_dab
    • ProjectWise Programming

    Report Saved Search to excel, with custom attributes

    Simon Biddle
    Offline Simon Biddle 5 months ago

    I would like to write a report to excel based on a saved search. I also need to write a custom attribute 'Revision No' to excel.

    In the if block the custom attribute is written to the terminal but it doesn't write to Excel. 

    $SavedSearch = 'PW test'
    $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
    $path = "C:\Users\user\Documents\00_Dev\powershell\pw\logs\weekly_report_$timestamp.xlsx"
    
    # Define the column headers you want to include in the Excel file
    $desiredColumnHeaders = @("Name", "Revision")
    
    $docs = Get-PWDocumentsBySearch -searchName $SavedSearch
    $docs = $docs | Select-Object -Property Name, @{Name="Revision";Expression={$_.CustomAttributes.Revision_Nos}}
    
    
    # Check if any documents are returned
    if ($docs.Count -gt 0) {
        # Get the property names of the first document and display them
        $propertyNames = $docs[0] | Get-Member -MemberType Properties | Select-Object -Property Name, @{Name="Revision";Expression={$_.CustomAttributes.Revision_Nos}}
        Write-Output "Available properties:"
        $propertyNames
    } else {
        Write-Output "No documents found in the saved search."
    }
    
    # Filter the properties you want to keep for each document
    $filteredDocuments = $docs | Select-Object -Property $desiredColumnHeaders, @{Name="Revision";Expression={$_.CustomAttributes.Revision_Nos}}
    
    # Export the filtered documents to an Excel file
    $filteredDocuments | Export-Excel -Path $path -WorksheetName "Documents" -AutoSize

    Any help is appreciated

    • Sign in to reply
    • Cancel

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies