2

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!

player0
  • 124,011
  • 12
  • 67
  • 124
Evans Raymond
  • 59
  • 6

1 Answers1

1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    This works fantastic! Can you explain what how the function works for me to reference back to? I've never done anything like this complex! – Evans Raymond Feb 07 '22 at 16:25
  • 1
    @EvansRaymond we use two queries in {} array. first, one is limited to output only the first column where we use regex within query via matches to exclude Team or Bench. next, we run it through REGEXEXTRACT and extract the following pattern: start with anything followed by Proper (big alphabet followed by small ones) right before caps start again. the second query generates 2nd, 3rd, 4th & 5th column but now we use REGEXREPLACE to remove everything after dash - including the dash itself – player0 Feb 07 '22 at 19:31
  • Okay so if I'm understanding correctly, the REGEXEXTRACT uses the query to get the first column of the player names not including 'Team' or 'Bench' and then formats it to first letter first name, period, whole last name (example: J. Randle for Julius Randle) The REGEXREPLACE function then adds the other stat columns again not with 'Team' or 'Bench' and formats it to the number before the dash (2 for 2-3) So if I have an array of specific players I want to track, can I reference that array of players so then the function would only then return the stats for the specific players? – Evans Raymond Feb 08 '22 at 04:18
  • 1
    @EvansRaymond yes and yes. for specific players you can just define it in (both) query like: `"select Col1 where Col1 matches '.*Davis.*|.*Ariza.*' "` – player0 Feb 08 '22 at 11:50
  • that worked perfectly! So the two queries now read "select Col1 where not Col1 matches 'TEAM|Bench' and Col1 contains 'Davis' or Col1 contains 'Ariza'" & "select Col15,Col4,Col8,Col9 where not Col1 matches 'TEAM|Bench' and Col1 contains 'Davis' or Col1 contains 'Ariza'" (the shorthand you wrote I'm unfamiliar with regex expressions so I'm not sure where in the query to put it. So if I have a list of players, is it possible to reference specific ones instead of hard coding them into the query? – Evans Raymond Feb 08 '22 at 20:09
  • 1
    @EvansRaymond regex in query is after `matches` and yes you can: `"select Col1 where Col1 matches '.*"&TEXTJOIN(".*|.*", 1, A2:A)&".*' "` where A2:A holds your names – player0 Feb 08 '22 at 23:14
  • so I added the select query for the player list and it didn't pull the selected player names when I attempted it. Did i do it correctly? https://docs.google.com/spreadsheets/d/1g2pF_eYETgY2139AgEs9qajMpk5MUHLv1Hmp0_6e0EI/edit?usp=sharing – Evans Raymond Feb 11 '22 at 22:40
  • 1
    you did but the url outputs stuff that is not in J1:J so it errors out - https://i.stack.imgur.com/3Wl9Q.png – player0 Feb 12 '22 at 02:54