Missing Data in Table Query (tq) from Google Spreadsheet

[Mario Colombo]

Hi,

I am doing a table query on a Google Spreadsheet like this:

https://docs.google.com/a/webrepublic.ch/spreadsheet/tq?key=0AoaBYlIzWjk2dFJvbTRndlxxxxxxxxxxcWpBc2tKcUE&transpose=0&headers=0&gid=19&range=A1%3AE&&tqx=out:html

Now, some of the cells that contain text are not returned in this table query. Instead of the text content of the cell there is just an empty value returned. The part of the object that should contain the cell value shows the following: ,,{"v":null}

If I switch the headers parameter to 1 then the first row (now header) which normally is returned missing two cells now contains all the text values, however other text cells further down in the rows are still missing their values.

Since I often need to combine text labels with data cells this is something I really need to work. Does somebody have insights into this strange behaviours?

All the best

Mario

[Mario Colombo]

I have prepared a test spreadsheet, please see the unexpected behaviour here:

Table Query: https://docs.google.com/a/webrepublic.ch/spreadsheet/tq?key=0AoaBYlIzWjk2dHNyZndoTW1oZzN4WTBOamZMTEVONXc&transpose=0&headers=0&gid=0&tqx=out:html

Spreadsheet: https://docs.google.com/a/webrepublic.ch/spreadsheet/ccc?key=0AoaBYlIzWjk2dHNyZndoTW1oZzN4WTBOamZMTEVONXc#gid=0

It seems that for columns that contain text and number formatted content, the text content is stripped.

Why is that?

All the best

Mario

[asgallant]

I opened the spreadsheet and ran the query, and the results came back as expected, no missing values.

[Mario Colombo]

Hi,

The test sheet cells were still formatted as text. I switched the cells that contain numbers back to a number format (currency) and you can see that cells that contain text now do not show up anymore.

All the best

Mario

[asgallant]

The spreadsheet is probably doing some type detection on each column, and in the numeric columns, it is only returning data when there is a numeric value.  As far as I am aware, there is no way to override that behavior and force it to return a cell in the format {"v":null,"f":"text value"}.

[jmarcus]

Hi Mario and asgallant,

I'm running into the same exact problem with one of my spreadsheets and before trying to reinvent the wheel, figured I would check in to see if you either of you had come up with a solution since your last post(s). 

Thanks!

Jessica

[asgallant]

I have not found a work-around for this.  There probably isn't anything you can do on the javascript end of things to fix it.  On the spreadsheet end, if you can force the column to use text-values instead of numbers, that would probably solve the problem.

[jmarcus]

Thanks! I'll give that a try. 

[Mario Colombo]

Hi, yes, this is also what I am doing now.

I am also organising my table queries so they don't contain multiple types or even <empty> per column.

All the best

Mario