How to add story points and original estimates to a JQL query?

Question

Hi,

I created some JQL query, in which I would like to add for each issue another column:

For stories I'd like to add their story points and for subtasks their original estimates.

I could not find how to do it.

My current query is :

=JIRA("project = myProj AND assignee = 'myname' AND (type not in (Story) OR (labels in (TasksLess))) AND status not in (Done, Duplicate, Obsolete) order by sprint asc", "parent, issuekey, type, summary, status, sprint, fixVersion, created, updated, component", 0, 100)

and I'd like to have something like:

=JIRA("project = myProj AND assignee = 'myname' AND (type not in (Story) OR (labels in (TasksLess))) AND status not in (Done, Duplicate, Obsolete) order by sprint asc", "parent, issuekey, type, summary, status, sprint, fixVersion, created, updated, component, storyPoints, originalEstimate", 0, 100)

which doesn't work for me now.

Can you advise please?

Thanks.

Earl McCutcheon · Answer

Hello @tal-yechye , The Original estimate and Time-based fields (Time Spent, Original Estimate, Remaining Estimate) will export the data in seconds, so it is a constant value based on the lowest common interval metric that can be accurately measured or used in comparative math functions in exported data set. Google Sheets can do some duration conversions to make it more human-readable and a conversion technique that you can use to get this into a duration format is to add a new column that takes the initial Seconds value and divide it by 86400 (converts to seconds in a day decimal timecode used by google sheets for the duration conversion) then set the format to "Duration" (Format > Number > Duration) to display the time as HH:MM:SS with a formula like the following to only populate if the initial field has a value: =if(ISBLANK(),,/86400) EXE: You can also do a custom date and time format if you only wanted to show minutes, or hours and minutes with some examples shown in: * https://stackoverflow.com/questions/25710299/how-to-format-a-duration-as-hhmm-in-the-new-google-sheets Additional reference material for the formulas can be seen at: * ISBLANK * IF function * Date and Number Formats Regards, Earl

tal-yechye · Answer

Thank you @Earl McCutcheon .

"story points" really worked.

However, adding the originalEstimate field yielded with unreasonable values like: 14400, 28800, 57600, 115200 etc.

I guess you may find here the consistency in these numbers...  14400X1,14400X2,14400X4, 14400X8, an so on...

---------------------------------

Update:

I found the consistency rule!

14400 = 4h

28800 = 1d

57600 = 2d

115200 = 4d

But why?...

---------------------------------

The query ends with:

..., component, story points, originalEstimate", 0, 100)

Earl McCutcheon · Answer

Hello @tal-yechye ,

With the field references in Jira cloud for sheets, Looking at the query "storyPoints" needs a space so updating it to "story points" should correct this issue for story points.

Another thing that may be occurring with story points is if you are using a Team Managed Project, then the story points use a different project field called "Story Point estimate" and you need to use the custom field value for this as the common name of the field is not reconized.  To find the custom field value you can oprn the Jira cloud for sheets sidebar by going to "Extensions >> Jira Cloud For Sheets >> Open" and then switch to the "Fields" tab and select edit, search for the story point estimate field and the customfield_ID will be listed below the field name, like this:

However, looking at "originalEstimate" the format is correct and should be populating the estimate values for either the Company Managed or Team Managed projects.  and I did a quick test to verify and can confirm the formatting:

What are you seeing for the output on the original estimate field?

Regards,
Earl