We are attempting to parse JSON data coming in from a GET call from Service-Now and sort by the "short_description"
Right now our output is sorting by project number.
import groovyx.net.http.ContentType
import groovyx.net.http.HTTPBuilder
import groovyx.net.http.Method
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 java.util.ArrayList
//import javax.ws.rs.core.MediaType
def url ="https://test.service-now.com"
def endpoint = "/api/now/table/pm_project"
def un = "username"
def pw = "password"
def encoded = "$un:$pw".toString().bytes.encodeBase64().toString()
def returnResponse
def httpBuilder = new HTTPBuilder(url)
def writer = new StringWriter()
def xml = new MarkupBuilder(writer)
try{
def output = httpBuilder.request(Method.GET, ContentType.JSON) {
headers."Authorization" = "Basic $encoded"
uri.path = endpoint
uri.query = [sysparm_fields:"number,short_description,sys_id",
sysparm_display_value: "true"]
xml.style(type:"text/css",
'''
#scriptField, #scriptField *{
border: 1px solid black;
}
#scriptField{
border-collapse: collapse;
}
''')
response.success = {response, json ->
xml.table(id:"scriptField"){
tr{
th("Project Number")
th("Project Name")
//th("Project Link")
}
if (json) {
returnResponse = json.result
for (int i = 0; i < returnResponse.size(); i++){
tr{
td{a(href:"https://test.service-now.com/pm_project.do?sys_id="+returnResponse."sys_id".get(i)+"&sysparm_stack=pm_project_list.do?sysparm_query=active=true",target:'_blank'){mkp.yield returnResponse."number".get(i)}};
td(returnResponse."short_description".get(i));
}
}
}
}
}
//return (writer.toString())
response.failure = { resp, data ->
if (data) {
returnResponse = [error: "Failed to make http request to $url/$endpoint", statusLine: resp.statusLine, data: data]
} else {
returnResponse = [error: "Failed to make http request to $url/$endpoint", statusLine: resp.statusLine]
}
}
}
} catch(e){
returnResponse = [error: "Some other error occured: $e"]
}
//returnResponse
return (writer.toString())
Right now this will send an output that looks like the screenshot
We are looking to try and sort by the project name/short description instead of the project number. We were given this URL that does the sorting for us, but we can't figure out how to add the system parameters to allow for sorting:
https://test.service-now.com/api/now/table/pm_project?sysparm_query=active%3Dtrue%5EORDERBYshort_description&sysparm_fields=short_description%2Cnumber%2Csys_id
Try this snippet:
import groovyx.net.http.URIBuilder
/* ... */
if (json) {
(json.result as List<Map>).sort{it.short_description}.each{item->
tr{
td {
def uri = new URIBuilder('https://test.service-now.com')
uri.path = 'pm_project.do'
uri.query = [
sys_id: item.sys_id,
sysparm_stack:'pm_project_list.do?sysparm_query=active=true'
]
a href: uri.toString(), target: '_blank', item.number.toString()
}
td item.short_description.toString()
}
}
}
/* ... */
This will take the results and sort them by the short_description value (regardless of what order they are received in).
Also, I've changed your for loop for a groovy collect "each" iterator. And I've added a URI builder to make things easier to read.
That seems to be working, Thank you!
Should we be worried about this error about json.result showing "No such property: result for class: java.lang.Object" ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
No, that's just the editor telling you that it doesn't really know what "json" is, so it can't confirm if "result" is valid.
You could tell the editor like this:
if (json) {
def results = (json as Map).result as List<Map>
results.sort{it.short_description}.each{item->
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.