You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I'm using Google Sheet integration to export raw data and integrate with our BI (Data Studio).
I have a problem with two fields of service desk:
Both are exported as "5h 20m" (text format) but I'd like to export them in number format, e.g. in minutes.
Does anyone a similar issue and, most important, a solution? :=)
Thanks in advance!
PD: I've installed the time to SLQ plugin, but I can't add the information to my Google Sheet downloadable fields.
Just for helping another person. Finally I solved the problem using Google Sheets formulas.
That's what I did:
[Remember: my tab "Raw data" ends on column O)
h1 t response | h2 t resp. | m t resp. | t resp. m | h1 t resol. | h2 t resol. | m t resol. | t resol. m
[All formulas are in Spanish version. English version has "," rather than ";" and function names are in English]
On S2 I put this formula
I did the same on T2 and W2
Finally I got the number of minutes of my time to first responde and time to solve on cells S2 and W2
With this number I've could create my Data Studio report.
Thanks for you answers and regards!
I know I'm digging up an old thread, but I hit the same issue recently and used the following to translate the data in Excel. Hopefully it helps someone.
=IF(ISNUMBER(VALUE(A1)), A1, IF(ISNUMBER(SEARCH("h", A1)), LEFT(A1, FIND("h", A1) - 1) * 60, 0) + IF(ISNUMBER(SEARCH("m", A1)), IF(ISNUMBER(SEARCH("h", A1)), MID(A1, FIND(" ", A1) + 1, FIND("m", A1) - FIND(" ", A1) - 1), LEFT(A1, LEN(A1) - 1)), 0))
nb: You'll need to update A1 to be whatever cell you want to translate.
For my reporting I've got the raw data from Jira export on one Excel sheet and then use that formula on another to translate the values.
It's working pretty well for me so far and means the amount of extra col's is limited to the minimum.
Hi @Marcelo Gallego ,
Thanks for posting in community.
Could you please search like this in Marketplace - https://marketplace.atlassian.com/search?hosting=cloud&query=Excel%20Report ?
Does this help solving your issue - https://marketplace.atlassian.com/apps/1212073/exporter-export-issues-to-excel-csv-pdf?hosting=cloud&tab=overview ?
Note - I am not part of any of these marketplace products and this response is not part of any paid promotion.
thanks for your response, but what I'm looking for is something like "Jira Cloud for Sheets add-on" or just a way to edit the format of some Jira Fields.
I want to keep using Data Studio + Jira Cloud for Sheets because I dominate them and are extremely useful for me.
Thanks again for your reply and regards.