I want to send an e-mail it has a inside JQL

Erdem UÇAK January 27, 2025

Hi community,

I have a JQL query and I want to send an e-mail this result of JQL query. I tried some method (scriptrunner, automation) but I could not resolve. I tried better excel automation and better excel export tool for this situation but I could not resolve again. Could you please help me Community

4 answers

1 accepted

1 vote
Answer accepted
Kawan Diogo
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 28, 2025

Hi @Erdem UÇAK 

 

I created a script template that might be useful for your request.

It will fetch the JQL query results and include them in a .csv file, then send it to the desired email.

Note: This template only handles simple information. If you need to include more details, don't forget to reference the creation of the file and make edits in the same order.

 

import com.atlassian.core.util.FileUtils
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.mail.Email
import javax.mail.internet.MimeBodyPart
import javax.mail.internet.MimeMultipart
import javax.activation.DataHandler
import javax.activation.FileDataSource
import javax.mail.internet.MimeMessage
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.mail.server.SMTPMailServer

// Set TO
def recipientEmail = 'sample@email.com'

// Set Subject
def subject = 'Test mail'

// Set Body
def emailBody = 'Sample Email'

// Set the JQL
def jqlQuery = """project not in ("project1", "project2", "project3") AND status in (Resolved, Closed) AND createdDate >= -1w AND resolved >= -1w ORDER BY resolved DESC, created ASC"""

// Get user and services
def user = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def searchService = ComponentAccessor.getComponent(SearchService)
def mailServer = ComponentAccessor.getMailServerManager().defaultSMTPMailServer

if (!mailServer) {
log.warn("No server SMTP configured!")
return
}

// Validate JQL
def parseResult = searchService.parseQuery(user, jqlQuery)
if (!parseResult.isValid()) {
log.error("Invalid JQL: ${jqlQuery}")
return
}

// Execute JQL
def searchResult = searchService.search(user, parseResult.getQuery(), PagerFilter.getUnlimitedFilter())
def issues = searchResult.getResults()

if (issues.isEmpty()) {
log.warn("No issues found in JQL.")
return "No issues found."
}

// Generate content in CSV
def csvContent = new StringBuilder("Issue Key,Summary,Status,Created,Resolved\n")
issues.each { issue ->
csvContent.append("${issue.key},\"${issue.summary}\",${issue.status.name},${issue.created},${issue.resolutionDate}\n")
}

// Save file CSV
def fileName = "JQL_Result_${System.currentTimeMillis()}.csv"
def file = new File("/tmp/${fileName}")

try {
file.withWriter("UTF-8") { writer ->
writer.write(csvContent.toString())
}

// Send email with Attachment
createMessage(recipientEmail, subject, emailBody, "${file}")
log.info("Mensage created")
} catch (Exception e) {
log.error("Error while try create email: ${e.message}", e)
return "Error: ${e.message}"
} finally {
file.delete()
log.info("File removed")
}

static createMessage(String to, String subject, String content, String filename) {
def mailServerManager = ComponentAccessor.mailServerManager
def mailServer = mailServerManager.defaultSMTPMailServer
def multipart = new MimeMultipart()
def body = new MimeBodyPart()
def mailAttachment = new MimeBodyPart()
def attachmentFile = new File(filename)

body.setContent(content, 'text/html; charset=utf-8')
mailAttachment.attachFile(attachmentFile)
multipart.addBodyPart(body)
multipart.addBodyPart(mailAttachment)

def email = new Email(to)
email.setSubject(subject)
email.setMultipart(multipart)
email.setMimeType('text/html')

def threadClassLoader = Thread.currentThread().contextClassLoader
Thread.currentThread().contextClassLoader = mailServer.class.classLoader
mailServer.send(email)
Thread.currentThread().contextClassLoader = threadClassLoader
}

return "Process done"

 

Let me know if this helped!

 

Best Regards

Erdem UÇAK January 29, 2025

Hey @Kawan Diogo I appriciate you for your support. These script is better for me :)

Only, I want to edit this script I added some field and I got it working.

thank you again

Like Kawan Diogo likes this
3 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 27, 2025

Hi @Erdem UÇAK , under what conditions do you wish to send this email? If you simply want to do this manually you can share the results of your filter. If you want to do this on a regular basis you can set up a subscription on the filter. The other  option is to consider automation to send an email with the results of your JQL filter. But in that case you need to define what the trigger is for your automation rule. Please note that the recipient of the email must have access to Jira to be able to see these issues. If I have missed the point of your question, please let me know.

Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 28, 2025

Hi @Erdem UÇAK , i believe you can achieve your goals if simply inserting a table with certain issue fields satisfies your requirements. I am mobile at the moment so cannot create an example for you. However, I found this previous post that illustrates what I am thinking. How-can-I-fix-HTML-and-add-to-automation-to-have-email-with-table 

0 votes
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 28, 2025

Hi @Erdem UÇAK!

We are the developers of the aforementioned Better Excel Automation app. Please open a support ticket for our team, and we will be there to help you!

Erdem UÇAK January 28, 2025

Hi @Aron Gombas _Midori_ 

thank you for response. I appriciate it. I had installed these app and restart it and when I use these app I saw a notice "you must install better excel exporter..." and better excel automation is free but better excel automation is not free. I want to use free tool or groovy script. Is there different idea

0 votes
Erdem UÇAK January 27, 2025

Hi @Jack Brickey thank you for your answer. I don't want use subscription on this filter. Actually, I want to use automation rule or scriptrunner. for example, firstly, in the scriptrunner or automation rule I will run JQL query and then It will export .xlsx format and It will send an e-mail. JQL query as below.

And, I don't want use different plug-in or tool. Is ıt possible?

 

project not in ("project1", "project2", "project3") AND status in (Resolved, Closed) AND createdDate >= -1w AND resolved >= -1w ORDER BY resolved DESC, created ASC

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events