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.