Converting between OneNote Tables and PowerShell PSObject

OneNote.ps1

function Get-1NotePage {
    <#
    .SYNOPSIS
        Finds the ID of a OneNote page
    .DESCRIPTION
       http://thebackend.info/powershell/2017/12/onenote-read-and-write-content-with-powershell/
    .EXAMPLE
       Get-1NotePage -Name "PowerShell Test"
    #>
    [cmdletbinding()]
    [Alias('Get-OneNotePage')]
    param (
        [string]$PageName,
        [string]$SectionName
    )

    $OneNote = New-Object -ComObject OneNote.Application
    [xml]$Hierarchy = ""
    $OneNote.GetHierarchy("", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$Hierarchy)

    if($SectionName) {
        # Get the Section ID
        $Section = $Hierarchy.notebooks.notebook.section | ?{$_.Name -eq "$SectionName"}
        # Get the Section Hierarchy
        [xml]$SectionHierarchy = ""
        $OneNote.GetHierarchy("$($Section.ID)", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$SectionHierarchy)
        $Pages = $SectionHierarchy.section.page
    } else {
        $Pages = $Hierarchy.notebooks.notebook.section.page
    }
    Write-Output (($Pages | ?{$_.Name -eq "$PageName"}) | Select Name,ID)
}

function Get-1NoteContent {
    <#
    .SYNOPSIS
       Reads OneNote XML content into an array of PowerShell objects
       Supports OneNote Strings and OneNote Tables
    .DESCRIPTION
       Ideas: http://thebackend.info/powershell/2017/12/onenote-read-and-write-content-with-powershell/
    .EXAMPLE
       Get-1NoteContent -PageName "PowerShell Test"
       Returns an array of PowerShell objects
    .EXAMPLE
       Get-1NoteContent -PageName "Service Status" -OutDefault
       Uses Out-Default to format output instead of Write-Output
       Useful for viewing results in console but unable to save output to variable
    #>
    [cmdletbinding()]
    [Alias('Out-OneNoteContent')]
    param (
        [string]$PageName,
        [string]$SectionName,
        [parameter(ValueFromPipelineByPropertyName,ValueFromPipeline)][string]$PageID,
        [switch]$OutDefault
    )

    begin {
        $OneNote = New-Object -ComObject OneNote.Application
        [xml]$Hierarchy = ""
        $OneNote.GetHierarchy("", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$Hierarchy)
        
    }
    
    process {
        $results = @()
        # Get Page ID
        if($PageName) {
            $Pages = Get-1NotePage -PageName $PageName -SectionName $SectionName
        } elseif($PageID) {
            $Pages = [pscustomobject][ordered]@{
                ID = $PageID
            }
        }

    
        foreach($PageID in $Pages.ID) {
            # Get the XML Page Content
            [xml]$PageContent = ""
            $OneNote.GetPageContent($PageID,[ref]$PageContent)
            # Each Note is an OE object
            foreach($OE in $PageContent.Page.Outline.OEChildren.OE) {
                # Get Note Data
                if($OE.T."#cdata-section") {
                    $results += [pscustomobject][ordered]@{
                        Page = $PageContent.Page.Name
                        Data = $OE.T."#cdata-section"
                    }
                    # Add Child OE - Bullets and Numbering
                    $listResults = $()
                    foreach($child in $OE.OEChildren.OE) {
                        $listPrefix = $null
                        if($child.List.Bullet) {
                            $listPrefix = "-"
                        } elseif($child.List.Number) {
                            $listPrefix = $child.List.Number.Text
                        }
                        # Add List data with newline
                        $listResults += "$listPrefix$($child.T."#cdata-section")`n"
                    }
                    # Add list results
                    if($listResults.count -gt 0) {
                        $results += [pscustomobject][ordered]@{
                            Page = $PageContent.Page.Name
                            Data = $listResults
                        }
                    }
                }
                # Process Note Table
                if($OE.Table -ne $null) {
                    $columns = @($OE.Table.Columns.Column)
                    # Headers are in the first row
                    $headers = @($OE.Table.Row[0].Cell.OEChildren.OE.T."#cdata-section")
                    $tableResults = @()
                    # Row
                    foreach($row in $OE.Table.Row | Select-Object -Skip 1) {
                        $rowObject = New-Object PSObject
                        # Column
                        for($i = 0; $i -lt $columns.count; $i++) {
                            $cell = $row.Cell[$i]
                            # Get columnn header. Remove HTML tags
                            $header = $headers[$i] -replace '<[^>]+>',''
                            # Get cell value. Remove HTML tags
                            $value = $cell.OEChildren.OE.T."#cdata-section" -replace '<[^>]+>',''
                            if(!$header) {
                                $header = "Column$i"
                            }
                            # Add cell value to rowObject
                            $rowObject | Add-Member -MemberType NoteProperty -Name $header -Value $value
                        }
                        $tableResults += $rowObject
                    }
                    $results += [pscustomobject][ordered]@{
                        Page = $PageContent.Page.Name
                        Data = $tableResults
                    }
                }
            }
        }


        if($OutDefault) {
            foreach($item in $results) {
                Write-Output $item.Data | Out-Default
            }
        } else {
            Write-Output $results
        }
    }
}

