I have a Property Maintenance app I created which has a table containing hours worked in Duration (hh:mm) format, e.g. 02:30.
I am now wanting to create a chart off this table, grouping the job and summing hours per job. After some research, I understand I need to do this in Google Sheet via the query() function, then present the data to Appsheet as read only data.
However, the sum() function in Google Sheet gives an error as the field format is not a number, so I’ve figured out the query after some time which looks like this:
“select Col1, sum(Col2) group by Col1”
Just taking one line of output I see my 10:45 (it’s a large garden!) is converted to a numeric as a fraction of a day.
Dig garden 0.4479166667
I’m now wanting to convert this back to a hh:mm format, and so far I have this formula where column B in the above spreadsheet output is the 0.4479166667 value.
which now gives me my 10:45 answer.
I’m wondering how I put this concatenate in the above ArrayFormula(query(…) in Google Sheet.
Just wondering if anyone else has done this before, and if there is an easier way?!
I’m also presuming the AppSheet Chart will also like the hh:mi data …
Thanks in advance.