We have a sheet which contains all calendar events:
eventId name startDate endDate guest
A Bob 10/7/2024 18:00 10/7/2024 19:00 bob@world.com
B Mike 10/9/2024 18:00 10/9/2024 19:00 mike@world.com
C Bob 10/9/2024 19:00 10/9/2024 20:00 bob@world.com
D Bob 10/9/2024 20:00 10/9/2024 21:00 bob@world.com
E Cancelled Mike 10/11/2024 16:00 10/11/2024 17:00 mike@world.com
F Other event 10/11/2024 16:00 10/11/2024 17:00
We have a pivot table that does what we need, but we need to replace it with a QUERY
function, so it can be populated automatically when the App Scripts run. This doesn't work for Pivot Tables unfortunately.
guest eventCount evenTitle
1 Other event
bob@world.com 3 Bob
mike@world.com 2 Mike, Cancelled Mike
We are able to reproduce the first two columns correctly:
=QUERY(CalendarEvents!A5:Z, "SELECT E, count(E) GROUP BY E",1)
However, we can't seem to get unique strings in the third column (eventTitle
). We were hoping this would work, but it doesn't:
=QUERY(CalendarEvents!A5:Z, "SELECT E, count(E), STRING_AGG(DISTINCT B, ', ') GROUP BY E",1)
Error: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 31. Was expecting one of:
Here is a test sheet.
Thank you for your help.
Here's a generalized approach which you may adapt accordingly:
=query({B6:B,E6:E,map(E6:E,lambda(Σ,textjoin(", ",1,unique(filter(B6:B,E6:E=Σ)))))},
"Select Col2,count(Col1),Col3 Where Col1!='' group by Col2,Col3 label count(Col1) ''",0)