send issue updates to a Microsoft Sharepoint List

[Christian Schneider]

I wrote a little groovy script, which I am very happily sharing. It took me some days (and nights) to get it functional - with a lot of help from the community.

use case

We are a little IT department and we are using Jira Service Desk and other Jira Projects to manage our work. Our internal customers do not have access to our Jira, they are primarily on sharepoint.

As we do have some planed maintenance down time and unplaned incicent downtime with our IT infrastructure and IT applications, we would like to be as transparent as possible to our internal clients. They should be able to have one place to look, if something is down and when the service is expected to be available again. We want our customers to check, before they open a ticket with us.

Until now, we had a manually managed sharepoint page, but it was badly managed by us and our customers do not go there.

idea

I want to set up a Jira project, within which we can add maintenance tasks or major incidents or problems. There will be a managed process. Every change on an issue in this Jira Project is mirrored into a sharepoint list. (well: create and update, no delete)

tools used

• JIRA MISC WORKFLOW EXTENSIONS (mostly for development)
• Scriptrunner Listener
• groovy RESTClient
• JSON
• Sharepoint API
• Sharepoint List

groovy script

And here is the script.

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.comments.Comment
import com.atlassian.jira.issue.comments.CommentManager
import com.atlassian.jira.issue.fields.CustomField

import groovyx.net.http.HTTPBuilder
import groovyx.net.http.RESTClient
import java.time.LocalDateTime


Issue issue = event.getIssue()

def issueKey = issue.getKey()
def issueStatus = issue.getStatus().getName()
def issueTitle = issue.getSummary()
def issueDescription = issue.getDescription()
def issueStartDate = ( issue.getCreated() != null ? issue.getCreated().format( 'yyyy-MM-dd HH:mm' ) : null )
def issueResolutionDate = ( issue.getResolutionDate() != null ? issue.getResolutionDate().format( 'yyyy-MM-dd HH:mm' ) : null )


def commentManager = ComponentAccessor.commentManager
def comments = commentManager.getComments(issue)
def issueComments = []
comments.each { comment ->
issueComments.add(comment.getCreated().format("dd.MM.yy HH:mm") + ":<br>" + comment.getBody())
}

def issueCommentsString = issueComments.join('<br><br><br>')

 


log.warn "Issue Key: ${issueKey}"
log.warn "Status: ${issueStatus}"
log.warn "Title: ${issueTitle}"
log.warn "Issue Description: ${issueDescription}"
log.warn "Startdate: ${issueStartDate}"
log.warn "Enddate: ${issueResolutionDate}"
log.warn "Comments: ${issueCommentsString}"

 

def baseURL = 'https://server.local/'
def type = 'application/json;odata=verbose'
def transactionID = UUID.randomUUID()
def UserAgent = 'Jira_Scriptrunner'

log.warn "${LocalDateTime.now()}: RESTClient TransactionID: ${transactionID}"

 

//------------------------------------------------------------------------------------------------------
// get FormDigestValue from sharepoint
def sharepointAPI = 'help-desk/it/St%C3%B6rungsmeldungen-Jira/_api/contextinfo'
def domain = 'domain'
def username = 'user'
def password = 'password'

def restClient = new RESTClient( baseURL )
restClient.auth.ntlm(username, password, '', domain)
def response = restClient.post(
path: sharepointAPI,
contentType: type,
headers: ['Connection': 'keep-alive', 'Accept': type])

//response.getContentType()
//response.getData()

log.warn "${LocalDateTime.now()}: Status Abruf FormDigestValue : ${response.getStatus()}"
//log.info "${response.getData()}"

def formDigestValue = response.data.d.GetContextWebInformation.FormDigestValue.toString()

 

//------------------------------------------------------------------------------------------------------
//check, if Jira issue already exists in sharepoint
def sharepointAPI_items = 'help-desk/it/Störungsmeldungen-Jira/_api/web/lists/getbytitle(\'jira\')/items'
def response2 = restClient.get(
path: sharepointAPI_items,
contentType: type,
headers: [
'TransactionID': transactionID,
'User-Agent': UserAgent,
'Connection': 'keep-alive',
'Accept': type,
'Content-Type': type,
'X-RequestDigest': formDigestValue
],
requestContentType: type,
query: [
'$filter': "JiraID eq \'${issueKey}\'",
'$top': '1',
'$select': 'ID, JiraID' 
]
)

def sharepointListItemID = response2.data.d.results.Id[0]
log.warn "ResponseData: ${response2.getData()}"
log.warn "sharepointListItemID: ${sharepointListItemID}"


//------------------------------------------------------------------------------------------------------
//create new or update existing ListItem in sharepoint
if (sharepointListItemID == null) {

//ListItem not on list -> create new ListItem in sharepoint
log.warn "${LocalDateTime.now()}: Jira-Issue NICHT in Sharepoint-Liste gefunden! --> create new"


//create new ListItem in sharepoint
def response3 = restClient.post(
path: sharepointAPI_items,
contentType: type,
headers: [
'TransactionID': transactionID,
'User-Agent': UserAgent,
//'Connection': 'keep-alive',
'Accept': type,
'Content-Type': type,
'X-RequestDigest': formDigestValue
],
requestContentType: type,
body: [
__metadata: [type: 'SP.Data.JiraListItem'],
JiraID: issueKey,
Title: issueTitle,
Beschreibung: issueDescription,
Kommentar: issueCommentsString,
Status: issueStatus,
Startdatum: issueStartDate,
Enddatum: issueResolutionDate
]
)

 

log.warn "${LocalDateTime.now()}: Status ListItem geschrieben : ${response3.getStatus()}"
log.warn "ResponseData: ${response3.getData()}"










} else {
//ListItem already on list -> update ListItem in sharepoint
log.warn "${LocalDateTime.now()}: Jira-Issue in Sharepoint-Liste gefunden! --> wird aktualisiert"


def sharepointAPI_update = "help-desk/it/Störungsmeldungen-Jira/_api/web/lists/getbytitle(\'Jira\')/items(${sharepointListItemID})"

def response3 = restClient.post(
path: sharepointAPI_update,
contentType: type,
headers: [
'TransactionID': transactionID,
'User-Agent': UserAgent,
//'Connection': 'keep-alive',
'Accept': type,
'Content-Type': type,
'X-RequestDigest': formDigestValue,
'X-HTTP-Method': 'MERGE',
'If-Match': '*'
],
requestContentType: type,
body: [
__metadata: [type: 'SP.Data.JiraListItem'],
JiraID: issueKey,
Title: issueTitle,
Beschreibung: issueDescription,
Kommentar: issueCommentsString,
Status: issueStatus,
Startdatum: issueStartDate,
Enddatum: issueResolutionDate
]
)

log.warn "${LocalDateTime.now()}: Jira-Issue in Sharepoint-Liste eingefügt : ${response3.getStatus()}"
log.warn "${response2.getData()}"



}

It might be obvious to you, that I am new to Groovy: the script is not yet perfect, but it works. Any comments/improvements are very welcome.

Many thanks go to:

@Sayed Bares 

@Ram Kumar Aravindakshan _Adaptavist_ 

@Fabio Racobaldo _Herzum_ 

References:

• Solved: NTLM Authentication in groovy (Scriptrunner -> Sha... (atlassian.com)
• Solved: Scriptrunner listener: get current issue data (atlassian.com)
• Get to know the SharePoint REST service | Microsoft Docs
• The Apache Groovy programming language - Documentation (groovy-lang.org)
• google ...

Edit:

For a better layout of the comments in sharepoint, use HTML <br>