How to maintain the original header name when performing Google Sheets Query

0
votes

Hi I have this working query that loops through an arbitrary range of data and produces the results I need:

=arrayformula(QUERY(Crew!A:DY,"SELECT E,A," & join(",", substitute("Count(`1`)", "1",substitute(address(1, column(Crew!F:DY), 4), "1", ""))) & "GROUP BY E,A", 1))

Unfortunately, this produces Columns that are labeled so:

count 18/12/2017 count 25/12/2017 count 01/01/2018

I need to force a display of the original column name, and if possible, the format, e.g. 18/12/2017 as this will allow me to perform further pivot or group by month type functions

I have experimented with different methods of adding a label at the end of the query, reduced the query to tests of the data without using the arrayformula, and searched through the queryLanguage Docs but all references seem to be related to applying a different text string rather than leaving the column header 'raw'

I suspect the main problem is my inexperience, I don't know the correct terms to search for? How do I achieve this? Cheers.

1
google-sheets
Ah what I actually do is fill different cells with functions i want e.g. cell F1 has the contents count(1), reference that cell and change out the value 1. The backticks make sure that columns BY and OR don't break the query. Sorry if that caused confusion. – cchriso

1 Answers

0
votes

You need to use label construction:

"select ... where ... group by ... label ..."

Reference:

https://developers.google.com/chart/interactive/docs/querylanguage#label

You may get labels text with the formula:

="label "&ArrayFormula(join(", ",substitute("count(`1`) ", "1",substitute(address(1, column(F1:G1), 4), "1", ""))&text(F1:G1," 'dd/mm/yyy'")))

change F1:G1 to your range.

The shorter version of the formula with Regex:

="label "&ArrayFormula(join(", ",REGEXREPLACE(ADDRESS(1,COLUMN(F1:G1),4),"(.*)\d+$","count(`$1`) "&TEXT(F1:G1,"'dd/mm/yyy'"))))