regexgoogle-sheetsimportgoogle-sheets-formulaespn

WebScraping ESPN Live Box Scores on Google Sheets


I am trying to create a Google Sheet that tracks the live data from any NBA game (given the ur) on ESPN using

=query(importhtml(L1&"?refresh="&H1,"table",2),
"Select Col1,Col15, Col4,Col8,Col9 
WHERE Col1 <> 'Bench' AND Col1 <>'TEAM'")

L1 in the formula is a reference to the ESPN website that the users inputs

Here is a link to the google sheet: https://docs.google.com/spreadsheets/d/1g2pF_eYETgY2139AgEs9qajMpk5MUHLv1Hmp0_6e0EI/edit?usp=sharing

I am trying to get the first column to output like the table on the bottom

Wanted Player Name Output
A. Davis
S. Johnson
R. Westbrook

Also I am trying to change the 3PT column to only show

3 pointers made: [ 3-5 --> 3]

Any help would be greatly appreciated!


Solution

  • try:

    =ARRAYFORMULA({{"Starters"; QUERY(IFNA(REGEXEXTRACT(
     QUERY(IMPORTHTML(L1&"?refresh="&H1, "table", 2),
    "select Col1 where not Col1 matches 'TEAM|Bench'"), "(.+?[A-z]+)[A-Z]")), "offset 1", )}, 
     REGEXREPLACE(TO_TEXT(QUERY(IMPORTHTML(L1&"?refresh="&H1, "table", 2),
    "select Col15,Col4,Col8,Col9 where not Col1 matches 'TEAM|Bench'")), "(-.*)", )})
    

    enter image description here