How to remove rows in Google Sheets from an imported HTML table?

Question

Working on a spreadsheet. I want to drop rows 98 through 102, as well as row 164 & 117.

But I cannot figure out how.

Formula in use: =(query(query(IMPORTHTML("https://www.tcgplayer.com/categories/trading-and-collectible-card-games/disney-lorcana/price-guides/the-first-chapter","table"),"select Col2,Col5,Col6,Col7"),"select * order by Col2 ASC",1))

Note: I don't know anything about formulas. You're going to have to spell it out for me.

Tried to "delete Row." It just loads back in.

I found several google search results for dropping or selecting specific columns, but google didn't help with results for dropping rows.

Also found a formula for removing duplicate rows. I don't have duplicates. I just have rows I want to eliminate.

Answer 1

You are importing data from a website using IMPORTHTML. You want to "remove" given row numbers.

There are probably several ways to answer this question. Using QUERY, it is not a matter of deleting "rows" based on a row number but using the WHERE clause to exclude records based on data attributes.

There are two attributes to exclude:

• Rarity: "None"
• Name: contains "(Oversized)"
• where not Col5 = 'None' and not Col2 contains '(Oversized)'

The NOT qualifier will exclude these attributes.

• NOT appears before the relevant comparison clause
• and will exclude both of the attributes

---

=query(    
IMPORTHTML("https://www.tcgplayer.com/categories/trading-and-collectible-card-games/disney-lorcana/price-guides/the-first-chapter","table",1),
"select Col2, Col5, Col6, Col7 
where not Col5 = 'None' and not Col2 contains '(Oversized)'")

---