Import Google Sheet in a Jira customfield with Scriptrunner

For this exercise we are going to base ourselves on 2 posts that we detail below:

For the exercise, we will go to our personal Gmail account and create a Google Sheet

 

Note that the name of the Sheet/Page is “people“

Now we are going to create the Google App Script, for this, we will edit the file “Code.gs“

Now add the following code:


function json(sheetName) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = spreadsheet.getSheetByName(sheetName)
  const data = sheet.getDataRange().getValues()
  const jsonData = convertToJson(data)
  return ContentService
        .createTextOutput(JSON.stringify(jsonData))
        .setMimeType(ContentService.MimeType.JSON)
}
 
function convertToJson(data) {
  const headers = data[0]
  const raw_data = data.slice(1,)
  let json = []
  raw_data.forEach(d => {
      let object = {}
      for (let i = 0; i < headers.length; i++) {
        object[headers[i]] = d[i]
      }
      json.push(object)
  });
  return json
}
 
function doGet(e) {
  const path = e.parameter.path
  //return json(path)
  return json("people")
}

Note that in the last line we have added: return json(“people”) since we will always return the “people” sheet, but better uncomment the line //return json(path) to use parameters in the REST API URL.

Once you are done with this, the final step is to publish your Apps Script as a Web App. You can simply create a New Deployment and set the Execute As to me and Who has access to Anyone. These settings allow your Web App to be publicly accessible.

Now we have the URL of the REST API, In this case, the URL is https://script.google.com/macros/s/AKfycbw9gpHbIauF8obidyDjxe3_L9qA-Ww-e8bv6pvNNGavAv-xxxxxxxxxxxxxxxxxxxxxxx/exec

or if we use “params” in the URL

In this case, the URL is https://script.google.com/macros/s/AKfycbw9gpHbIauF8obidyDjxe3_L9qA-Ww-e8bv6pvNNGavAv-xxxxxxxxxxxxxxxxxxxxxxx/exec?path=people.

Well, now it’s time to go to Jira to configure the customfield and we will apply a Behavior and a custom REST Endpoint from Scriptrunner inside Jira that will fill in the customfield values when editing or creating a Ticket. Those values will be obtained from the Jira REST Endpoint custom which in turn will call the Google Sheet REST Endpoint custom. We will use CURL for the operation, in other words CURL must be installed on your Jira server OS for this exercise.

First create a field in Jira of type text single line. Name it “Google Sheet Field”

Create a Behaviour with a content like this in the Initialiser:


import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.sal.api.user.UserManager
 
def constantsManager = ComponentAccessor.getConstantsManager()
  
def userUtil = ComponentAccessor.getUserUtil()
log.debug ComponentAccessor.getComponent(UserManager)
 
def issueManager = ComponentAccessor.getIssueManager()
def issuekey =  issueManager.getIssueObject(getContextIssueId())?.getKey()
 
getFieldByName("Google Sheet Field").convertToSingleSelect([
    ajaxOptions: [
        url : getBaseUrl() + "/rest/scriptrunner/latest/custom/GoogleSheetQuery?requesttype=999",
        query: true, // keep going back to the sever for each keystroke
        minQueryLength: 4,
        keyInputPeriod: 500,
        formatResponse: "general",
    ]
])

Now create a REST Endpoint in Jira (In this example the REST Endpoint is protected only to be called by users members of jira-users group)

The REST new Endpoint will be: https://jira.example.com/rest/scriptrunner/latest/custom/GoogleSheetQuery?requesttype=999

Note the param “requesttype with value 999“


import groovy.json.JsonSlurper; 
import com.atlassian.jira.issue.comments.CommentManager
import com.atlassian.jira.user.util.UserManager
import com.atlassian.jira.component.ComponentAccessor
 
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
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 edreamsodigeo.MyCompanyProperties 
  
@BaseScript CustomEndpointDelegate delegate
  
GoogleSheetQuery(httpMethod: "GET", groups: ["jira-users"]) { MultivaluedMap queryParams ->
 
        def query = queryParams.getFirst("query") as String
        log.warn("query:"+ query)
        def requesttype = queryParams.getFirst("requesttype") as String
        log.warn("requesttype:"+ requesttype)
         
        
         
        def rt = [:]
        def retorno = ""
        if ( query == null ) query = ""
        if (query || true ) {
            def sout = new StringBuilder(), serr = new StringBuilder()
            def proc = ""
             if ( requesttype == "999" ) { 
                proc = ("curl -H 'Accept: application/json' -L 'https://script.google.com/macros/s/google_url_obtained/exec?path=people'" ) 
             }
             log.warn(proc)
             def proc2 = [ 'bash', '-c', proc].execute()
             proc2.consumeProcessOutput(sout, serr)
             proc2.waitFor();
             def result = sout.toString()
             def error = serr.toString(); 
             log.warn("ERROR:"+error)
             log.warn("RESULT:"+result)
             result = jsonSlurper.parseText(result)
             def parentIds = null
             def Ids = null
             if ( requesttype == "999" ) { 
                result = result.name
             } 
              
                        def items_2 = result  
                        //def items_3 = seleccion.Resources*.value   
                        def item_list_init = '{"items":[';
                        //def item_list_end = '],"total":"'+seleccion.totalResults+'","footer":"Choose app..."}';
 
                        int i = 0;
                        int j = 0;
                        while ( i < items_2.size()) {
                            if (items_2[i].toLowerCase().contains(query.toLowerCase())|| (query == "")) {
                                 if (parentId != null && parentId!= "") {
                                    if (parentId.isNumber()) {  
                                        if (parentIds[i] == parentId.toInteger()) { 
                                            //log.warn("parentId is number")
                                            items_2[i] = items_2[i] + " (" + Ids[i] + ")"
                                            item_list_init = item_list_init + '{"value":"'+items_2[i].replaceAll("\\\\", "\\\\\\\\")+'","html":"'+items_2[i].replaceAll("\\\\", "\\\\\\\\")+'","label":"'+items_2[i].replaceAll("\\\\", "\\\\\\\\")+'"},' 
                                        }
                                    }    
                                 } else {
                                    //log.warn("no parentId")
                                    item_list_init = item_list_init + '{"value":"'+items_2[i].replaceAll("\\\\", "\\\\\\\\")+'","html":"'+items_2[i].replaceAll("\\\\", "\\\\\\\\")+'","label":"'+items_2[i].replaceAll("\\\\", "\\\\\\\\")+'"},'
                                 }
                                 j++;
                            }
                            i++;    
                        }
                        def item_list_end = '],"total":"' + j + '","footer":"Choose option..."}';
                        retorno = item_list_init[0..-2] + item_list_end;
                        if (j == 0) {
                            retorno = '{"items":[],"total":"0","footer":"Choose option..."}'
                        }
             
       }
 return Response.ok(retorno).build();
}

Note we use this CURL call to the system in order to obtain the data of the Google Sheet

curl -H ‘Accept: application/json’ -L ‘https://script.google.com/macros/s/google_url_obtained/exec?path=people&#8217;

It’s important to use -L para to follow URL changes after the call to Google

Now is turn to test it!

 

Raúl Peláez - June 5, 2023 / Share it: