0

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.

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.