Hi!
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.
Hi!
Just for helping another person. Finally I solved the problem using Google Sheets formulas.
That's what I did:
ARRAYFORMULA('Raw Data'!A1:O2000)
[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
SI.ERROR(SPLIT(MINUSC(K2);"qwertyuiopasdfghjklzxcvbnm`=[]\;',./!@#$%^&*()");0)
[All formulas are in Spanish version. English version has "," rather than ";" and function names are in English]
On S2 I put this formula
SI(R2<>0;R2*60+P2+Q2;SI(Q2<>0;P2*60+Q2;P2*60))
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Cheers
Suvradip
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Paul,
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi!
Unfortunately We're on a cloud version.
Do you know any alternative for cloud?
Regards!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.