1

I'm trying to use a Google charts query in my spreadsheet to count the number of form responses by a specific person on a given date.

I'm very new to the Google Charts query language (I just started trying to figure it out about an hour ago), but I know SQL so I thought it wouldn't be too hard.

Anyway, here's what my query looks like right now:

=QUERY('Form Responses'!A2:F100, "select count(*) where name = 'Ajedi32' and date timestamp = date '2013-11-19'", {"timestamp", "name", "done", "todo", "blocker", "comment"})

And here's the error I'm getting:

error: Invalid query: Query parse error: Encountered " " * " " * "" at line 1, column 14. Was expecting one of: "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... ...

I don't get it. Why would it be expecting a function name instead of *?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Ajedi32
  • 45,670
  • 22
  • 127
  • 172

2 Answers2

1

I think the problem is likely that count(*) is not a valid construct in the Query language. Try using a column name instead of *.

[edit - answer pertains to a different API; new answer below]

After reading over some questions from other users (link1, link2, [link3(Google spreadsheet Query Error - column doesn't exist)), it seems like the QUERY function does not support the use of column names. You can either refer to columns by letter:

=QUERY($A$1:$C$10, 'select A, B, C');

or by column index in the selected range:

=QUERY($A$1:$C$10, 'select col1, col2, col3');

Try that and see if addressing your columns by letter or index works.

Community
  • 1
  • 1
asgallant
  • 26,060
  • 6
  • 72
  • 87
  • Nope that's not it. Changing the `*` to `timestamp` gives the same error: `error: Invalid query: Query parse error: Encountered " "timestamp" "timestamp "" at line 1, column 14. Was expecting one of: "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... ...` – Ajedi32 Nov 19 '13 at 20:04
  • I see from your own answer that you are not using the system I thought you were: you are dealing with the internal spreadsheet queries, and I was thinking of the external query interface. Ignore my answer, they have nothing in common. – asgallant Nov 19 '13 at 21:07
  • That's weird. I was going off of Google's [spreadsheet function list](https://support.google.com/drive/table/25273?hl=en), which seems to require a `headers` parameter. You seem to be right about using the column letters though. – Ajedi32 Nov 20 '13 at 00:59
0

Ultimately, I ended up avoiding the query function entirely by using the filter function instead:

=COUNT(FILTER('Form Responses'!$A$1:$F$100; DATEVALUE('Form Responses'!$A1:$A100)=DATEVALUE('2013-11-19'); 'Form Responses'!$B1:$B100='Ajedi32'))

Expanded for readability:

=COUNT(
  FILTER(
    'Form Responses'!$A$1:$F$100;
    DATEVALUE('Form Responses'!$A1:$A100)=DATEVALUE('2013-11-19');
    'Form Responses'!$B1:$B100='Ajedi32'
  )
)

(If anyone still wants to have a go at answering my original question, feel free.)

Ajedi32
  • 45,670
  • 22
  • 127
  • 172