Use groovy script to transform a grid into subtasks

Introduction

Use the table grid editor to create a list of subtasks

This is a more advanced example how you can create a list of subtasks based on the information collected in the grid.

Then by transitioning the 'Create Subtasks' workflow action, all rows in the action grid with status 'Open' will be transformed into a subtask.

Solution

Setting up the script

Some assumptions

You did setup a standard action grid (like the one which is set by default)
You have configured a subtask issuetype 'action'
You installed the Script Runner
You configured a workflow transition - 'Create subtasks' with a post function 'Script postfunction' pointing to a groovy script on the filesystem

Create subtasks from actions

/**
 * Import some packages from jira
 *
 */
import com.atlassian.crowd.embedded.api.User
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.config.SubTaskManager
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.IssueFactory
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.issue.fields.config.manager.IssueTypeSchemeManager
import com.atlassian.jira.issue.issuetype.IssueType
import com.atlassian.jira.security.JiraAuthenticationContext
import com.atlassian.jira.user.util.UserUtil
import org.apache.log4j.Logger;
import groovy.sql.Sql;
import groovy.sql.GroovyRowResult;
import java.sql.SQLException;
/**
 * Setup a logger such that errors and such get logged properly
 */
Logger log = Logger.getLogger("com.idalko.scripts");
/**
 * Some parameters for the script
 */
String dbConnectionURL = "jdbc:mysql://localhost:3306/defaultj610?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=storage_engine=InnoDB";
String dbUsername = "root";
String dbPasswd = "root";
String dbDriver = "com.mysql.jdbc.Driver";
// the gridtable is the table as created in the database
String gridTable = "actions_c11001";
// the ISSUETYPENAME is the name of the subtask that needs to be created
String ISSUETYPENAME = "action";
// Setup a connection to the database
Sql connection;
try {
    connection = Sql.newInstance( dbConnectionURL, dbUsername, dbPasswd, dbDriver );
} catch (SQLException e) {
    log.error( "Unknown (Issue connecting to the database: ${e.getMessage()}");
    return;
}
// Collect all relevant data
List<GroovyRowResult> result = null;
try {
    String query = "select * from ${gridTable} where issueId = ${issue.id} and istatus = 'O'";
    log.debug("Running query " + query);
    result = connection.rows(query);
} catch (SQLException e) {
    connection.close();
    return "Issue retrieving data from the database: " + e.getMessage();;
}
if (result == null) {
    log.debug("No rows returned ...");
    connection.close();
    return;
}
log.debug("Select returned:\n" + result.join("\n"));

// Setup the environment to create subtasks
JiraAuthenticationContext authenticationContext = ComponentAccessor.getJiraAuthenticationContext()
IssueFactory issueFactory = ComponentAccessor.getIssueFactory();
UserUtil userUtil = ComponentAccessor.getUserUtil();
IssueManager issueManager = ComponentAccessor.getIssueManager();
SubTaskManager subTaskManager = ComponentAccessor.getSubTaskManager();
IssueTypeSchemeManager issueTypeSchemeManager = ComponentAccessor.getIssueTypeSchemeManager();
// Make sure that we have an issueType which exists
IssueType subtaskIssueType = issueTypeSchemeManager.getSubTaskIssueTypesForProject(issue.getProjectObject()).findResult {it.getName() == ISSUETYPENAME ? it : null};
if (subtaskIssueType == null) {
    log.error("Issuetype with name ${ISSUETYPENAME} not found");
    return;
}

// For all the rows in the resulting set
for (GroovyRowResult row: result) {
    String summary = row.get("usummary");
    String assigneeKey = row.get("iassignee");
    /*
     * Prepare information
     */
    User reporter = authenticationContext.getUser().getDirectoryUser();
    if (reporter == null) {
        log.error("Cannot resolve reporter - are you logged in ?");
        return;
    }
    User targetUser = null;
    if (userUtil.getUserByKey(assigneeKey) != null) {
        targetUser = userUtil.getUserByKey(assigneeKey).getDirectoryUser();
    }
    /*
     * Setup subtask
     */
    MutableIssue subtaskIssueObject = issueFactory.getIssue();
    subtaskIssueObject.setProjectObject(issue.getProjectObject());
    subtaskIssueObject.setIssueTypeObject(subtaskIssueType)
    subtaskIssueObject.setSummary(summary);
    if (targetUser) {
        subtaskIssueObject.setAssignee(targetUser);
    }
    subtaskIssueObject.setReporter(reporter);

    /*
     * Commit to the database
     */
    Map params = new HashMap();
    params.put("issue", subtaskIssueObject);
    Issue storeIssue = issueManager.createIssueObject(reporter, params);
    subTaskManager.createSubTaskIssueLink(issue, storeIssue, reporter);
}

connection.close();

After the Java API was released in v 1.18.0 you can use it to avoid direct connection to the database.

Create Sub-tasks using Java API

import com.atlassian.crowd.embedded.api.User
import com.atlassian.jira.bc.issue.IssueService
import com.atlassian.jira.bc.issue.IssueService.CreateValidationResult
import com.atlassian.jira.bc.issue.IssueService.IssueResult
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.config.SubTaskManager
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.IssueInputParameters
import com.atlassian.jira.issue.comments.CommentManager
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.fields.config.manager.IssueTypeSchemeManager
import com.atlassian.jira.issue.issuetype.IssueType
import com.atlassian.jira.project.Project
import com.atlassian.jira.security.JiraAuthenticationContext
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.plugin.PluginAccessor
import org.apache.log4j.Logger;

