Google-sheets – IMPORTHTML data is sporadically changing to incorrect format

google-sheets-cell-formatgoogle-sheets-queryimporthtml

I'm hoping you can help me with my problem using importhtml with Google Sheets. It is a problem similar to Trying to use Google Sheets importHTML() to import a table. It forces content to a date format

I am using importhtml and query together to get data that includes duration data that can be in either XX:XX or XX:XX:XX. The data is not always HH:MM:SS format. Sometimes it's MM:SS:00 Sometimes it's MM:SS, and others are HH:MM:SS.

If I use importthtml and view the data while the the column is set to Automatic formatting, view of data is correct.
However if I use query, in some cases the data changes to something weird, especially if the leading value is 24. 24 gets translated into 00. I guess it's getting confused with a time value. I would like either data to remain exactly as it comes from the URL, but it seems Google Sheets tries to format it.
I've tried many different workarounds to no avail.

The table lookup with correct time values is: 

=IMPORTHTML("http://www.thepowerof10.info/athletes/profile.aspx?athleteid=800922&viewby=date","table",18)

But using query in conjunction with importhtml I get different values for only some of the results.

=query(IMPORTHTML("http://www.thepowerof10.info/athletes/profile.aspx?athleteid=800922&viewby=date","table",18),"select Col1, Col2")

I hope that there is some way to get the data to be correct.

Best Answer

Possibly try a different approach. Don't use query but instead, in A1 and copied across to B1:

=index(IMPORTHTML("http://www.thepowerof10.info/athletes/profile.aspx?athleteid=800922&viewby=date","table",18),,column())

Then either format ColumnB as Duration (and accept some extra zeros) or also use the plain IMPORTHTML as the source to copy format only from its relevant column into ColumnB and then delete the 'plain' formula.

Related Topic