using query with importRange() - "Unable to parse query string for Function QUERY parameter 2: NO_COLUMNF"

Question

I have tried lots of permutations and keep hitting a wall. Why don't this work.

Step 1, this works:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/blablabla/edit#gid=blablabla","combined!A1:F10")

Produces:

ga:year ga:month    ga:source   ga:medium   ga:campaign ga:goalCompletionsAll
2013    6   #3 Announcement of new titles   email   iPad title announcement 1
2013    6   (direct)    (none)  (not set)   3948
2013    6   0fb72887-fb60-4d86-b8e2-9990801bc83e    (not set)   wda 0
2013    6   1.1.1.1 referral    (not set)   0
2013    6   1.1.2.1 referral    (not set)   0
2013    6   10.122.101.1    referral    (not set)   0
2013    6   10.14.163.91:8080   referral    (not set)   0
2013    6   10.15.0.1   referral    (not set)   0
2013    6   10.173.0.1  referral    (not set)   0

So far so good.

My actual data set is tens of thousands of records long. I want to count(*) initially on this sample set:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/blablabla/edit#gid=blablabla","combined!A1:F10"),"SELECT COUNT(F)",1)

Gives:

Unable to parse query string for Function QUERY parameter 2: NO_COLUMNF

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/blablabla/edit#gid=blablabla","combined!A1:F10"),"SELECT COUNT(*)",1)

Gives:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROREncountered "*" 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" ... <ID> ... <QUOTED_ID> ...

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/blablabla/edit#gid=blablabla","combined!A1:F10"),"SELECT COUNT F",1)

Gives:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROREncountered "F" at line 1, column 14. Was expecting one of: <EOF> "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "(" ...

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/blablabla/edit#gid=blablabla","combined!A1:F10"),"SELECT COUNT('ga:goalCompletionsAll')",1)

Gives: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROREncountered "\'ga:goalCompletionsAll\'" 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" ... ... ...

And I've tried lots of other things. Keep getting errors.

When I'm inside the sheet in question and run the query, it works. It's only when I try to import it causes problems. But if you look at the first function I wrote I can import the data.

What gives?

Stephen Rouffy Stephen Rouffy · Accepted Answer · 2016-01-02T02:29:17

If you haven't gotten it to work try Col6 instead of F. When using query with importRange it requires the column number where Col1 is the first column selected in the imported range.

using query with importRange() - "Unable to parse query string for Function QUERY parameter 2: NO_COLUMNF"

2 Answers