Adaptavist Scriptrunner Service-Now REST Endpoint Table sorting

[Steven Carmean]

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

[Peter-Dave Sheehan · Accepted solution]

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.

[Steven Carmean]

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" ? 

[Peter-Dave Sheehan]

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->

[Steven Carmean]

Excellent! Thanks again for your help!