CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-query
ChaPPer5
ChaPPer5

Reputation: 105

QUERY() function treats multiple rows as the header and groups all into first row. Is there a fix or workaround?

When I use the query() function, if there is a date or number column that has some blanks at the top it will group all the rows down to the first date/number entry into the first row, with the headers. I need it to treat all rows after the header as individual rows, regardless of blanks. I'm assuming that it treats those rows as not being part of the data because they don't have values in certain columns, however it achieves this decision sporadically. See image or link for context.

My attempts at resolving so far have been

  1. Removing the labels at the top i.e.: =QUERY(A1:C, "SELECT * label A ''") but that kept the grouping.
  2. ORDER BY doesn't help, as those top rows seem exempt, part of the header.
  3. Inserting a column of numbers to trick it into thinking there are numbers, it ignores this wherever I position it (hence the sporadic comment).

I am aware that I could write a Google Apps Script to resolve this, however I'm trying to keep the required skill for adapting the process on a level that others can use. The data is coming from an API that I can't order or format until in the spreadsheet.

This is the only blocker from me fully automating several processes so I'd appreciate any help in finding a workaround or a solution. :)

Image: Cell E1 is =QUERY(A1:C, "SELECT *") and you can see A2 to A5 are shoved unceremoniously in with the header. Solutions?

Code with outcome

https://docs.google.com/spreadsheets/d/1MU35HrkRxyHQaliQgKxqeHBViulMnRmPN9UUO7kq0ts/edit?usp=sharing

Upvotes: 1

Views: 2441

Answers (1)

JPV
JPV

Reputation: 27282

Using the (optional) headers argument should solve this. See if this helps

=QUERY(A1:C, "SELECT *", 1)

Upvotes: 4

Related Questions