Google Visualization Query & Sheets -- not returning proper rows for simple spreadsheet

Question

I must be misunderstanding their documentation, but this seems so simple and straightforward...

I have here a very simple Sheets document: https://docs.google.com/spreadsheets/d/1I8PQWcldqZmCpctFPyXm5UpPgjeRYpX8dtkVxYgcy08/edit?usp=sharing

Look at the document. How many rows are there? It looks like there are 5 rows, right?

Let's write some JavaScript code using the Visualization API...

function displayData(response) {
    var data = response.getDataTable();
    console.log(data.getNumberOfRows());
}

var opts = {sendMethod: 'auto'};

var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1I8PQWcldqZmCpctFPyXm5UpPgjeRYpX8dtkVxYgcy08/edit?usp=sharing', opts);

query.setQuery('select *');
query.send(displayData);

Super simple! Grab all data from the spreadsheet, and print the number of rows.

But the observed result is 1 rows. Why 1? That doesn't make sense.

When you dig into the DataTable, you see that it's concatenating cells that should be separate together, e.g. data.Ff[0].label === "foo one uno".

Why is that? Is this a bug, or am I misunderstanding something about this API?

Answer 1

It's worth noting that QUERY doesn't return columns with mixed data types:

In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

Here's a workaround by editing the URL of your datasource:

google.load('visualization', '1', {
    callback: function () {

        var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1I8PQWcldqZmCpctFPyXm5UpPgjeRYpX8dtkVxYgcy08/gviz/tq?tqx=out:html&tq?gid=0&headers=0&range=A1:C');
        query.send(displayData);
    }
});

function displayData(response) {

    numRows = response.getDataTable().getNumberOfRows();

    document.getElementById('data').innerHTML = numRows;
}

http://jsfiddle.net/gregpearl/zs98r3v8/