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

~ 0 min
2016-02-16 12:21

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)

Gemiddelde beoordeling: 0 (0 Stemmen)

U kunt commentaar op deze vraag geven