Skip to content

Instantly share code, notes, and snippets.

@sbealer

sbealer/gist:8739130

Created January 31, 2014 18:20
Show Gist options
  • Save sbealer/8739130 to your computer and use it in GitHub Desktop.
Save sbealer/8739130 to your computer and use it in GitHub Desktop.
Download ZIP
Advanced Google Query to pivot values
Raw
gistfile1.txt
say you have a spreadsheet formatted thusly (for instance a survey form):
timestamp, user, question
'2013-01-01', person1, Y
'2013-01-01', person2, Y
'2013-01-01', person3, N
and you want to get a count, by day, of the responses like:
Date, Y, N
'2013-01-01', 2, 1
The below function will do that for you. Notice the use of the toDate function to allow grouping by day. Also notice the pivot C which pivots the question column.
=QUERY('Form Responses'!A:D;"select toDate(A), count(D) where B != '' group by toDate(A) pivot C label toDate(A) 'Day'")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment