Configuring DataView from two sheets in the same Google Spreadsheet file for Google Charts DataSource

Question

I am finding it hard to get this answer. I am looking into creating a Google Chart Dashboard for my client and have a Year, Month, Day filter however, their records for 1 year are over 4000 rows, I thought of adding more sheets in the files and put each year's worth of data in a separate sheet.

How would I go about creating a view in the script that grabs all the information from all sheets and make them in a single view so I can use it as a Datasource for my Google Chart Dashboard.

I tried adding 20,000 rows in one sheet and it would return a server error.

any help will be appreciated!

asgallant asgallant · Accepted Answer · 2013-07-23T19:00:46

The Query API can only select data from one sheet at a time, so you will have to make multiple queries and then join all of the data together into a single DataTable when the queries return:

function drawChart () {
    // use "gid" parameter to select the sheet number
    // or the "sheet" parameter to select the sheet by name
    var query1 = new google.visualization.Query(spreadsheetUrl + '&gid=0'); // sheet 1
    var query2 = new google.visualization.Query(spreadsheetUrl + '&gid=1'); // sheet 2
    var query3 = new google.visualization.Query(spreadsheetUrl + '&gid=2'); // sheet 3
    // etc...

    // set up each query
    // eg: query1.setQuery('select a, b');

    var queriesReady = [false, false, false]; // array should have a number of elements equal to the number of queries
    var dataTables = [];

    // readyCheck function takes the response returned by the query and the index in the "queriesReady" array to set to true
    // each query should use a unique index
    function readyCheck (response, index) {
        if (response.isError()) {
            alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
            return;
        }
        data[index] = response.getDataTable();
        queriesReady[index] = true;

        var allDone = true;
        for (var i = 0; i < queriesReady.length; i++) {
            allDone = allDone & queriesReady[i];
        }

        if (allDone) {
            // merge DataTables and draw chart
        }
    }

    query1.send(function (response) {
        readyCheck(response, 0);
    });
    query2.send(function (response) {
        readyCheck(response, 1);
    });
    query3.send(function (response) {
        readyCheck(response, 2);
    });
}

Configuring DataView from two sheets in the same Google Spreadsheet file for Google Charts DataSource

2 Answers