// constants
String tgeFieldName = "TGE_TEST";
String targetIssueTypeName = "Sub-task";
String summaryColumnName = "isummary";
String descriptionColumnName = "isummary";

// add a comment
// String commentColumnName = "com";

// copy some columns as custom field values
/*
String currencyColumnName = "cur";          String currencyCustomFieldName = "Currency";
String quotationColumnName = "quant";       String quotationCustomFieldName = "Quotation";
String priceColumnName = "unitprice";       String priceCustomFieldName = "Price";
*/

// set up logger
Logger log = Logger.getLogger("com.idalko.scripts");

// find tge field
PluginAccessor pluginAccessor = ComponentAccessor.getPluginAccessor();
CustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager();
CustomField tgeCustomField = customFieldManager.getCustomFieldObjectByName(tgeFieldName);
if (tgeCustomField == null) {
    log.error("TGE field with name " + tgeFieldName + " not found");
    return;
}
Long tgeCustomFieldId = tgeCustomField.getIdAsLong();

// find current user
JiraAuthenticationContext jiraAuthenticationContext = ComponentAccessor.getOSGiComponentInstanceOfType(JiraAuthenticationContext.class);
Object userObject = jiraAuthenticationContext.getLoggedInUser();
User user = userObject instanceof ApplicationUser ? ((ApplicationUser) userObject).getDirectoryUser() : (User) userObject;

// read the grid data
Class dataManagerClass = pluginAccessor.getClassLoader().findClass("com.idalko.jira.plugins.igrid.api.data.TGEGridTableDataManager");
def tgeGridDataManager = ComponentAccessor.getOSGiComponentInstanceOfType(dataManagerClass);
Long issueId = issue.getId();
List<Map<String, Object>> rows = null;
try {
    def callResult = tgeGridDataManager.readGridData(issueId, tgeCustomFieldId, null, null, 0, 10, user);
    rows = callResult.getValues();
    log.debug(rows.size() + " rows were read from the TGE grid with id " + tgeCustomFieldId);
    log.debug("TGE grid content: " + rows);
} catch (Exception e) {
    log.error("Grid data cannot be read from the the TGE grid with id " + tgeCustomFieldId + ". Cause: " + e.getMessage());
    return;
}

// find target issue type
IssueTypeSchemeManager issueTypeSchemeManager = ComponentAccessor.getIssueTypeSchemeManager();
Project project = issue.getProjectObject();
Collection<IssueType> issueTypes = issueTypeSchemeManager.getSubTaskIssueTypesForProject(project);
IssueType targetIssueType = null;
for (IssueType issueType : issueTypes) {
    String issueTypeName = issueType.getName();
    if (targetIssueTypeName.equals(issueTypeName)) {
        targetIssueType = issueType;
        break;
    }
}
if (targetIssueType == null) {
    log.error("Issuetype with name " + targetIssueTypeName + " not found");
    return;
}

// find target custom fields (if it is needed)
/*
CustomField currencyCustomField = customFieldManager.getCustomFieldObjectByName(currencyCustomFieldName);
CustomField quotationCustomField = customFieldManager.getCustomFieldObjectByName(quotationCustomFieldName);
CustomField priceCustomField = customFieldManager.getCustomFieldObjectByName(priceCustomFieldName);
*/

// create sub-tasks
IssueService issueService = ComponentAccessor.getIssueService();
SubTaskManager subTaskManager = ComponentAccessor.getSubTaskManager();
// CommentManager commentManager = ComponentAccessor.getCommentManager();
for (Map<String, Object> row : rows) {
    String summary = (String) row.get(summaryColumnName);
    String description = (String) row.get(descriptionColumnName);
    // String comment = (String) row.get(commentColumnName);
    // Object currency = (String) row.get(currencyColumnName);
    // Object quotation = (Double) row.get(quotationColumnName);
    // Object price = (Double) row.get(priceColumnName);

    IssueInputParameters inputParameters = issueService.newIssueInputParameters();
    inputParameters.setProjectId(project.getId());
    inputParameters.setSummary(summary);
    inputParameters.setIssueTypeId(targetIssueType.getId());
    inputParameters.setAssigneeId(user.getName());
    inputParameters.setReporterId(user.getName());
    inputParameters.setDescription(description);
    // inputParameters.addCustomFieldValue(currencyCustomField.getIdAsLong(), String.valueOf(currency));
    // inputParameters.addCustomFieldValue(quotationCustomField.getIdAsLong(), String.valueOf(quotation));
    // inputParameters.addCustomFieldValue(priceCustomField.getIdAsLong(), String.valueOf(price));
    inputParameters.setApplyDefaultValuesWhenParameterNotProvided(true);

    CreateValidationResult createSubTaskValidationResult = issueService.validateSubTaskCreate(userObject, issueId, inputParameters);
    if (!createSubTaskValidationResult.isValid()) {
        Collection<String> errors = createSubTaskValidationResult.getErrorCollection().getErrorMessages();
        log.error("Sub-task cannot be created due to following reasons: " + errors);
        return;
    } else {
        IssueResult issueResult = issueService.create(userObject, createSubTaskValidationResult);
        Issue subTask = issueResult.getIssue();
        subTaskManager.createSubTaskIssueLink(issue, subTask, userObject);
        log.debug("Sub-task " + subTask + " for issue " + issue + " is created");

        // add comment
        // commentManager.create(subTask, userObject, null, comment, null, null, new Date(), null, true);
        // log.debug("Comment " + comment + " is added to sub-task " + subTask);
    }
}