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”
![](https://garytown.com/wp-content/uploads/2021/07/image-10-1024x539.png)
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.
![](https://garytown.com/wp-content/uploads/2021/07/image-13-812x1024.png)
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)
![](https://garytown.com/wp-content/uploads/2021/07/image-11.png)
![](https://garytown.com/wp-content/uploads/2021/07/image-12-1024x629.png)
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
}
![](https://garytown.com/wp-content/uploads/2021/07/image-14-783x1024.png)
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
![](https://garytown.com/wp-content/uploads/2021/07/image-15-1024x119.png)
![](https://garytown.com/wp-content/uploads/2021/07/image-16-603x1024.png)
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.
![](https://garytown.com/wp-content/uploads/2021/07/image-17-1024x85.png)
Back in our Confluence Site:
![](https://garytown.com/wp-content/uploads/2021/07/image-19-1024x680.png)
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
You will want to add -basicparsing to the invoke-webrequest
It says, basic authentication with passwords is deprecated. For more information, see: https://developer.atlassian.com/cloud/confluence/deprecation-notice-basic-auth/
not sure what is the workaround for this issue? Using ConfluencePS module?