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'")), "(-.*)", )})
- Return elements from a list that match a pattern
- convert/compile regular expressions to C code
- How to make the regex negative lookbehind consume discarded text?
- Generate words that fit in Guids (just for fun)
- Insert newlines before sequences of all-caps words
- Convert all uppercase words in a string to title case
- Add a character in between regex
- RegExp testing fails when string is validated in RegExp tools
- VSCODE snippet PHP to fill namespace automatically
- Regular expression to extract text between square brackets & last string out of the square bracket
- What is the difference between a plus and a star in regex?
- Extract class names from JAR with special formatting
- How to replace a comma with a dot in a number (or any replacement)
- regex to match at certain string and not followed by another string using notepad++
- RegEx for matching UK Postcodes
- Why does the grep command select more than expected?
- Regex for ISO 8601 durations
- Regular expression for number with length of 4, 5 or 6
- Regex to match a whole number not ending in some digits
- Can anyone lead me to DEFINE assertions documentation for regular expressions?
- Regex to find string not followed by separator and 1 or 2 digits
- Regex for quoting unquoted XML attributes
- Regex to extract all the rows from CSV - Apache Nifi
- 12-digit regex for phone number
- regex to exclude lowercase only strings, but save strings which contain capital
- Extract year with an optional plus right after from a string using PHP regex
- missing quotation marks on keys in JSON
- Regex for a valid hashtag
- Enumerate Possible Matches of Regular Expression in Java
- camelCase to underscore in vi(m)