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 “

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

or if we use “params” in the URL

In this case, the URL is

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:

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 groovy.json.JsonBuilder
import groovy.transform.BaseScript
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 ''" ) 
             def proc2 = [ 'bash', '-c', proc].execute()
             proc2.consumeProcessOutput(sout, serr)
             def result = sout.toString()
             def error = serr.toString(); 
             result = jsonSlurper.parseText(result)
             def parentIds = null
             def Ids = null
             if ( requesttype == "999" ) { 
                result =
                        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("\\\\", "\\\\\\\\")+'"},'
                        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 ‘;

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: