Sunday, June 23, 2013

Google Spreadsheet Query BY keyword



I am having issues with referencing a particular column in a query function.

The project I am working on involves the creation of a room audit tool for easy data collection.  These rooms are grouped into different zones and different people are responsible for different zones.

Right now I have a spreadsheet set up so that form responses are automatically separated based off of zones by using the query function.

This form creates many columns in the responses and when I try to reference column BY in Sheet 'Form Responses' in order to put into the sheet 'Zone B,' the query function breaks because 'by' is a word and I can't get it to read the column in any other way.

Here is a link to the screenshot of my spreadsheet:

Here is the formula I was using in cell A1 of sheet Zone B: =sort(query('Form Responses'!A1:FO,"select A,B,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BZ,CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO where C='B'",1),1,false)

I need to be able to select by zone and I need to be able to have it sort so that it can have the most recent submissions remain at the top.

Thank you for your time.


'BY' is the keyword of query formula so it is having this issue...

You can try:
=sort(query(ArrayFormula('Form Responses'!A1:FO),"select Col1,Col2,Col46,Col47 where Col3='B'",1),1,false)

Here in the above query you have to use formula Arrayformula in your query's data and then instead of refering columns by A,B,C.. you can refer them by Col1,Col2,Col3 and so on...

I hope this helps you..

No comments:

Post a Comment