function Out-1Note {
    <#
    .SYNOPSIS
       Send a Note to a OneNote Page
       Note can be a string or an array of PSObjects
    .DESCRIPTION
       This functions uses COM-object to interact with OneNote. It requires OneNote 2013 and .NET 4.0 to function correctly
       By default new Notes are created in the 'Unfiled Notes' section
       PowerShell PSObjects are converted to OneNote XML Tables
       Original: http://asaconsultant.blogspot.com/2015/03/getting-things-done.html
    .EXAMPLE
       Out-1Note -Note "Remember to call Frank on 12/12 by lunch"
       Creates a new page in OneNote in the Unfiled Notes section. The PageName is the Note
    .EXAMPLE
       Out-1Note -Note "Remember to call Frank on 12/12 by lunch" -PageName "Don't forget"
       Creates a new page in OneNote in the Unfiled Notes section named "Dont' forget"
    .EXAMPLE
       Out-1Note -Note @(Get-Service | Select Name,Status) -PageName "PowerShell Test" -SectionName "Testing" 
       Creates a new page in OneNote in the Testing section with a table of the Get-Service array
    .EXAMPLE
       Out-1Note -Note "I like turtles" -PageName "PowerShell Test" -SectionName "Testing" -Append
       Appends a Page with a Note. If multiple pages are found, the Note is appended to the LastModified page
    #>
    [cmdletbinding()]
    [Alias('Out-OneNote')]
    Param(
        [Parameter(Mandatory=$true)]$Note,
        [string]$PageName,
        [string]$SectionName = "Unfiled Notes",
        [switch]$Append
    )
    # http://stackoverflow.com/questions/8186819/creating-new-one-note-2010-page-from-c-sharp
    $f = $MyInvocation.InvocationName
    Write-Verbose -Message "$f - START"

    $onenote = New-Object -ComObject OneNote.Application -ErrorAction SilentlyContinue
    if (!$onenote) { 
        throw "Error - Unable to create OneNoe application object (COMobject error)"
    }

    $scope = [Microsoft.Office.Interop.OneNote.HierarchyScope]::hsPages
    [ref]$xml = ""
    $onenote.GetHierarchy($null, $scope, $xml)
    $schema = @{one="http://schemas.microsoft.com/office/onenote/2013/onenote"}
    
    Write-Verbose -Message "$f -  Finding section in OneNote"
    $xpath = "//one:Notebook/one:Section"
    Select-Xml -Xml ([xml]$xml.Value) -Namespace $schema -XPath $xpath | foreach {
        if ($psitem.Node.Name -eq $SectionName) { 
            $SectionID = $psitem.Node.ID
            $SectionPath = $psitem.Node.Path
            $SectionPages = $psitem.Node.Page
        }
    }

    if (!$SectionID) { 
        throw "Unable to find Section with name $SectionName"
    }

    [ref]$PageXML = ""
    if(!$Append) {
        Write-Verbose -Message "$f -  Creating new page"
        [ref]$newpageID =""
        $onenote.CreateNewPage("$SectionID",[ref]$newpageID,[Microsoft.Office.Interop.OneNote.NewPageStyle]::npsBlankPageWithTitle)

        if (!$newpageID.Value) { 
            throw "Unable to create new OneNote page"
        }

        Write-Verbose -Message "$f -  Getting page content"
        $onenote.GetPageContent($newpageID.Value,[ref]$PageXML,[Microsoft.Office.Interop.OneNote.PageInfo]::piAll)

        if (!$PageXML) { 
            throw "Unable to get OneNote page content"
        }

        Write-Verbose -Message "$f -  Parsing new page xml content to find the title element"
        [Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | Out-Null
        $xDoc = [System.Xml.Linq.XDocument]::Parse($PageXML.Value)

        $title = $xDoc.Descendants() | where Name -like "*}T"
        if (!$title) { 
            throw "Unable to get title of new onenote page"
        }

        if(!$PageName) {
            $title.Value = "$Note"
        } elseif($PageName) {
            $title.Value = "$PageName"
        }

        Write-Verbose -Message "$f -  Setting page title to $($title.Value)"
        $onenote.UpdatePageContent($xDoc.ToString())
    } else {
        # Find the page in the section
        $Page = @($SectionPages | ?{$_.Name -eq $PageName} | Sort-Object LastModifiedTime | Select-Object -Last 1)

        if($Page.Count -ne 1) {
            Write-Output "Section:`"$SectionName`" with Page:`"$PageName`" not found. Abort"
            return
        }
        
        # Get the page content
        $onenote.GetPageContent($Page.ID,[ref]$PageXML,[Microsoft.Office.Interop.OneNote.PageInfo]::piAll)
    }

    if($Note -is [array]) {
        if(!$Append) {
            # Start Page
            $NewContent = @"
                <one:Page xmlns:one="http://schemas.microsoft.com/office/onenote/2013/onenote" ID="$($newpageID.Value)" >
                <one:Outline>
                    <one:Position x="36.0" y="86.4000015258789" z="0" />
                    <one:Size width="117.001953125" height="40.28314971923828" />
                    <one:OEChildren> 
                        <one:OE>
                            <one:Table bordersVisible="true">
                                <one:Columns>
"@
        } else {
            # Append Page
            $NewContent = @"
                <one:OE>
                    <one:Table bordersVisible="true">
                        <one:Columns>
"@
            
        }
        # Columns
        $headers = @($Note[0].PSObject.Properties | Select-Object -ExpandProperty Name)
        for($i = 0; $i -lt $headers.count; $i++) {
            $width = 100/$headers.count
            $NewContent += "<one:Column index='$i' width='$width' />"
        }
        $NewContent += "</one:Columns>"

        # Column Headers
        $NewContent += "<one:Row>"
        for($i = 0; $i -lt $headers.count; $i++) {
            $NewContent += "<one:Cell><one:OEChildren><one:OE><one:T><![CDATA[$($headers[$i])]]></one:T></one:OE></one:OEChildren></one:Cell>"
        }
        $NewContent += "</one:Row>"

        # Rows
        foreach($item in $note) {
            $NewContent += "<one:Row>"
            for($i = 0; $i -lt $headers.count; $i++) {
                $NewContent += "<one:Cell><one:OEChildren><one:OE><one:T><![CDATA[$($item.($headers[$i]))]]></one:T></one:OE></one:OEChildren></one:Cell>"
            }
            $NewContent += "</one:Row>"
        }

        if(!$Append) {
            # End Page
            $NewContent += "</one:Table></one:OE></one:OEChildren></one:Outline></one:Page>"
        } else {
            # End Table
            $NewContent += "</one:Table></one:OE>"   
        }
    } else {
        # New page with Note
        if(!$Append) {
            $NewContent = @"
            <one:Page xmlns:one="http://schemas.microsoft.com/office/onenote/2013/onenote" ID="$($newpageID.Value)" >
                    <one:Outline>
                        <one:Position x="36.0" y="86.4000015258789" z="0" />
                        <one:Size width="117.001953125" height="40.28314971923828" />
                        <one:OEChildren> 
                            <one:OE>
                                <one:T><![CDATA[$Note]]></one:T>
                            </one:OE>
                        </one:OEChildren>                
                    </one:Outline>
            </one:Page>
"@
        } else {
            #New OE with Note
            $NewContent = @"
                <one:OE>
                    <one:T><![CDATA[$Note]]></one:T>
                </one:OE>
"@
        }
    }

    if(!$Append) {
        Write-Host "$f -  Inserting Note at $sectionPath"
        $onenote.UpdatePageContent($NewContent)
    } else {
        Write-Host "$f -  Appending Note at $sectionPath"
        $lastOEChildrenIndex = $PageXML.Value.LastIndexOf("</one:OEChildren>")
        $updatedPageXML = $PageXML.Value.Insert($lastOEChildrenIndex,$NewContent)
        $onenote.UpdatePageContent($updatedPageXML)
    }
}

function Search-1Note {
    <#
    .SYNOPSIS
       Searches OneNote for a term
    .DESCRIPTION
       This functions uses COM-object to interact with OneNote. It requires OneNote 2013 and .NET 4.0 to function correctly
       Using -Categories will show Search categories similar to searching in the OneNote application
       Output can be piped to Get-1NoteContent
    .EXAMPLE
        Search-1Note "Remember to call"
    .EXAMPLE
        Search-1Note "Remember to call" | Get-1NoteContent
    #>
    [cmdletbinding()]
    [Alias('Search-OneNote')]
    param (
        [string]$Search = "PowerShell",
        [switch]$Categories
    )

    $OneNote = New-Object -ComObject OneNote.Application
    [xml]$Hierarchy = ""
    $OneNote.GetHierarchy("", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$Hierarchy)

    # Search OneNote
    [xml]$searchHierarchy = ""
    $OneNote.FindPages("","$Search",[ref]$searchHierarchy)

    $notebooks = @()
    if($searchHierarchy.Notebooks.UnfiledNotes) {
        $notebooks += $searchHierarchy.Notebooks.UnfiledNotes
    }

    if($searchHierarchy.Notebooks.Notebook) {
        $notebooks += $searchHierarchy.Notebooks.Notebook
    }
    
    $results = @()
    foreach($notebook in $notebooks) {
        foreach($section in $notebook.section) {
            foreach($page in $section.page) {
                $PagePath = "$($notebook.name) > $($section.Name)"
                $results += [pscustomobject][ordered]@{
                    PageName = $page.name
                    Path = $PagePath
                    LastModifiedTime = $page.lastModifiedTime
                    PageID = $page.ID
                }
            }
        }
        foreach($sectionGroup in $notebook.SectionGroup) {
            foreach($section in $sectionGroup.Section) {
                foreach($page in $section.page) {
                    $PagePath = "$($notebook.name) > $($sectionGroup.Name) > $($section.Name)"
                    $results += [pscustomobject][ordered]@{
                        PageName = $page.name
                        Path = $PagePath
                        LastModifiedTime = $page.lastModifiedTime
                        PageID = $page.ID
                    }
                }
            }
        }
    }

    $results = $results | Sort-Object -Property LastModifiedTime -Descending
    if($Categories) {
        # Filter the results to In Title, On Page, and Recycle Bin
        # These categories are the similar to the OneNote application search results
        $recycleResults = $results | ?{$_.Path -like "*OneNote_RecycleBin*"}
        $notRecycleResults = $results | ?{$_.Path -notlike "*OneNote_RecycleBin*"}
        # In Title Results are not in a Recycle Bin
        $inTitleResults = $notRecycleResults | ?{$_.PageName -like "*$Search*"}
        $notInTitleResults = $notRecycleResults | ?{$_.PageName -notlike "*$Search*"}
        # On Page Results are not in Title or in Recycle Bin
        $onPageResults = $notInTitleResults | ?{$_.Path -notlike "*OneNote_RecycleBin*"}

        Write-Output "In Title: `"$Search`" $($inTitleResults.count)"
        Write-Output $inTitleResults | Select-Object PageName,Path | Out-Default

        Write-Output "On Page: `"$Search`" $($onPageResults.count)"
        Write-Output $onPageResults | Select-Object PageName,Path | Out-Default

        Write-Output "Recycle Bins: `"$Search`" $($recycleResults.count)"
        Write-Output $recycleResults | Select-Object PageName,Path | Out-Default
    } else {
        Write-Output $results
    }
}

Out-1Note -Note "Remember to call Frank on 12/12 by lunch"
Out-1Note -Note "Remember to call Frank on 12/12 by lunch" -PageName "Never forget"
Out-1Note -Note @(Get-Service | Select Name,Status) -PageName "Never forget" -SectionName "Testing" 
Out-1Note -Note "I like turtles" -PageName "Never forget" -SectionName "Testing" -Append

Get-1NoteContent -PageName "Never forget"
Get-1NoteContent -PageName "Never forget" -SectionName "Testing"
Get-1NoteContent -PageName "Never forget" -SectionName "Testing" -OutDefault

Search-1Note "Remember to call" -Categories
Search-1Note "Remember to call" | Get-1NoteContent

Nice work but just its seems that one little bug slipping through......if you have a Onenote page like this:

---

Topic1:

• Line1
• Line2

Topic2:

• Line1
• Line2

Topic3:

• Line1
• Line2

---

In other words, normal line ending in colon, followed by normal bullet (or numbered) list, results in only the top-level items returned as in this example shown below:

Get-1NoteContent -PageName 'Testpage'

Page                  Data
-----                ------
Testpage             Topic1:
Testpage             Topic2:
Testpage             Topic3:

and if using Get-1NoteContent -PageName 'Testpage' -OutDefault

Topic1:

Topic2:

Topic3:

If there is a way to capture the list items that would be awesome!

Nice work but just its seems that one little bug slipping through......if you have a Onenote page like this:

Topic1:

• Line1
• Line2

Topic2:

• Line1
• Line2

Topic3:

• Line1
• Line2

If there is a way to capture the list items that would be awesome!

After some testing the items are an array of OE elements nested inside another OE.
I added a foreach loop to extract the item into an array separated by newlines.
This works for Bullets and Numbering.
Let me know if you have any issues.