REST Endpoint: Iterate over Json map and parse to HTML table
Hi all,
Based on my previous post, I want to be able to parse JSON data to an html table from data pulled from an external API, which in our case is our ServiceNow instance.
The REST Endpoint Code is as follows:
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.json.JsonOutput
import groovy.json.JsonSlurper
import groovy.xml.MarkupBuilder
import groovy.transform.BaseScript
import groovyx.net.http.ContentType
import groovyx.net.http.HTTPBuilder
import groovyx.net.http.Method
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
import javax.ws.rs.core.MediaType
@BaseScript CustomEndpointDelegate delegate2
CMRDisplay(httpMethod: "GET") { MultivaluedMap queryParams ->
def query = queryParams.getFirst("query") as String
def rt = [:]
if (query) {
String url = "https://test.service-now.com"
String uriPath = "/api/now/table/u_jira_change_data"
HTTPBuilder http = new HTTPBuilder(url)
def output = http.request(Method.GET, ContentType.JSON) {
uri.path = uriPath
uri.query = [sysparm_query:"u_jira_ticket_number=$query", sysparm_fields:"u_change_record.number,u_change_record.short_description,u_change_record.state", sysparm_display_value: "true"]
headers.'Authorization' = "Basic ${"xxxxxxxx".bytes.encodeBase64().toString()}"
response.failure = { resp, reader ->
log.warn("Failed to query ServiceNow API: " + reader.text)
}
}
def cmrState = output["result"]*."u_change_record.state"
def cmrNumber = output["result"]*."u_change_record.number"
def cmrDesc = output["result"]*."u_change_record.short_description"
def table =
"""<table class="aui aui-table-list" aria-hidden="true">
<thead>
<tr>
<th id="record-num">Record Number</th>
<th id="record-desc">Description</th>
<th id="record-state">State</th>
</tr>
</thead>
<tbody>
<tr class="aui-row-subtle">
<td headers="record-num">${cmrNumber}</td>
<td headers="record-desc">${cmrDesc}</td>
<td headers="record-state">${cmrState}</td>
</tr>
</tbody>
</table>
"""
Response.ok().type(MediaType.TEXT_HTML).entity(table.toString()).build()
}
}
Working with a test issue in our sandbox instance, we get the following JSON data:
{
"result": [
{
"u_change_record.number":"CHG0010042",
"u_change_record.state":"Draft",
"u_change_record.short_description":"test app req 5"
},
{
"u_change_record.number":"CHG0010061",
"u_change_record.state":"Draft",
"u_change_record.short_description":"test"
},
{
"u_change_record.number":"CHG0016010",
"u_change_record.state":"Draft",
"u_change_record.short_description":"Test Jira"
},
{
"u_change_record.number":"CHG0010057",
"u_change_record.state":"Draft",
"u_change_record.short_description":"tesst"
}
]
}And the REST Endpoint code renders the json into the following. html table:
My only problem now is that the rows don't generate as expected. Obviously, there should be 4 rows in total, with [CMR1..., test app req 5, Draft] in one row, then [CMR2..., test, Draft] as the second row, and so on. But instead, the output results are being crammed into one row.
I want the table to look something like this, but styled with css to resemble a Jira table:
Additionally, the css class attributes don't take effect for some reason. Referencing Jira's AUI Documentation, I figured that the CSS would automatically apply when referencing the 'aui' class in for the table.
I know I have to iterate through the JSON Map somehow, but I don't know how to map that out to the html table and have each record number/recrod desc/record state set listed out as rows on the table.
Any tips/suggestions as always are highly appreciated!
2 answers
1 accepted
I would recommend you become familiar with groovy's markup builder
Here is something I quickly put together to in the Scriptrunner Console
import groovy.json.JsonSlurper
import groovy.xml.MarkupBuilder
def jsonString ="""{"result": [{
"u_change_record.number":"CHG0010042",
"u_change_record.state":"Draft",
"u_change_record.short_description":"test app req 5"
},{
"u_change_record.number":"CHG0010061",
"u_change_record.state":"Draft",
"u_change_record.short_description":"test"
},{
"u_change_record.number":"CHG0016010",
"u_change_record.state":"Draft",
"u_change_record.short_description":"Test Jira"
},{
"u_change_record.number":"CHG0010057",
"u_change_record.state":"Draft",
"u_change_record.short_description":"tesst"
}]}"""
def json = new JsonSlurper().parseText(jsonString)
def writer = new StringWriter()
def xml = new MarkupBuilder(writer)
xml.table(class:'aui'){
thead{
tr{
json.result[0].keySet().each{
th it.split(/\./)[1]
}
}
}
tbody{
json.result.each{ res->
tr{
res.values().each{
td it
}
}
}
}
}
writer.toString()
That generated a table that looks like this:
Thanks for mentioning the credentials part...big uh oh on my end.
And yes that's exactly what it was. I forgot the builder already took care of the Json.
Now I have a table as expected :D
Many Thanks!
Only one question left though...how come the builder didn't register the "class:aui" attribute? Because now the only thing is that the css didn't carry over.
Ok. Two more things (and then I should be good to go. You've been a really great help to me, I can't thank you enough!):
1.) When you mention creating a style block above my table, how exactly would I initiate that? Would I write something like :
style{
.
css
.
}right above where the structure of the table starts (above xml.table), define the classes with css elements, and then reference the class names for thead, dr, etc.? (Hope this makes sense.)
2.) Looking through the groovy markup builder doc that you referred to me in the beginning of this post, I don't see anything about css property names that are accepted in groovy/by the markupbuilder. If you review the css I added to the headers and (attempted to add to the) table:
xml.table('style':'column-rule: 4px double #ff00ff'){
thead('style':'color:rgba(183,189,199,255); font-family:arial,sans-serif; border: 1px solid #dddddd'){
tr{
jsonOutput.result[0].keySet().each{
th it.split(/\./)[1]
}
}
}
tbody{
jsonOutput.result.each{ res->
tr{
res.values().each{
td it
}
}
}
}
}
The color and font for the headers change, as expected. But the border doesn't get created, and for the table as a whole, the column-rule property doesn't get rendered. Is this because I'm not setting the properties correctly?
And on a further note regarding the css properties usable by the markupbuilder code/groovy in general, do you know of any documentation/web resources that I could reference to further build upon stylizing the table within the rest endpoint code? My end goal is to make this table look similar to the tables in a jira environment...like the table generated from the markup builder code that you ran in the scriptrunner console.
________________________
I'm very close to the output I'm looking for from the REST Endpoint, and I couldn't have done it without your help. Thank you!
And there we have it! Amazing!
@Peter-Dave Sheehan Thanks for putting the time into helping with this!
Thanks for the suggestion of mapping/calling the headers! I didn't think about the order of the values potentially changing up in response.
The updated headers successfully display with the changes to thead:
.
.
.
def headerMaps = [
'u_change_record.number': 'Number',
'u_change_record.state' : 'State',
'u_change_record.short_description' : 'Short Description'
]
xml.table(class:'aui'){
thead {
tr{
output.result[0].keySet().each{
th headerMaps[it] ?: it.split(/\./)[1]
}
}
}
.
.
.
...as for the url embedding, however, that still doesn't work :(
I went with using the loop and edited the tbody block as follows:
tbody{
output.result.each{ res->
tr{
res.values().each{
if(it=='number'){
td {
def number = res['u_change_record.number']
a(href:"https://test.service-now.com/cm?id=cm_stdnew&table=change_request&view=sp&number=$number",target:'_blank') number
}
} else {
td it
}
}
}
}
}
And here's how the table looks now with the changes in place:
The headers successfully changed. But the "Number" values are still plain and the urls didn't get embedded. It's nothing to do with the url itself because I even tried testing with the link to Google, and that didn't get embedded either.
Thanks!
That seems to have solved the problem with the url embedding...but there's a new problem now. The values for State and Short Description don't appear now.
Ah, I think I got it! All I had to change was at the end of the condition to td val:
tbody{
output.result.each{ res->
tr{
res.values().eachWithIndex{val, idx->
def colHeader= res.keySet()[idx]
if(colHeader=='u_change_record.number'){
td {
a(href:"https://test.service-now.com/cm?id=cm_stdnew&table=change_request&view=sp&number=$val",target:'_blank') {
mkp.yield val
}
}
} else {
td val
}
}
}
}
}Let me know if this was the right way to correct the code.
But with that, I think I now have my final expected result:
Once again, thanks for all the help!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.