Select specific row and range of rows from specific columns with query

Question

I have the following spreadsheet :

|  |   A    |    B   |    C   |   D    |   E    |
| 1| Labels | Item 1 | Item 2 | Item 3 | Item 4 |
| 2| name   |   yes  |  no    |  yes   |  yes   |
| 3| price  |   yes  |  no    |  yes   |  yes   |
| 4| tag    |   yes  |  no    |  yes   |  yes   |
| 5| desc   |   yes  |  no    |  yes   |  yes   |
| 6| loc    |   yes  |  no    |  yes   |  yes   |
| 7| ref    |   yes  |  no    |  yes   |  yes   |
| 8| obj    |   yes  |  no    |  yes   |  yes   |
| 9| rand   |   yes  |  no    |  yes   |  yes   |
|10| rand2  |   yes  |  no    |  yes   |  yes   |
|11| rand3  |   yes  |  no    |  yes   |  yes   |
|12| rand4  |   yes  |  no    |  yes   |  yes   |

Note: 1 to 12 and A to E are not within the sheet

How can I select row 1 and a range of row 5 to 10 from columns A, C, E to have the following result? :

|  |   A    |    C   |   E    |
| 1| Labels | Item 2 | Item 4 |
| 5| desc   |  no    |  yes   |
| 6| loc    |  no    |  yes   |
| 7| ref    |  no    |  yes   |
| 8| obj    |  no    |  yes   |
| 9| rand   |  no    |  yes   |
|10| rand2  |  no    |  yes   |

Note: I left 1, 5, 6, …, 10 and A,C,E just to better understand the output. I don't want them in my data, I just want the data within the sheet

I got something like this working for a range of row : SELECT A,C,E LIMIT 6 OFFSET 4 but I miss row 1 and can't find how to add it in the same query.

Answer 1

try:

=QUERY(A1:E; "select A,C,E limit 6 offset 3"; 1)

Answer 2

In case you're looking for a solution with Google Script :

If the ranges to consider will be selected manually by a user, you might be interested in using getActiveRangeList()

Documentation : https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactiverangelist

If the ranges to consider are fixed and can be integrated in your script, you might be interested in using getRangeList()

Documentation : https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getrangelista1notations

Note that if you want to iterate on a rangeList in your script, you will also need to retrieve the different ranges of your list, using getRanges(). Example :

    var rangeList = ss.getActiveRangeList();
    var myRanges = rangeList.getRanges();

    for (i = 0; i < myRanges.length; i++) {
    ...
    }

I hope that this is useful. Cheers

Answer 3

with query:

=query(filter({row(A1:A),A1:E&""},A1:A<>""), "Select Col2, Col4, Col6 where Col1=1 or Col1>=5 and Col1<=10")

with filter:

=filter({A1:A & "",C1:C&"", E1:E& ""},((row(A1:A)=1)+(row(A1:A)>=5)*(row(A1:A)<=10))>0)