How to use importrange with a condition?

Question

I already tried this formula but it pops up a VALUE error:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_xao-sZcCYX_adqb0OJ4SZ4D0sKj-4rRQwbursU78Oo/edit#gid=991773268","'CW NoFSH Accounts'!B8:K13706"),"SELECT * WHERE COL6 = 'SSL'")

Error:

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: COL6

Answer 1

First, you don't need all of that in your IMPORTRANGE.

Also, if this is the first time you've used IMPORTRANGE on that spreadsheet, you need to authorize it. And within a QUERY, you won't see the authorization; you'll just get an error.

Try this:

1.) Go to some blank cell and copy/paste this:

=IMPORTRANGE("1_xao-sZcCYX_adqb0OJ4SZ4D0sKj-4rRQwbursU78Oo","CW NoFSH Accounts!B8")

2.) If this is the correct ID and correctly typed sheet name, one of two things will happen: either you'll get a notice in the cell that you need to click to authorize connecting with the sheet, or the contents of cell B8 from that sheet will appear. If you get the notice, click it. If you get any other error, then either the ID is wrong or the sheet name is wrong, and you'll need to correct it. If you see the contents of cell B8 from that sheet, move on to the next step.

3.) If you either authorized the sheet connection or were able to see the contents of cell B8 from that sheet, you can now delete that temporary formula from the test cell.

4.) At this point, your original formula may now be working. If not, delete it and replace it with this:

=QUERY(IMPORTRANGE("1_xao-sZcCYX_adqb0OJ4SZ4D0sKj-4rRQwbursU78Oo","CW NoFSH Accounts!B8")),"SELECT * WHERE COL6 = 'SSL'")

Notice that IMPORTRANGE only needs the source sheet ID up front (the part after "/d/" and before the next forward slash, if there is another).

Notice also that you don't need the single quotes around sheet names in the second parameter (though IMPORTRANGE will ignore them if they're present).

I suspect that you just never authorized the sheet connection and that you'll be asked to authorize during Step 2 above.