In this page

Extending the field-helper-tool.groovy script

field-helper-tool.groovy is a customizable Groovy script that is part of the Better Excel Exporter app and that gives easy access to Jira data for the Excel templates. The script file is well-structured and easy to read, and can be customized just like any other Groovy script used by the app.

This article guides you through the most typical customization and extension tasks.

Custom fields

Adding support for new custom field types

The most typical extension you want to make in field-helper-tool.groovy is adding support for new custom field types. Although we support many custom fields out of the box, you may be using 3rd party apps that are not among the official integrations or you may be using your own custom built apps.

Assuming you have basic Java and Groovy skills, exporting custom fields is a fairly straightforward thing to do. For the implementation work, you will need to find out these for each new custom field type:

  1. What is the type key of the custom field?
  2. How to get the value from the value object or the custom field object?
  3. What is the best cell type for the value: text, number or date?
  4. If this is number or date type data, what is the best cell format?
Finding the custom field type key

Each custom field type is identified by a unique textual key, like com.pyxis.greenhopper.jira:gh-sprint.

How to find this?

  1. Go to an issue that has a custom field of this type.
  2. Export the issue to XML by clicking ExportXML.
  3. In the resulted XML document, look for the custom field by its name, and the field type key will be available in the corresponding key attribute:
    <customfield id="customfield_11103" key="com.tempoplugin.tempo-accounts:accounts.customfield">
    <customfieldname>Account</customfieldname>
Returning the custom field value

For this, you should understand how the custom field returns its value, and how can you transform that (if necessary) to an Excel field value. For text fields, this can be as simple as returning the unchanged String object. For some more complicated field, the value itself may be just an ID, that you need to transform to a representation that the users expect to see. For example, you may want to transform a sprint ID to the sprint's name and return that.

This step depends on the field implementation, therefore it is useful to have the Java source of the custom field type class, of the value object class, etc. In most cases you just need call getters on those. As always, you can use the implementation of the already supported custom fields as samples.

Add the logic that returns the field value as a new case in the getCustomFieldValue() method:

case "com.acme:mycustomfield": // <- your custom field type key comes here
	(value != null) ? value.toString() : null // <- your logic comes here
	break

Pro tip: in certain cases, you may not have the Java source of the custom field, but you need to support that any way. You can resort to reverse engineering, by extracting the app JAR, checking the atlassian-plugin.xml file for the custom field type configurations and decompile the implementation classes with JD-GUI.

Returning the cell type

Add the logic that returns the cell's format to the completely trivial getCustomFieldCellType() method. You can use one of "text", "number" or "date", whichever represent your data the best. Please note that the default format is "text", therefore if your data is of "text" type, then you can completely skip this step and leave the method unchanged.

Returning the cell format

If your data is "number" or "date" type, you can specify the exact cell format to be used with them. For example, a number representing a currency value may be best used with a 2 decimal digit format that also incorporates the currency sign, while a "days since" kind of value may be just an unsigned integer.

For this, you have to return the Excel cell format strings from the trivial getCustomFieldFormat() method.

Calculated fields

Customizing calculated fields

Recall that there are three distinct field type categories handled by the app, each one having three different rendering properties. These different cases are mapped one by one to 3 * 3 = 9 methods in field-helper-tool.groovy. We now focus only on the getCalculatedXxx() methods:

getCalculatedFieldValue()
getCalculatedFieldCellType()
getCalculatedFieldFormat()

Let's see a concrete example how to modify a calculated field!

Customizing the format of the comments' submission dates is a common need for users, and doing this by altering field-helper-tool.groovy is easy. Assume we want the year, month and day values to appear in their respective order and in all numeric.

Since we need to change the formatting, it's straightforward to look for the getCalculatedFieldFormat method:

private def getCalculatedFieldFormat(customField) {
	switch(customField.name) {
		case EXCEL_FIRST_COMMENT_DATE_CALCULATED_FIELD_NAME:
		case EXCEL_LAST_COMMENT_DATE_CALCULATED_FIELD_NAME:
			"dd/mm/yyyy hh:mm" // <- change this format pattern!
			break

		default:
			// no custom format
			null
	}
}

Simply change the format string, upload the modified Groovy file, and generate another export:

case EXCEL_FIRST_COMMENT_DATE_CALCULATED_FIELD_NAME:
case EXCEL_LAST_COMMENT_DATE_CALCULATED_FIELD_NAME:
	"yyyy/mmm/dd hh:mm" // <- this is the new pattern
	break
Adding new calculated fields

Adding a calculated field is almost as easy as modifying an existing one. Just introduce the new field type in the beginning of the script, then modify the three methods that implmenet the calculated fields (see the previous section).

Let's dive in right now with a concrete example.

We are going to implement a new custom field that shows whether the issue's assignee is an active Jira user account, using red/green text coloring in the Excel cells for better visual indication. To demonstrate all customization features, we will:

  1. Return a numeric 0 or 1 value, based on whether the assignee is active or not
  2. Use Excel custom number format codes to turn these numeric values into colored "active"/"inactive" texts

First, add a new constant for the name of the new calculated field near the beginning of the FieldHelperTool class. We will call this USER_IS_ACTIVE_FIELD_NAME in our example. Also make sure you add the new constant to the CALCULATED_FIELD_NAMES list right below the declaration.

...
private static EXCEL_LAST_COMMENT_DATE_CALCULATED_FIELD_NAME = "Excel last comment date"
private static USER_IS_ACTIVE_FIELD_NAME = "User is active" // <- our new calculated field name

private static CALCULATED_FIELD_NAMES = [ EXCEL_COMMENTS_CALCULATED_FIELD_NAME,
	EXCEL_FIRST_COMMENT_CALCULATED_FIELD_NAME, EXCEL_FIRST_COMMENT_TEXT_CALCULATED_FIELD_NAME, EXCEL_FIRST_COMMENT_AUTHOR_CALCULATED_FIELD_NAME, EXCEL_FIRST_COMMENT_DATE_CALCULATED_FIELD_NAME,
	EXCEL_LAST_COMMENT_CALCULATED_FIELD_NAME, EXCEL_LAST_COMMENT_TEXT_CALCULATED_FIELD_NAME, EXCEL_LAST_COMMENT_AUTHOR_CALCULATED_FIELD_NAME, EXCEL_LAST_COMMENT_DATE_CALCULATED_FIELD_NAME,
	USER_IS_ACTIVE_FIELD_NAME ] // <- also added here

Then add a new "case" with the field name to getCalculatedFieldValue():

private def getCalculatedFieldValue(issue, customField) {
	// ...
	case USER_IS_ACTIVE_FIELD_NAME:
		issue.assignee.active ? 1 : 0
		break

Also add a new "case" to getCalculatedFieldCellType(), and specify that the cell has a numeric value (otherwise custom number format codes will not work):

private def getCalculatedFieldCellType(customField) {
	// ...
	case USER_IS_ACTIVE_FIELD_NAME:
		"number"
		break

Finally, add a new "case" to getCalculatedFieldFormat(), and use an Excel formatting expression that says: if the value is greater than zero, render the word "active" in green, otherwise render the word "inactive" in red (pretty smart, right?):

private def getCalculatedFieldFormat(customField) {
	// ...
	case USER_IS_ACTIVE_FIELD_NAME:
		'[Green][>0]"active";[Red]"inactive"'
		break

To test your newly added calculated field:

  1. Create a new Jira custom field (any type) with the exact name User is active.
  2. Add this custom field to the columns in the Issue Navigator, and export your issue list to Excel.
  3. Your column should be filled with green "active" and red "inactive" values!