How to exclude header row using Google Query and importrange

Question

I have a Google Query that is designed to import data from a single column in 6 separate Google Sheets. I also need to exclude the Header row in the source spreadsheet. My query is constructed as follows:

=query({importrange("Key","Range");importrange("Key","Range");......)},"Select * where Col1 <> ''",0)

What is happening is all the data from each source sheet is coming in but at each block of data the column header is also shown. Example below:

Header
data
data
data
Header
data
data
data
Header
data
data

The ,0) at the end is my attempt to exclude the header row. It works when I use the following query with a date column:

... where Col1 is not null",0)

The Example in the text above should have each word on a new row and not strung across the page as shown... — Mike Eburne
Can't you adjust the range so that it starts below the header row and only import the header row from the first importange (assuming headers are the same in all sheets)? I believe something like this should work: {importrange("Key","A1:Z");importrange("Key","A2:Z");...... — JPV
That was how I originally constructed the query. However, and perhaps I should have included this bit of information before, the source sheets are fed by Jotform responses and if I clear out the data, then somehow the data range shifts down to what was the last row - i.e. importrange("Key","A2:A1000"); becomes importrange("Key","A5:A1000");... Very frustrating as I cannot see how this is happening. That was the reason I went to include the entire row and somehow exclude the header row in the import. — Mike Eburne

player0 player0 · Accepted Answer · 2019-05-25T22:24:00

instead of this:

=QUERY({IMPORTRANGE("Key", "Sheet1!A1:Z");
        IMPORTRANGE("Key", "Sheet1!A1:Z");......}, "where Col1 is not null", 0)

do this:

=QUERY({IMPORTRANGE("Key", "Sheet1!A2:Z");
        IMPORTRANGE("Key", "Sheet1!A2:Z");......}, "where Col1 is not null", 0)

How to exclude header row using Google Query and importrange

2 Answers