Jira vs Google Doc importXML

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!

3 thoughts on “Jira vs Google Doc importXML

  1. Jason

    I’m also trying to do exactly this but it isn’t working for me. The formula says the value is wrong and to check the URL. In JIRA I set up my filter, then view XML, I copy the URL that it displays and paste it into the formula (along with the credentials for logging in) then the Xpath. No matter what I do it doesn’t pull anything in. I can get importXML to work with other sites or XML files, just not with JIRA. Would love any further direction.

    Reply
  2. A different Jason

    I have the same problem, but I’m wondering if it’s related to Google not really having access to view the XML. Even though you are including the user/pass in the URL, I haven’t seen that actually work. Since I cannot access my JIRA instance outside of the network or VPN, GoogleDocs is not able to access it to import. I think it may be as simple as that.

    Reply

Leave a Reply