If you ever need to import data – live – from Jira to a google spreadsheet, this might help you.
- 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.
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.
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.
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.
Did you save the filter before you exported to .xml?