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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.