Buggy, Quirky Behaviour to Watch for in Google Spreadsheets ImportData and QUERY Formulae

One of the problems of playing with tech is that lots of it is actually broken, and you don’t always notice… and when you do, you can waste hours trying to work out whether its your fault or a bug.

So for example, I was getting errors several steps down the line in something I was tinkering with, which I’ve managed to track back to what appears to be a bug in a Google Spreadsheet QUERY formula.

See if you notice anything odd in this video:

[Arghhh… note to self (again!): when grabbing screencasts, get the mouse cursor out of the way…:-(]

When I go past a certain import range, the first imported line gets messed up… Looking back at the original data sheet from which the data is imported, we can maybe see why?

The cause of the Google spreadsheets bug?

I’m guessing the cells are of different types – strings generally, but a number type in F4. I guess this could be claimed to be my problem, but as the data in this original sheet is being imported as CSV data from a URL, it’s the spreadsheet that’s casting various types into the same column.

Maybe we can force it to a string type?

Forcing the data type in Google Spreadsheet columns

Does it fix it?

QUERY input now fixed...

Apparently so….

And the lesson? When using the =QUERY() formula, make sure that you’re importing data of the same datatype in each cell; and when using the =ImportData() formula, cast the type of the columns yourself… (I’m assuming this persists, and doesnlt get reset each time the spreadsheet resynchs the imported data from the original URL?)

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

3 thoughts on “Buggy, Quirky Behaviour to Watch for in Google Spreadsheets ImportData and QUERY Formulae”

  1. Google Refine is pretty useful for data cleansing / inspecting but that is a cure rather than a prevention.

Comments are closed.