Atlassian Confluence - Updating Tables with PowerShell

TLDR - GitHub Script

Ok, I know that this post, and my last post about using PowerShell with Jira aren't exactly the normal things I blog about, but I've been working with Jira & Confluence lately, and if for no other reason than to help me remember things in the future, I'm going to blog about it.

Before I continue, I would have been pretty stuck if not for this Post: "How to create/update/read HTML table on Confluence wiki page using PowerShell" where I grabbed this function: _convertFromHTMLTable and incorporated it into my script.

I'm not going into that function, you can check it out on that blog post.

Overview

  • Connect to your Confluence Cloud Wiki via user name & Token
    • Requires PS Module = ConfluencePS (From PS Gallery)
  • Get the Page you want to update
  • Convert the Table using the function
  • Update the Table (Modify and/or Append)
  • Grab other Page Content
  • Put it all back together and overwrite the Confluence Page with your changes.

Connecting To Confluence

Please see my previous post about getting up a Free Atlassian Cloud subscription, then go ahead and setup your own Confluence Instance. Follow the last post to get your Token as well.

#Connection Creds to JIRA Cloud
#https://support.atlassian.com/atlassian-account/docs/manage-api-tokens-for-your-atlassian-account/
$AtlassianName = "YOUR ACCOUNT EMAIL HERE"
$AtlassianToken = 'YOUR TOKEN HERE' | ConvertTo-SecureString -Force -AsPlainText 
if (!($Credential)){$Credential = New-Object System.Management.Automation.PsCredential("$AtlassianName",$AtlassianToken)}
$AtlassianConfigServer = "https://garytown.atlassian.net" #YOUR SERVER THERE
Import-Module ConfluencePS -ErrorAction Stop
# authenticate to your Confluence space
$baseUri = "$AtlassianConfigServer/wiki"
Set-ConfluenceInfo -BaseURi "$baseUri" -Credential $Credential

