Query + Importrange functions together

Question

I'm trying to get range from different sheet IDs. But getting error message.

=query({IMPORTRANGE($X$5,”CASES LIST!B9:T58”);IMPORTRANGE($X$6,”CASES LIST!B9:T58”);IMPORTRANGE($X$7,”CASES LIST!B9:T58”);IMPORTRANGE($X$8,”CASES LIST!B9:T58”);IMPORTRANGE($X$9,”CASES LIST!B9:T58”);IMPORTRANGE($X$10,”CASES LIST!B9:T58”);IMPORTRANGE($X$11,”CASES LIST!B9:T58”);IMPORTRANGE($X$12,”CASES LIST!B9:T58”);IMPORTRANGE($X$13,”CASES LIST!B9:T58”);IMPORTRANGE($X$14,”CASES LIST!B9:T58”);IMPORTRANGE($X$15,”CASES LIST!B9:T58”);IMPORTRANGE($X$16,”CASES LIST!B9:T58”);IMPORTRANGE($X$17,”CASES LIST!B9:T58”);IMPORTRANGE($X$18,”CASES LIST!B9:T58”);IMPORTRANGE($X$19,”CASES LIST!B9:T58”);IMPORTRANGE($X$20,”CASES LIST!B9:T58”);IMPORTRANGE($X$21,”CASES LIST!B9:T58”);IMPORTRANGE($X$22,”CASES LIST!B9:T58”);IMPORTRANGE($X$23,”CASES LIST!B9:T58”);IMPORTRANGE($X$24,”CASES LIST!B9:T58”);IMPORTRANGE($X$25,”CASES LIST!B9:T58”);IMPORTRANGE($X$26,”CASES LIST!B9:T58”);IMPORTRANGE($X$27,”CASES LIST!B9:T58”);IMPORTRANGE($X$28,”CASES LIST!B9:T58”);IMPORTRANGE($X$29,”CASES LIST!B9:T58”);IMPORTRANGE($X$30,”CASES LIST!B9:T58”);IMPORTRANGE($X$31,”CASES LIST!B9:T58”);IMPORTRANGE($X$32,”CASES LIST!B9:T58”);IMPORTRANGE($X$33,”CASES LIST!B9:T58”);IMPORTRANGE($X$34,”CASES LIST!B9:T58”);IMPORTRANGE($X$35,”CASES LIST!B9:T58”)},"select * where Col1 is not null",0)

Where X5:X38 refers to sheets ID.

Answer 1

The formula is using typographical / curly quotes ” instead of straight quotes ". Another possible problem might be have not authorized the access to the source spreadsheets.

To authorize the access to the spreadsheets put each IMPORTRANGE in a single cell. An authorization pop up will be shown, then click the "Allow access" button shown, once the authorization is done, you can remove these formulas.

Tip: On a blank column add the following formula to the top cell

=IMPORTRANGE($X7,"CASES LIST!B9:T58")

The filldown to get all the required formulas.

Related

• Automatically "Allow Access" for IMPORTRANGEs in Google Sheets?