ExSil may be the perfect mashup ever

Ok, that was a bad attempt at a clever portmanteau. Bas attempt aside, when you combine SIL and Excel you do create a powerful combination. SIL (almost) always had the ability to work with CSV files which was almost good enough. However, now with the release of the SIL Excel Connecter everything has gotten even better. There are probably some very useful ways to combine SIL and Excel that you have never thought about. Don’t worry, I am here to lay awake in bed at night for you. Here are some of the awesome mashups I have dreamed up:l

Simple Reports/Exports

Maybe they don’t need to be so simple. However, I have made things like this using CSV files for a long time and now they can get even better with Excel. Some examples of reports/exports that I have made are:

SIL Runner dashboard gadget to export more than 1,000 issues out of Jira. The 1,000 issue export limit was added to Jira for a reason and some people are uncomfortable increasing this number globally. However, using a SIL Runner dashboard gadget you can give this power to some users and not others.

In the example below notice that users can enter any JQL query to export. They can also select from predefined column templates as well.

When the script is run the resulting Excel file gets emailed to the user for easy download. Another option would be to write the file to a ticket in a special project.

Shared Access and Control

The SIL Excel Connector was born from a user request to be able to read data from an Excel file. The company had a spreadsheet on a shared network drive that had corporate budget information. They wanted scripts running in Jira to be able to read this budget data directly from the shared file so when it was updated it would automatically update in Jira as well.

Pretty cool if you ask me.

Calculated Fields

Even with a new generation of business tools it is still pretty hard to beat excel when it comes to crunching some numbers. If you already have the answers in a complicated spread sheet wouldn’t it just be easier to get the calculations from their instead of reproducing all that logic in SIL?

Interdependent Custom Fields

This is one of my favorite tricks. one of the more popular ways to use Live Fields from Power Scripts or script driven custom fields from Power Custom Fields is to make them interdependent. For example, you select a product (or enter its weight) in one field. In the next field you see a list of shipping options where the price has been calculated from data from the first field. You use fields like this all the time on other websites and don’t even take notice. But why not do the same in Jira? The first time everyone writes a script for fields like this they do something like this:

//code for a price field

if(product == "Helicopter") {
  return "$100,000,000";
}

Imagine doing that for hundreds of values? There is a much simpler way of doing it and it is almost always overlooked. For example, lets pretend that we have 3 fields that will all be dependent on each other, Product, Color, and Price. Rather than making all this SIL code we could drive all 3 fields off of a CSV or Excel file. The data in that file would look like this:

Product	Color	Price

Product	Color	Price
Bike	Red	75
Bike	Blue	80
Bike	Green	60
Plane	Purple	500,000
Plane	Orange	475,000
Plane	Pink	750,000
Helicopter	Lime	100,000,500
Helicopter	Silver	100,000,750
Helicopter	Pearl	100,001,500

This is what the code would like like for a script controlled custom field:

struct _product {
  string product;
  string color;
  string price;
}

_product [] products = =readFromCSVFile("Products.csv", true); //read data from csv file

for(_product p in products) { //loop through all products
    if(p.product == productField) { //if product row matches product field data
        if(p.color == colorField) { //if color row matches product field data
            return p.price; //return the product price
        }
    }
}

While this may seem like a lot of code, consider this, it does not matter how many products are in the data with this type of code. It could be 10 or 1,000. The code stays the same!

Better Logging

I have written some pretty complex batch operations for people that required very, very good logging. I could have written directly to the Jira log file (like normal). However, since I was the one constantly looking into those log files for information I became frustrated with how hard it was to find the info. I soon started creating my own log files that were in Excel format. Using standard Excel filters I was quickly able to find the information I was looking for.

For example, some of the data I included was:

Time stamp
Pass/fail (true/false)
Project
Issue key
Error message
Field 1
Field 2
Field 3
…