Jira Cloud for Sheets - Adding A Calculated Field

[kateu]

Hi - I am using Jira Cloud for Sheets and refreshing the sheet daily. However, I would like to add a calculated field to the sheet without it getting deleted every time the sheet refreshes. Is there a way to do this? 

[Earl McCutcheon · Accepted solution]

Hello @kateu ,

When you do the Streight data import from the tool it will overwrite the tata on the sheet the data is imported into, so there are a few different approaches you can use here, the first one would be to use multiple sheets if you wanted to use the Direct data import, Sheet1 for the data pull and sheet2 with references to sheet1 to do all of your calculations and display data for reports.  This way the dynamic data from the import will live in a completely separate sheet then the reports for the data calculations.

EXE if you had sheet1 with all your data pulled in, and needed to do a sum of the number values in column E from sheet1 you could use the the following formula in a cell on sheet2 to do the summation, noting to step down to cell E2 as the starting point to avoide conflicts with the Header row:

=SUM(sheet1!E2:E)

Alternatively you can use the =jira() formula in a different section of the page, But lower down in the page and doing all your formulas at the top.  The sections below the rows with the =jira() formula will be overwritten, and the rows above will be left alone.  In the following Example my project is EXE in cell 1 and referencing A1 in the =jira() formula below, all i need to do to update the formula is edit cell A1 and then any formula added to rows 4 and above will not be impacted when the data is refreshed:

And you can take this a bit further and add in a data validations drop down list in cell A1 for a quick referance back and forth between various projects and all your formulas above the data pull as well for a rapid project update on live data, The following example in the GIF below being super basic layout (but you can make this as elaborate as needed) with a couple =countif() formulas to show how they update as the data refreshes that count total cells with a value and statuses that are = "backlog" and then the data validation set up on A1 with referance to the project names in cells J1 and J2 so that the data updates on the fly when selected:

And you can combine this as needed and do combinations of multi-sheet data pulls for different projects, or filters, and then referance and combine all the data  onto a separate sheet for the reporting portion.

Regards,
Earl

[kateu · Accepted solution]

Great! Thank you very much.