CODEWITHSUNDEEP
google-sheets
Oliver Paton
Oliver Paton

Reputation: 49

Google sheets query to remove header not working with offset

Can't figure out why some of these queries work and some don't. Just trying to build a table of my own

=query(TRANSPOSE(ImportHtml(C7, "table",1)),"select * limit 1 offset 1")

=query(TRANSPOSE(ImportHtml(C2, "table",1)),"select * limit 1 offset 1")

Where c2 = http://www.vitalmtb.com/product/compare/2819 and C7 = http://www.vitalmtb.com/product/compare/2775

The ones not working bring back the row of headers, when I want to remove the headers and leave the data.

Upvotes: 2

Views: 7278

Answers (1)

user6655984
user6655984

Reputation:

The query formula has the third, optional parameter: the number of header rows. If it's not provided, a guess is made to what rows are headers (usually the top one is). In your first example, its guess is that there is 1 header row.

The header row is always a part of the array returned by query, which uses it to label the returned columns (unless you override that by providing different headers with label). This is why you can't get rid of it by changing offset: the offset determines with data rows to return; the header row is present regardless.

It you want to get rid of headers, set the third parameter to 0, so that the headers are treated as data. Then offset will offset past them: 

=query(TRANSPOSE(ImportHtml(C2, "table", 1)), "select * limit 1 offset 1", 0)

Upvotes: 2

Related Questions