Google-sheets – How to use IMPORTRANGE when I have multiple tabs

formulasgoogle sheetsgoogle-sheets-queryimportrange

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.

Best Answer

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 {"",""})

Related Topic