CODEWITHSUNDEEP
google-sheets
owenw
owenw

Reputation: 25

Google Sheets importxml query to remove one piece of data

I'm using the following query in my sheet to import total Spotify streams for artists. Example:

=IMPORTXML("https://chartmasters.org/spotify-streaming-numbers-tool/?artist_name=&artist_id=1uh2pZRWuOebEoQgFVKK7l&displayView=Disco","//tr[@class='careerTotals'][2]")

However it's returning one extra value I don't want ("EAS"). I would like to just have the artist name in A and the total streams in B. Any ideas? Thanks.

Upvotes: 2

Views: 480

Answers (1)

Tanaike
Tanaike

Reputation: 201643

How about these modifications?

Modified formula:

=TRANSPOSE(IMPORTXML(A1,"//tr[@class='careerTotals'][2]/td[position()<3]"))

or

=QUERY(IMPORTXML(A1,"//tr[@class='careerTotals'][2]"),"SELECT Col1,Col2")
  • The URL of https://chartmasters.org/spotify-streaming-numbers-tool/?artist_name=&artist_id=1uh2pZRWuOebEoQgFVKK7l&displayView=Disco is put in the cell "A1".
  • At 1st modified script, the expected values are retrieved with xpath of //tr[@class='careerTotals'][2]/td[position()<3] and those are put to the columns using TRANSPOSE.
  • At 2nd modified script, the expected values are retrieved from the retrieved 3 values using QUERY.

Result:

This result is from the 1st modified formula. 2nd one is also the same result.

enter image description here

References:

Upvotes: 2

Related Questions