In this page

Prerequisites

Before reading this article, it is recommended to understand the basics of Excel templates. Otherwise, this tutorial will be more difficult to follow without context.

Why scripting?

Better Excel Exporter uses actual Excel files (XLSX and XLSM) as spreadsheet templates. In the templates, you can access Jira domain objects (issues, users, versions, etc.), you can use expressions, tags, functions, formulas to get the final result. Although these tools are powerful at what they do, they do not offer a full-blown programming language. That may impose limitations when implementing more complex Excel spreadsheet exports.

In many cases, this limitation is not a real problem, as your Excel spreadsheet exports may not require any complex logic. You just want to display field values with expressions, make trivial if-then switches with tags. If the Excel features like formulas are sufficient to do calculations from the exported data, then you should not complicate your life with scripting.

In other situations, the requirements for the logic that needs to be built into your templates are more complex. To implement these, tags and expressions alone may not be enough, you will need to do some scripting.

Don't worry, scripting is easy and it opens new horizons for your Excel spreadsheets!

When to use scripting?

Some use case examples that you can implement only with scripting:

  • Integrate with external resources. (Ex: integrate vendor information into your quotes queried from an external CRM database or an external webservice.)
  • Access Jira internals. (Ex: execute a secondary saved filter to collect more data.)
  • Implement data processing algorithms using advanced data structures. (Ex: build dependency tables for traceability matrixes.)

What is Groovy?

Groovy is the primary scripting language supported by the Better Excel Exporter for Jira. Groovy is the de-facto standard agile scripting language for the Java platform, the same platform on which Jira runs.

What are the advantages of Groovy, compared to other scripting languages?

  • It is very easy to learn and use.
  • It is already known for Jira users, as many other Jira apps use Groovy to implement custom logic.
  • It beautifully integrates with Jira internals.
  • There are lots of sample code, documentation and answers available on the web.
  • It is mature and proven, having been used in mission critical apps at large organizations for years.

The basics of Groovy can be learnt literally in hours, assuming that you have some background in a modern programming language, like Java, Javascript or C++.

Useful resources:

Writing Groovy scripts

Your first script in 2 minutes

Here is the good old Hello world example, implemented with Groovy for the Better Excel Exporter for Jira.

First, save your logic to a Groovy script file hello-world.groovy:

// hello-world.groovy

helloWorld = new HelloWorldTool()

class HelloWorldTool {
	def say() {
		"Hello world!"
	}
}

Then, execute this in your template hello-world.xlsx:

## hello-world-fo.xlsx

## execute the script with the <mt:execute> tag
| <mt:execute script="hello-world.groovy"/> |

## after executing the script, the object "$helloWorld" is available
## let's call a method and put the greeting text to a cell
| ${helloWorld.say()}                       |

Tadaam! That's it. Now you have the text generated by the Groovy code in the Excel.

Tip: it is usually a good idea to follow the naming convention used above. If your template is an implementation of "my document type", then save the template to my-document-type.xlsx and the script to my-document-type.groovy. It helps to see what files belong together.

Passing objects from templates to scripts

Besides the basics of script execution, it is important to learn how to share data and pass objects between templates and scripts.

Scripts have a dead-simple way to access the context objects used in templates. There is an important aspect of script execution, which is albeit transparent, yet important to understand. Before execution, each script is converted to an actual Groovy class in the background. (Our script hello-world.groovy will be converted to the class named hello-world, for instance.) Then, the context objects will be available as properties of that class! As the generated class is the "outermost" class in the script, its properties appear like "global variables" (more on this later). Simple, right?

Here is an example. You probably know that the currently signed-in Jira user is available as $user in the template code. At the same time, this is also available as the object user in Groovy!

// hello-world.groovy

helloUser = new HelloUserTool(user) // "user" is available from the context

class HelloUserTool {
	def user

	HelloUserTool(user) {
		this.user = user // store the argument for later use
	}

	def say() {
		"Hello ${user.displayName}!" // use a property
	}

	def say2(issues) {
		"Hello ${user.displayName}! You have ${issues.size()} issues." // use a property and a method argument
	}
}

Let's greet him:

## hello-world.xlsx

| ${helloUser.say()} |

You can also pass arguments to the Groovy methods with ease:

## hello-world.xlsx

| ${helloUser.say2(issues)} |

Final note: although from the above code it may feel like as if we had a global variable "user", this is not true. In fact, there is no such thing like "global" in Groovy! Read this article to avoid surprises.

Passing objects from scripts to templates

The recipe is simple: all so-called "binding variables" created in Groovy will be automatically available in the templates.

What is a binding variable? When it is not defined, it is in the binding, thus it will be available in templates, too.

