=IMPORTHTML forces content to date format

Question

I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question:

Trying to use Google Sheets importHTML() to import a table. It forces content to a date format

to this table:

https://www.actionnetwork.com/nfl/nfl-against-the-spread-standings

In short, IMPORTHTML changes sports records to date format. Changing the numbers format to plain text does not work when importing websites, as the format is preset. However, there is a workaround using the ARRAYFORMULA and REGEXREPLACE. When you open the link scroll down to Aurielle's response for the single formula version. I have tried to implement the workaround for the NFL record link above, but I can't quite find the right formula mostly because, again, I'm a beginner and I don't understand these formulas. I was wondering if anyone understood this better and could give me the correct formula for this specific table of NFL records.

I would greatly appreciate any help!

Answer 1

There are likely to be many solutions to your question. Even in this answer, I would expect that the structure could be simplified - possiblky along the lines of the answer by Rubén in Trying to use Google Sheets importHTML() to import a table. It forces content to a date format. In that answer, Rubén constructed a formula in which column() was a variable. This would be a convenient enhancement to this answer.

The answer is in two parts:

• Part#1 - variables

  • Cell A1: the URL https://www.actionnetwork.com/nfl/nfl-against-the-spread-standings
  • Cell A2-A12 - (11 cells) contain the XPath for specific elements of the output formulae.
    • A2-Headers: //*[@id="__next"]/div/main/div/div[2]/div/table/thead/tr[1]
    • A3-Team Names: //*/div/main/div/div[2]/div/table/tbody/tr/td[1]
    • A4-Overall: //*/div/main/div/div[2]/div/table/tbody/tr/td[2]
    • A5-Home: //*/div/main/div/div[2]/div/table/tbody/tr/td[3]
    • A6-Away: //*/div/main/div/div[2]/div/table/tbody/tr/td[4]
    • A7-ATS: //*/div/main/div/div[2]/div/table/tbody/tr/td[5]
    • A8-ATS Home: //*/div/main/div/div[2]/div/table/tbody/tr/td[6]
    • A9-ATS-Away: //*/div/main/div/div[2]/div/table/tbody/tr/td[7]
    • A10-Ov/Un: //*/div/main/div/div[2]/div/table/tbody/tr/td[8]
    • A11-Ov/Un:Home //*/div/main/div/div[2]/div/table/tbody/tr/td[9]
    • A12-Ov/Un-Away: //*/div/main/div/div[2]/div/table/tbody/tr/td[10]

• Part#2 - Output

  • Cell C1 - Headers/Column Titles
    • =importxml(A1,A2)
  • Cell C2 - Team names Column
    • =ARRAYFORMULA(trim(transpose(query(transpose(query({importxml(A1,A3)})),,COLUMNS(query({importxml(A1,A3)}))))))
  • Cells D2-L2 (9 columns) - column data
    • Overall: =arrayformula(substitute(ARRAYFORMULA(SUBSTITUTE(trim(transpose(query(transpose(query({importxml(A1,A4)})),,COLUMNS(query({importxml(A1,A4)})))))," "," → "))," → ",""))
    • Home: =arrayformula(substitute(ARRAYFORMULA(SUBSTITUTE(trim(transpose(query(transpose(query({importxml(A1,A5)})),,COLUMNS(query({importxml(A1,A5)})))))," "," → "))," → ",""))
    • Away: =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A6)})),,COLUMNS(query({importxml(A1,A6)})))))," "," → ") )," → ",""))
    • ATS: =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A7)})),,COLUMNS(query({importxml(A1,A7)})))))," "," → ") )," → ",""))
    • ATS Home: =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A8)})),,COLUMNS(query({importxml(A1,A8)})))))," "," → ") )," → ",""))
    • ATS Away: =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A9)})),,COLUMNS(query({importxml(A1,A9)})))))," "," → ") )," → ",""))
    • Ov/Un: =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A10)})),,COLUMNS(query({importxml(A1,A10)})))))," "," → ") )," → ",""))
    • Ov/Un Home: =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A11)})),,COLUMNS(query({importxml(A1,A11)})))))," "," → ") )," → ",""))
    • Ov/Un Away" =arrayformula(substitute(ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(query({importxml(A1,A12)})),,COLUMNS(query({importxml(A1,A12)})))))," "," → ") )," → ",""))

---

URL and XPaths

---

Output

Highlighted cells indicate a cell containing a formula.

---

Credit: Max Makhrov: How to use CONCAT in QUERY?

Answer 2

@John Tokish, this should do the trick:

=ArrayFormula(REGEXREPLACE(TO_TEXT(IMPORTHTML("https://www.actionnetwork.com/nfl/nfl-against-the-spread-standings","table",1)),"200|20.+$",""))

As you see, I've first converted the entire IMPORT* TO_TEXT. This takes care of everything but the W-L-T formats, which can easily be converted with the REGEXREPLACE, since all W-L-T will come through as text-dates with years in the 2000s.

Of course, if you have the URL in another cell (e.g., A1), the formula can be shortened:

=ArrayFormula(REGEXREPLACE(TO_TEXT(IMPORTHTML(A1,"table",1)),"200|20.+$",""))

Answer 3

Here is a solution using a single formula

=ArrayFormula(
REGEXREPLACE(SPLIT(TRANSPOSE(SPLIT(JOIN("",TRANSPOSE(QUERY(TRANSPOSE(
{IMPORTXML(A1,"//tr/*"),TRANSPOSE(SPLIT(REPT(REPT("|,",9)&"*,",33),","))}
),,1000000))),"*")),"|")," {0,}- {0,}","-")
)

It will only works while the table to imports keeps it's current shape:

• 9 columns
• 1 headers row
• 33 rows, one for each team.

The formula use IMPORTXML instead of IMPORTHTML because it allows us to use xPath.

xPath allows us to select the nodes text to be imported.

It's worthy to note that IMPORTXML doesn't solve that some team stats (wins-loses-draws) be converted to dates but, considering that the wins-loses-draws have a node between them we could import each of these values in separate cells.

In this case IMPORTXML is resturning a an array of values having a single column. TRANSPOSE, QUERY, JOIN, SPLIT, REPT are used to transform the single column into a 34 rows x 9 columns table.

REGEXREPLACE is used to remove the extra spaces by using a regular expression to match the spaces and dash between the wins, loses and draws resulting from IMPORTXML and the xPath used.