CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
geetee
geetee

Reputation: 33

In Google Sheets, can I query sheets from the current workbook as well as sheets from a second workbook together in the same array?

I have a formula that currently queries four sheets in the same workbook:

=IFNA(QUERY({'Sheet1'!A2:J;'Sheet2'!A2:J;'Sheet3'!A2:J;'Sheet4'!A2:J},"select Col2,Col5, ...

I want to add a fifth sheet into the array that's actually kept in another workbook. As much as it would solve all my problems, I can't bring the data into the same workbook as the other sheets due to privacy/permissions issues. I've tried doing this:

=IFNA(QUERY({IMPORTRANGE("<url>","'New Sheet'!A2:J");'Sheet1'!A2:J;'Sheet2'!A2:J;'Sheet3'!A2:J;'Sheet4'!A2:J},"select Col2,Col5, ...

... but it gives me an error "Unable to parse query string for Function QUERY parameter 2: lower takes a text parameter."

Is there any way to incorporate an importrange into the sheets array of a query?

Upvotes: 1

Views: 666

Answers (1)

Osm
Osm

Reputation: 2891

01 - Paste this in a separate cell first and allow access.

IMPORTRANGE("<url>","'New Sheet'!A2:J")

02 - and then your formula

=IFNA(QUERY({IMPORTRANGE("<url>","'New Sheet'!A2:J");
            'Sheet1'!A2:J;
            'Sheet2'!A2:J;
            'Sheet3'!A2:J;
            'Sheet4'!A2:J},"select Col2,Col5,...

If that doesn't work, try using UPPER or nested LOWER(LOWER()) instead of LOWER elsewhere in your formula.

Upvotes: 1

Related Questions