How to query several documents by using specific headers in Google Sheets?-CodePudding

I have several workbooks, each consisting of form results from various surveys and questionnaires I've conducted over time. What I would like to accomplish is import only specific columns by matching their headers from all of these documents. For example, I would like to collect the emails of all English speaking respondents who opted for the newsletter. Here are the examples I would like to unify into a single sheet:

Workbook 1 > Sheet X > Column B (Email), Column E (Language), Column X (Newsletter)
Workbook 1 > Sheet Y > Column B (Email), Column C (Language), Column G (Newsletter)
Workbook 2 > Sheet Z > Column A (Email), Column C (Language), Column J (Newsletter)
etc.

The result should be something like Workbook 3 > Sheet X >

query({Workbook 1(Sheet!Range); Workbook 2(Sheet!Range)},"SELECT Email WHERE Language='English' AND Newsletter='Yes'"

from indicated workbooks and sheets.

I've figured how to import data from external sheets using

=query(importrange(URL, Sheet!Range), Query)

and managed to match headers using the following trick:

=QUERY(data!A:X,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("Email addres",data!A1:X1,0),4),1,"")), but only within the same workbook.

I tried combining these but ran into various errors. How can I achieve the desired result?

CodePudding user response：

see this example for 3 spreadsheets where row 1 contains headers and each spreadsheet contains all 3 columns:

=INDEX(QYERY({
 TRANSPOSE(QUERY(TRANSPOSE(TO_TEXT(IMPORTRANGE("id1", "sheetname!A1:Z"))), 
 "where Col1 matches 'Email|Language|Newsletter'", 0));
 TRANSPOSE(QUERY(TRANSPOSE(TO_TEXT(IMPORTRANGE("id2", "sheetname!A1:Z"))), 
 "where Col1 matches 'Email|Language|Newsletter'", 0));
 TRANSPOSE(QUERY(TRANSPOSE(TO_TEXT(IMPORTRANGE("id3", "sheetname!A1:Z"))), 
 "where Col1 matches 'Email|Language|Newsletter'", 0))}, 
 "where Col1 is not null", 0)