Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Upload table via the API using powershell

Frank_Tighe March 12, 2020

I want to use confluence to automate some auditing of our enviroments but am running into errors when I try and upload data via the API. 

Honestly i think i may be approaching this the wrong way so please feel free to correct me.

I am collecting data from AD then using the built in Convertto-html command. After a litle clean up my data looks like bellow

<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/></colgroup>
<tr><th>SamAccountName</th><th>LastLogonDate</th><th>Description</th></tr>
<tr><td>Usern.nme1</td><td>13/10/2019 10:05:05</td><td>Fleet</td></tr>
<tr><td>Usern.nme2</td><td>18/09/2018 15:40:12</td><td>CSR Team 
</table>
</body>

I then add this with some other data to a variable calle $body, which looks like the following: 

{"type":"page","title":"90 day inactivity report", "version":{"number":7},"body":{"storage":{"value":"<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/></colgroup>
<tr><th>SamAccountName</th><th>LastLogonDate</th><th>Description</th></tr>
<tr><td>Usern.nme1</td><td>13/10/2019 10:05:05</td><td>Fleet</td></tr>
<tr><td>Usern.nme2</td><td>18/09/2018 15:40:12</td><td>CSR Team 
<tr><td>Usern.nme1</td><td>16/06/2018 17:54:21</td><td>CSR</td></tr>
</table>
</body>","representation":"storage"}}}

And then issue the following to upload the data

Invoke-RestMethod https://jira.domain.co.uk/confluence/rest/api/content/45056041?expand=body.storage -Method Put -Headers $Headers -Body $body

 

and get this error

Invoke-RestMethod : The remote server returned an error: (500) Internal Server Error.
At line:1 char:1

 

My feeling is it doesnt like a character in the data, as i tried this with just "test" as the payload and it works without issue. but the error is so vague.

 

Can anyone tell me how could i:

 

  • Get more informaiton from the API as to why a quiery falled
  • Upload special characters via the API
  • Just help in general

 

Thanks in advance

2 answers

0 votes
Paul March 4, 2022 edited

Hi,

Your HTML code appears to be missing a end of column and end of row tag </td></tr> on the CSR Team section. Also when posting HTML to Confluence I have found you don't need  the HTML <Header><Title><Body> elements as well, Confluence over-rides these with it's own HTML Page elements.

 

Here is my code that I have used to update a page :-)
The code also reads the current page version and auto increments the version number every time run the script.

Remember to update your Confluence Page URL and ID number

 

clear-host
clear-history

write-host -ForegroundColor Cyan "Requesting Login ID and password to authenticate with confluence."
if(-not($Credentials))
{
$Credentials = Get-Credential
}

$ConfluenceURL = "https://confluence.site/rest/api/"

$ConfluencePageID = "123456"

$Headers = @{"Authorization" = "Basic "+[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes(($Credentials.UserName+":" `
+[System.Runtime.InteropServices.marshal]::PtrToStringAuto([System.Runtime.InteropServices.marshal]::SecureStringToBSTR($Credentials.Password)) )))
"Accept"="application/json"
"ContentType"="application/json"
'X-Atlassian-Token' = 'nocheck'
}

$Call = "content/{0}?expand=version,body.storage,ancestors,space,results,contenttype" -f $ConfluencePageID

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

write-host -ForegroundColor Cyan "Logging into the confluence page that requires updating"
$CurrentConfluence = Invoke-WebRequest -Method GET -Headers $Headers -Uri ($ConfluenceURL + $Call) -UseBasicParsing | ConvertFrom-Json
$HTMLVersion = $($CurrentConfluence.version.number + 1)


Write-Host -ForegroundColor Cyan "obtaining HTML input data."


$HTMLBody = '<div><p><table><colgroup><col/><col/><col/></colgroup><tr><th>SamAccountName</th><th>LastLogonDate</th><th>Description</th></tr><tr><td>User.Name1</td><td>13/10/2019 10:05:05</td><td>Fleet</td></tr><tr><td>User.Name2</td><td>18/09/2018 15:40:12</td><td>CSR Team </td></tr><tr><td>User.Name1</td><td>16//06//2018 17:54:21</td><td>CSR</td></tr></table></p></div>'

$Body = '{{"type":"{0}","title":"{1}","version":{{"number":"{2}" }},"body":{{"storage":{{"value":"{3}","representation":"storage"}}}}}}' -f $CurrentConfluence.type, $CurrentConfluence.title, $HTMLVersion, $HTMLBody


[void][System.Text.Encoding]::UTF8.GetBytes($Body)

$Call = "content/$($ConfluencePageID)"

Write-Host -ForegroundColor Yellow "Updated the body contents with new information."
$Error.Clear()
read-host “Press ENTER to publish the update...”
try
{
$UpdateConfluence = Invoke-WebRequest -Method Put -Uri ($ConfluenceURL + $Call) -Body $Body -Headers $Headers -ContentType "application/json;" -UseBasicParsing | convertfrom-json
Write-Host -ForegroundColor Green "Update published"
}
catch
{
$Error
}

 

Capture.PNG

0 votes
Frank_Tighe March 12, 2020

Some further investigation suggests that this is caused by my table being a array.. 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
AUG Leaders

Atlassian Community Events