How to access/read data from a Google spreadsheet using scriptRunner (without Appscript) ?

[Vignesh Poopandian]

Hello,

I'm trying to automate User addition to Jira group (to be performed by non-Jira users), for which I'm planning to create a google form and the results would be stored in spreadsheet.

Sample data the spreadsheet would contain : "Group Name", "user email"

Now, I'm able to add users to a group using ScriptRunner and REST API.

Below is the code I'm using,

import com.atlassian.jira.component.ComponentAccessor

import com.atlassian.jira.config.properties.APKeys

import com.atlassian.sal.api.net.Response

import com.atlassian.sal.api.net.ResponseException

import com.atlassian.sal.api.net.ReturningResponseHandler

import com.atlassian.sal.api.net.TrustedRequest

import com.atlassian.sal.api.net.TrustedRequestFactory

import com.atlassian.sal.api.net.Request

import groovy.json.JsonBuilder

import groovy.json.JsonSlurper

import groovyx.net.http.ContentType

import groovyx.net.http.URIBuilder

import java.net.URL;

import java.nio.file.Path;

def currentUser = ComponentAccessor.jiraAuthenticationContext.loggedInUser

def baseUrl = ComponentAccessor.applicationProperties.getString(APKeys.JIRA_BASEURL)

def trustedRequestFactory = ComponentAccessor.getOSGiComponentInstanceOfType(TrustedRequestFactory)

//def payload = new JsonBuilder([payloadField: 'payload value']).toString()

def payload = new JsonBuilder([name: 'user_email_to_be_added_to_group']).toString() // USER TO BE ADDED

def endPointPath = '/rest/api/2/group/user?groupname=GROUP_NAME' // GROUP NAME

def url = baseUrl + endPointPath

def request = trustedRequestFactory.createTrustedRequest(Request.MethodType.POST, url) as TrustedRequest

//request.addTrustedTokenAuthentication(new URIBuilder(baseUrl).host, currentUser.name)

request.addBasicAuthentication(new URIBuilder(baseUrl).host,"USERNAME","PASSWORD") // BASIC AUTH CREDENTIALS

request.addHeader("Content-Type", ContentType.JSON.toString())

request.addHeader("X-Atlassian-Token", 'no-check')

request.setRequestBody(payload)

def response = request.executeAndReturn(new ReturningResponseHandler<Response, Object>() {

    Object handle(Response response) throws ResponseException {

        if (response.statusCode != HttpURLConnection.HTTP_OK) {

            log.error "Received an error while posting to the rest api. StatusCode=$response.statusCode. Response Body: $response.responseBodyAsString"

            return response.responseBodyAsString

        } else {

            def jsonResp = new JsonSlurper().parseText(response.responseBodyAsString)

            log.info "REST API reports success: $jsonResp"

            return jsonResp

             }

        }

    })

The above script is working well and good.

But, I'm not able to read or access a google spreadsheet data directly from Jira console using scriptrunner.

FYI, there is no connectivity between APPSCRIPT and JIRA in our organization. The connection is denied.

So, I'm looking for some other alternates to directly read a spreadsheet data to be used in the above script.

Thanks in advance!

Regards,

Vignesh

[Ram Kumar Aravindakshan _Adaptavist_]

Hi @Vignesh Poopandian

This is not a simple task. You must ensure that you have the correct credentials and certificates to even be able to access the Google Spreadsheet REST API.

From the approach you are trying in your code, i.e. basic username and password, this will not work.

You could, however, try the example provided in the Adaptavist Library to connect to external REST Endpoints. You will need to play around with the code and modify it accordingly.

I hope this helps to solve your question. :-)

Thank you and Kind regards,
Ram