The basics are that you need to feed it your Server that you setup (https://YourServer.atlassian.net/wiki), and the credentials.

Now that you have that, you can leverage commands to get the page info. In this Post, I've created a Confluence page for our regression testing named: "WaaS 20H2 Regression Testing"

Pulling Confluence Page & Build the PowerShell Table

At this point, with your connection to Confluence, you can grab the page and build your PowerShell Table Array

Add-Type -AssemblyName System.Web

function _convertFromHTMLTable {
    # function convert html object to PS object
    # expects object returned by (Invoke-WebRequest).parsedHtml as input
    param ([System.__ComObject]$table)

    $columnName = $table.getElementsByTagName("th") | % { $_.innerText -replace "^\s*|\s*$" }

    $table.getElementsByTagName("tr") | % {
        # per row I read cell content and returns object
        $columnValue = $_.getElementsByTagName("td") | % { $_.innerText -replace "^\s*|\s*$" }
        if ($columnValue) {
            $property = [ordered]@{ }
            $i = 0
            $columnName | % {
                $property.$_ = $columnValue[$i]
                ++$i
            }

            New-Object -TypeName PSObject -Property $property
        } else {
            # row doesn't contain <td>, its probably headline
        }
    }
}


$PageToUpdate = Get-ConfluencePage -SpaceKey 'MEMCM' | Where-Object {$_.Title -match "WaaS 20H2 Regression Testing"}
$pageID = $PageToUpdate.ID

$Headers = @{"Authorization" = "Basic " + [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes(($Credential.UserName + ":" + [System.Runtime.InteropServices.marshal]::PtrToStringAuto([System.Runtime.InteropServices.marshal]::SecureStringToBSTR($Credential.Password)) ))) }

# Invoke-WebRequest instead of Get-ConfluencePage to be able to use ParsedHtml
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
try {
    $confluencePageContent = Invoke-WebRequest -Method GET -Headers $Headers -Uri "$baseUri/rest/api/content/$pageID`?expand=body.storage" -ea stop
} catch {
    if ($_.exception -match "The response content cannot be parsed because the Internet Explorer engine is not available") {
        throw "Error was: $($_.exception)`n Run following command on $env:COMPUTERNAME to solve this:`nSet-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Internet Explorer\Main' -Name DisableFirstRunCustomize -Value 2"
    } else {
        throw $_
    }
}

# from confluence page get content of the first html table
$table = $confluencePageContent.ParsedHtml.GetElementsByTagName('table')[0]

# convert HTML table to PS object
$confluenceContent = @(_convertFromHTMLTable $table)
#endregion get data from confluence page (table)

At this Point, the $PageToUpdate contains the entire page, including the information we'll need later to rebuild the page.

We also have the entire table of data in the $conflurenceContent variable, but using a Invoke-WebRequest instead of leveraging the typical Get-ConfluencePage function.

Updating Table in PowerShell to Add Data

During my Task Sequence, it creates a JSON File and populates a file share with information about that Machine and Testing Process. (if you want it, the script is on GitHub)

The script loops through, creating an array of all of the JSON Data, then compares it to the data in the table, either updating a row, or appending a new one.

#Get Regression Testing JSON Files & build Array
$RegressionFolder = "\\src.corp.viamonstra.com\logs$\Regression"
$JSONFiles = Get-ChildItem -Path $RegressionFolder -Recurse -Filter "*.json"
$JSONArray = @()
Foreach ($JSONFile in $JSONFiles)
    {
    $JSONRaw = Get-Content -Path $JSONFile.fullname | ConvertFrom-Json
    $JSONArray += $JSONRaw
    }

Now we want to merge the data (skip duplicates, or update if it has a newer run), and append new machines not in the table.

#Update Confluence Table Object with Data from Regression Testing
$UpdateRequired = $false
Foreach ($JSONItem in $JSONArray)#{}
    {
    $UpdateContent = $confluenceContent | Where-Object {$_.Name -match $JSONItem.Name}
    if ($UpdateContent){
        if ($UpdateContent.'Test Date' -eq $JSONItem.'TS Start')
            {
            Write-Output "Duplicate, Skipping update of Record: $($JSONItem.Name) on $($JSONItem.'TS Start')"
            }
        else
            {
            UpdateRequired = $true
            Write-Output "Updating Record for $($JSONItem.Name) on $($JSONItem.'TS Start')"
            $UpdateContent.Make = $JSONItem.Manufacturer
            $UpdateContent.Model = $JSONItem.Model
            $UpdateContent.'Product / Type ID' = $JSONItem.ID
            $UpdateContent.Name = $JSONItem.Name
            $UpdateContent.User = $JSONItem.LoggedON
            $UpdateContent.'BIOS Version' = $JSONitem.'BIOS Version'
            $UpdateContent.'BIOS Mode' = $JSONItem.'BIOS Mode'
            $UpdateContent.'Driver Version' = $JSONItem.DriverPack
            $UpdateContent.Encryption = $JSONItem.Encryption
            $UpdateContent.'Win Build' = $JSONItem.IPUBuild
            $UpdateContent.Status = $JSONItem.WaaS_Stage
            $UpdateContent.'Test Date' = $JSONItem.'TS Start'

            }
        }   
    Else{
        $UpdateRequired = $true        
        Write-Output "Creating new Record for $($JSONItem.Name) on $($JSONItem.'TS Start')"
        $NewContent = New-Object PSObject
        $TemplateRecord = $confluenceContent | Select-Object -First 1
        $TemplateRecord.psobject.properties | % {
            $NewContent | Add-Member -MemberType $_.MemberType -Name $_.Name -Value $_.Value
            }
        $NewContent.Make = $JSONItem.Manufacturer
        $NewContent.Model = $JSONItem.Model
        $NewContent.'Product / Type ID' = $JSONItem.ID
        $NewContent.Name = $JSONItem.Name
        $NewContent.User = $JSONItem.LoggedON
        $NewContent.'BIOS Version' = $JSONitem.'BIOS Version'
        $NewContent.'BIOS Mode' = $JSONItem.'BIOS Mode'
        $NewContent.'Driver Version' = $JSONItem.DriverPack
        $NewContent.Encryption = $JSONItem.Encryption
        $NewContent.'Win Build' = $JSONItem.IPUBuild
        $NewContent.Status = $JSONItem.WaaS_Stage
        $NewContent.'Test Date' = $JSONItem.'TS Start'
        $confluenceContent = $confluenceContent + $NewContent
        }
    }

That block of code checks each item in the JSON, compares it to the table, and either updates the row, skips to the next machine in the array, or creates a new record. If no changes are needed, it keeps $UpdateRequired = $false.

At this point, we'll have the updated $confluenceContent, with additional items

Now that we have all of the data, it's time to build a Confluence Page in PowerShell.

if ($UpdateRequired -eq $true){
    #Update Confluence Page
    $BodyHeader = ($($PageToUpdate.Body) -Split('<table'))[0]
    $BodyFooter = ($($PageToUpdate.Body) -Split('</table>'))[-1]
    $Tablebody = $confluenceContent | ConvertTo-ConfluenceTable | ConvertTo-ConfluenceStorageFormat
    $UpdatedBody = $BodyHeader + $Tablebody + $BodyFooter
    Set-ConfluencePage -PageID $pageID -Body $UpdatedBody
    }

This will grab the Information from the current page before the Table Starts and place it in the $BodyHeader, and the information after the Table and put it in $Body Footer, and create $TableBody from the $confluenceContent. Then $UpdatedBody puts them all together.

Lastly, we set the Page to $UpdatedBody

NOTE, you might have some odd issues if you have more than 1 table on the page, I have not tested that, but pretty sure things would go poorly.

Output from Set-ConfluencePage command

Back in our Confluence Site:

Only the Table has updated, rest of the Page text stays intact.

Few things I've noticed... the Table isn't so user friendly after, I personally had issues with the sort feature in Confluence, and the pretty formatting of the cells went away. However, for a simple table that displays technical information, it's good enough in my book.

You can get the full script on GitHub.

Posted at GARYTOWN.COM

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.