How to make a different view of rough data from for example a Google Form responses sheet?


Use a combination of the function IMPORTRANGE which output is the input for the QUERY function. Because of this combination you will have to use 'Col1' to indentify the first Coloumn instead of using letters like in Excel.

Also a special construct is used to refer to data within the sheet to base the selection on. In field $C$5 there might be a selection box to select certain values to base the query on.

=QUERY(IMPORTRANGE($B$3; "Form Responses!B1:M100"); "Select Col1, Col5, Col9 where Col9 contains '"&$C$5&"' order by Col1";0)

In field $B$3 the (long)key of the sheet you want to retrieve the data from is stored. In field $C$5 some text is stored which you can used to select the datarows you want to show.

 

Another example in which two other functions are used: determining the average value and replacing the label text with values from the current sheet:

=QUERY(IMPORTRANGE($G$2;"Form Responses!D1:AD100");"select Col1, avg(Col15), avg(Col19), avg(Col20), avg(Col21) where Col1<>'' group by Col1 label avg(Col15) '"&$B$7&"', avg(Col19) '"&$C$7&"' , avg(Col20) '"&$D$7&"', avg(Col21) '"&$E$7&"'";1)

 

And an example with more than one selection clause:

=QUERY(IMPORTRANGE($B$3; "Form Responses!B1:M100");"Select Col1, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col2, Col3, Col4 where Col1<>'' and Col12 = 'Ja' and Col6 contains '"&$C$4&"' and Col7 contains '"&$D$4&"'";1)

Labels: Excel, Google, Query, Sheet
Laatst bijgewerkt:
2016-02-16 12:21
Auteur:
Harry van der Pol
Revisie:
1.3
Gemiddelde beoordeling:0 (0 Stemmen)

U kunt commentaar op deze vraag geven

Chuck Norris has counted to infinity. Twice.