CODEWITHSUNDEEP
web-scrapinggoogle-sheetsgoogle-sheets-formula
orschiro
orschiro

Reputation: 21735

How to import first column using =IMPORTHTML?

I am trying to import this table:

enter image description here

Source: https://www.tradeskillmaster.com/black-market?realm=EU-draenor

However, upon using =IMPORTHTML("https://www.tradeskillmaster.com/black-market?realm=EU-draenor","table"), the first column remains blank.

enter image description here

Test: https://docs.google.com/spreadsheets/d/1MVgD5MUgOik89MMZweLKMZQLSkMeBNoCN_FrPe-eZ5U/edit?usp=sharing

Upvotes: 0

Views: 851

Answers (2)

Tanaike
Tanaike

Reputation: 201388

How about this answer? Please think of this as just one of several possible answers.

In this answer, IMPORTXML is used instead of IMPORTHTML.

Sample formula:

={QUERY(IMPORTXML(A1,"//tr | //td/a/@title"),"SELECT Col1 WHERE Col1 IS NOT NULL"),QUERY(IMPORTXML(A1,"//tr"),"SELECT Col2,Col3,Col4,Col5,Col6,Col7,Col8")}
  • The URL of https://www.tradeskillmaster.com/black-market?realm=EU-draenor is put in the cell "A1".
  • Column "A" is retrieved from QUERY(IMPORTXML(A1,"//tr | //td/a/@title"). In this case, the xpath of //tr and //td/a/@title have the value and title of the column "A", respectively.
  • Column "B" to "H" are retrieved from QUERY(IMPORTXML(A1,"//tr"),"SELECT Col2,Col3,Col4,Col5,Col6,Col7,Col8"). In this case, the 1st column is removed.

Result:

enter image description here

Reference:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

player0
player0

Reputation: 1

if the table contains JavaScript elements (which it does in your case) then such elements are not possible to import into Google Sheet with any formula. scraping of JS in GS is just not supported.

Upvotes: 2

Related Questions