How to use IMPORTRANGE when I have multiple tabs?

Question

I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new sheet.

Is there a way I can make the formula automatically search through all the tabs rather than manually write 30x IMPORTRANGE in a QUERY function?

Example for the formula I have created for one day of the month:

=QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q","01.05.2019!A1:M200")},"SELECT Col2 WHERE Col7 contains 'Emil' and Col11 <>10")

Your help will be highly appreciated.

Answer 1

Unfortunately no, you will need to do it this way:

=QUERY({
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "01.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "02.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "03.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "04.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "05.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "06.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "07.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "08.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "09.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "10.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "11.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "12.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "13.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "14.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "15.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "16.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "17.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "18.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "19.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "20.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "21.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "22.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "23.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "24.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "25.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "26.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "27.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "28.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "29.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "30.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "");
 IFERROR(QUERY({IMPORTRANGE("1ZMBUM_u8j74GZ1KFv0OqROcUfY6Wocr3IlgMGBn8q", 
 "31.05.2019!A1:M200")}, "select Col2 where Col7 contains 'Emil' and Col11 <>10"), "")},
 "where Col1 is not null", 0)

(and if you select 2 columns in a query you will need to add two fake columns eg. replace "" with {"",""})

Answer 2

There is a simpler way than the formula on the other answer but it could be used only if your spreadsheet have free cells to use as Google Sheets spreadsheets have a 5 million cells limit and this alternative will potentially double the required cells by the other hand this also could make your spreadsheet slower.

In summary add a new tab and a array to it.

The simpler form of such array has the following form

={'Sheet 1'!A:Z';'Sheet 2'!A:Z';'Sheet 3'!A:Z'}

If your sheets have blank rows, the you could add if you want to simplify your IMPORTRANGE you could put the above array as the first argument of your QUERY

=QUERY({'Sheet 1'!A:Z';'Sheet 2'!A:Z';'Sheet 3'!A:Z'},...)

Then import this new sheet.

NOTE: If the array is too complex to be built manually, you could use Google Apps Script to add the array for you.