1

I'm attempting to import some data to my Google Sheet.

Using QUERY to reshape my data, an example code of what I am trying to import is as follows:

=query(importhtml("http://statfox.com/cbb/cbbteam.asp?teamid=DUKE&season=2019&log=1",
 "table",8),"Select * where Col2 is not null order by Col1")

Unfortunately, in many cases where a team is an underdog and the line should be +#, the query is not returning the value.

If I remove the QUERY and use the following importhtml all values show up correctly:

=importhtml("http://statfox.com/cbb/cbbteam.asp?teamid=DUKE&season=2019&log=1","table",8)

Any insight into the matter or ideas on what might be causing this or solutions would be greatly appreciated.

2

this is because imported data are in the format +2.5 instead of simple 2.5

the solution is to remove + sign with:

=QUERY(ARRAYFORMULA(SUBSTITUTE(
 IMPORTHTML("http://statfox.com/cbb/cbbteam.asp?teamid=DUKE&season=2019&log=1", "TABLE", 8),
 "+", "")), "Select * where Col2 is not null order by Col1")

|improve this answer|||||
  • Thank you very much - this is exactly what I needed! – NCAA Binder Dec 27 '18 at 5:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.