bindingVar = "I am a binding variable" // will be available in the template
String localVar = "I am a local variable" // it will *not* be available in the template

Therefore, we recommend the following simple scheme:

  1. Implement your logic in lightweight Groovy classes
  2. Instantiating them as binding variables in Groovy
  3. Call their methods in the template
You may also want to follow the naming convention of calling your tool classes SomethingTool and instantiating them called something, just as we did in the examples.

Good practices

  1. Separation of concerns: clearly separate visuals and logic. Use tags, expressions and Excel features for iterating, trivial if-then's, formatting, and use Groovy for implementing complex logic. Not vice versa!
  2. Follow the naming conventions suggested in this article.

Advanced topics

Logging from scripts

Logging from a script can be useful in a number of cases, like providing debug information in the Jira log or signaling exceptional conditions with warnings. In order to write to the Jira log, you have to use log4j, the logging library also used by Jira itself.

Steps:

  1. Import the Logger class (in the top of the Groovy script file):
    import org.apache.log4j.Logger
  2. Create a Logger object in your class:
    public class MyClass {
    	def log = Logger.getLogger(this.getClass())
    
    	// ...
  3. After these, you can write to the Jira system log like this:
    // this goes to the Jira system log
    log.error("User not found!")
    Please note that when your script writes to the Jira log, the log lines can be filtered out or formatted by the log4j configuration, like any "regular" log line.

As a low-cost alternative, you can also write directly to the system console:

// this goes to the System (java) console
System.out.println("Hello console")

This trick should only be used for quick verifications, if you have access to the console (typically in development environments).

Unit testing scripts

Writing unit testing in Groovy is easy. For practical reasons, we recommend putting your unit test(s) into the file of the tested Groovy class, unless that grows inconveniently large. This helps to keep them neatly packaged together.

Here is a simple Groovy tool that counts the resolved issues in the passed collection:

resolvedCounter = new ResolvedCounterTool()

public class ResolvedCounterTool {
	public long countResolved(issues) {
		Closure query = { it.resolutionDate != null }
		return issues.findAll(query).size()
	}
}

Now, bring this script to your favorite IDE, and add a beautifully simple test case method:

public void testCountResolvedTasks() {
	def issues = [ [:], [ resolutionDate: new Date() ], [:] ] as Set
	def result = countResolved(issues)
	assert result == 1
}

You can run the test trivially:

resolvedCounter = new ResolvedCounterTool()
resolvedCounter.testCountResolvedTasks()

When your tests are running fine, comment out the invocation of the tests, and deploy the script back to the server.

You may be curious, what happens when running tests in Jira? That's also doable, with one important remark.

Technically speaking, you could run tests before each export. Test failures will be written to the Jira log, and that's cool. For example, if you change the assert to this:

assert result == 2

you will see the following easy-to-read error in the Jira log:

Assertion failed:

assert result == 2
	|      |
	1      false

The problem is that failed tests will also make the export fail. (The assert will terminate the execution of the script, and the final PDF document cannot be rendered.) As the test failure details only appear in the Jira log, the users not looking at the log will only see a broken document with a cryptic error message.

Therefore, running Groovy tests in Jira is recommended only for development purposes.

Debugging scripts in Jira

You can efficiently develop simple scripts solely in the app's built-in editor, and use logging to understand its behaviour.

When things get more complicated, you can develop, test and debug scripts outside Jira, using the testing approach discussed above in your favorite IDE.

When deploying your work to production, logging should be your primary tool to diagnose problems. Additionally, this is also possible to debug the scripts within Jira in a rather tricky way! The idea is to launch Tomcat with JPDA debugging enabled, connect to Tomcat from your IDE (debugger) and open the script file in the IDE. The debugger will be intelligent enough to show you the script (that is being actually executed in the server) in the editor, and allow you to step over that!

Steps:

  1. First start Jira with the remote debugging enabled, typically with this command:
    JIRA_HOME_DIR/bin/catalina.sh jpda start
  2. Connect to the Jira with your remote debugger, as explained here.
  3. Open up the Groovy file in your IDE, and set a breakpoint in the code.
  4. Start an export which uses this Groovy script, and the debugger will stop at the breakpoint! (This is pretty crazy, right?)

Please note that for this to work, the Groovy file must be exactly the same in Jira and in your IDE, otherwise line number will not match.

If you are a purist (or don't have the time), you can actually do all your work with a simple text editor!

If you would like to enjoy syntax highlighting, code-completion and so, we recommend to use the Eclipse IDE (a great general purpose development environment) combined with the following apps:

  1. Groovy-Eclipse to edit Groovy scripts (*.groovy).

Next step

Display Excel charts to easily visualize your Jira data.

Questions?

Ask us any time.