If you ever need to import data – live – from Jira to a google spreadsheet, this might help you.

You’ll need:

• The Url of your Jira server (I’ve only tested with ondemand)
• Username and password to an account with rights to export xml
• This list of jira fieldnames (not the same as jql)

Now open a spreadsheet or create a new one.

Now insert something like this.

=ImportXML(“https://yourserver.atlassian.net/sr/jira.issueviews:searchrequest-xml/temp/SearchRequest.xml?

jqlQuery=project+in+%28%22Projectname%22%29+ORDER+BY+Rank+ASC&

tempMax=1000

&field=summary&field=timeoriginalestimate&field=timespent

&os_username=alice&os_password=bob“, “//item”)

You need to change the text in red to something that will work for you. And make it all one line – I just broke it down to make it easier to read.

As you can see I’ve listed the fields I need – you don’t need to do this if you don’t mind getting a lot of data. Remember to use the fieldliste above – not all fieldsnames match their jql names.

The last part of the statement – the xpath – tells google doc, which xml elements to extract. It’s actually a bit to simple her, as the timeoriginalestimate and timespent fields have a “seconds” attribute, that may be easier to work with the the text “2.54h” text returned by the field it self.

Change the xpath to something like “//item | //item/timespent/@seconds | //item/timeoriginalestimate/@seconds” to get the attributes (which is a bit useless for what I want as google places them on separate rows and only does it if there actually is an attribute – which makes it hard to know which value was returned if only one fo them exist in the xml – I’ll note it here what I end up with). The alternativ is something =if(C9<>””, value(left(C9,LEN(C9)-1)), “”) on the hour columns.

Have fun!