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:
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!
1 Answer
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'")), "(-.*)", )}) 
