Using Spreadsheet Data source for Timeline charts

[Matt Lightbourn]

Hi,

I have recently got into the script to create a timeline and obviously need to link this up to a data source.  So I cobbled two scripts together which I believe would work and, although loading the html file doesn't error, it just doesn't do anything, please help. I want to do a Timeline which has B in the timeline element, C is the row on the chart (name of person), D is start date and E is end date. It's just a spreadsheet table and I've done an SQL statement. Let me know if there are any glaring error you can see - I cobbled from Google playground adn from the visualisation chart pages

<script type="text/javascript" src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization',

       'version':'1','packages':['timeline']}]}"></script>

<script type="text/javascript">

google.setOnLoadCallback(drawChart);

function drawChart() {

  var container = document.getElementById('example3.1');

  var chart = new google.visualization.Timeline(container);

  // http://spreadsheets.google.com/ccc?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c

  var query = new google.visualization.Query(

      'http://spreadsheets.google.com/tq?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c&pub=1');

  // Apply query language.

  // B is Client Name, C is Consultant Name, D is start date, E is end date, F is status

  query.setQuery('SELECT B,C,D,E ORDER BY B');

  // Send the query with a callback function.

  query.send(handleQueryResponse);

}

function handleQueryResponse(response) {

  if (response.isError()) {

    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

    return;

  }

  var data = response.getDataTable();

  chart.draw(data);

}

</script>

<div id="example3.1" style="width: 1000px; height: 200px;"></div>

[asgallant]

You likely have a scope issue.  Your handleQueryResponse function is referencing the chart variable that was created in drawChart, but since drawChart and handleQueryFunction are both in the global scope, handleQueryFunction can't access drawChart's internal variables.  Move handleQueryResponse inside drawChart and it should work:

function drawChart() {
    var container = document.getElementById('example3.1');
    var chart = new google.visualization.Timeline(container);
   

    function handleQueryResponse(response) {
        if (response.isError()) {
            alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
            return;
        }
        var data = response.getDataTable();
        chart.draw(data);
    }
   
    // http://spreadsheets.google.com/ccc?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c
    var query = new google.visualization.Query('http://spreadsheets.google.com/tq?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c&pub=1');
    // Apply query language.
    // B is Client Name, C is Consultant Name, D is start date, E is end date, F is status
    query.setQuery('SELECT B,C,D,E ORDER BY B');
    // Send the query with a callback function.
    query.send(handleQueryResponse);
}

[Matt Lightbourn]

Thanks for your reply, I copied and pasted the script contained in your reply and it resulted in the below message.  I will work through and work out what happened :) Thanks, Matt

function drawChart() { var container = document.getElementById('example3.1'); var chart = new google.visualization.Timeline(container); function handleQueryResponse(response) { if (response.isError()) { alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()); return; } var data = response.getDataTable(); chart.draw(data); } // http://spreadsheets.google.com/ccc?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c var query = new google.visualization.Query('http://spreadsheets.google.com/tq?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c&pub=1'); // Apply query language. // B is Client Name, C is Consultant Name, D is start date, E is end date, F is status query.setQuery('SELECT B,C,D,E ORDER BY B'); // Send the query with a callback function. query.send(handleQueryResponse); }

[asgallant]

Where did you see that?

[Matt Lightbourn]

in the browser

[Matt Lightbourn]

Here's my latest version of the script - it just doesn't get any better - it still doesn't do anything at all :(

<script type = ”text / javascript”src = ”https: //www.google.com/isapi?autoload={‘modules’:[{‘name’:’visualisation’,

        ‘version’:’1’,’packages’:[‘timeline’]}]}” ></script>

<script type=”text/javascript”>

Google.setOnLoadCallback(drawchart);

function drawChart() {

    // Set Data Source

    var query = new google.visualization.Query(‘https://docs.google.com/spreadsheet/ccc?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c&usp=sharing#gid=0’);

    // Set Query for Data Source

    query.setQuery(‘SELECT B, C, D, E‘);

    // Send the query with callback function

    query.send(handleQueryResponse);

    }

    // Handle Query errors

        function handleQueryResponse(response) {

            if (response.isError()) {

                alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

                return;

            }

            // Draw Chart

            var data = response.getDataTable();

            var container = document.getElementById(‘schedule’);

            var chart = new google.visualization.Timeline(container));

    chart.draw(data);

}

</script>

<div id=”schedule” style=”width: 1000px; height: 200px;”></div>

[asgallant]

Tackling things in order:

1) Google.setOnLoadCallback should not have a capital "g" in "google": google.setOnLoadCallback

2) many of your quotes are not really quotes (this usually is caused by editing code in a MS Office product, which converts ' into ’ - a completely different character that javascript (and most other programming languages) does not recognize as a quote

3) you have an extra parenthesis at the end of the line var chart = new google.visualization.Timeline(container));

4) your spreadsheet URL should reference the tq service from Google docs, not the ccc service: 'https://docs.google.com/a/redvespa.com/spreadsheet/tq?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c&usp=drive_web#gid=0'

5) in your spreadsheet, your dates are actually strings.  You either need to convert the strings to dates in your spreadsheet or use a DataView to convert them to Date objects in javascript, which you can do like this:

function handleQueryResponse(response) {

    // Handle Query errors

    if (response.isError()) {

        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

        return;

    }

    // Draw Chart

    var data = response.getDataTable();

    

    var view = new google.visualization.DataView(data);

    view.setColumns([0, 1, {

        type: 'date',

        label: data.getColumnLabel(2),

        calc: function (dt, row) {

            var date = dt.getValue(row, 2);

            if (date == '' || date == null) {

                return null;

            }

            else {

                var dateArr = date.split('/');

                var day = dateArr[0];

                var month = dateArr[1] - 1; // convert to javascript's 0-indexed months

                var year = dateArr[2];

                return new Date(year, month, day);

            }

        }

    }, {

        type: 'date',

        label: data.getColumnLabel(3),

        calc: function (dt, row) {

            var date = dt.getValue(row, 3);

            if (date == '' || date == null) {

                return null;

            }

            else {

                var dateArr = date.split('/');

                var day = dateArr[0];

                var month = dateArr[1] - 1; // convert to javascript's 0-indexed months

                var year = dateArr[2];

                return new Date(year, month, day);

            }

        }

    }]);

    var container = document.getElementById('schedule');

    var chart = new google.visualization.Timeline(container);

    chart.draw(view);

}

6) some of your rows are missing dates.  You need to either add dates to those rows or put restrictions on the query to remove rows where the dates are null.  You can restrict to non-null dates like this:

query.setQuery('SELECT B,C,D,E WHERE D is not null AND E is not null and D != "" AND E != ""');

I made a working jsfiddle based on your code that you can work from: http://jsfiddle.net/asgallant/UUmQh/

On Tuesday, November 26, 2013 5:56:55 PM UTC-5, Matt Lightbourn wrote:

Here's a tidied up version of my script - which of course does nothing at all.  I want to step through and find out what it does do and see where it fails - a result which shows nothing to me means no error just, it has abandoned displaying its results. Very odd:

< script type = ”text / javascript”src = ”https: //www.google.com/isapi?autoload={‘modules’:[{‘name’:’visualisation’,

‘version’: ’1’,

’packages’: [‘timeline’]

}]

}” > < /script>

<script type=”text/javascript” >

Google.setOnLoadCallback(drawchart);

function drawChart() {

    // Set Data Source

    var query = new google.visualization.Query(‘https: //docs.google.com/a/redvespa.com/spreadsheet/ccc?key=0Aq4kEViSS7iEdGk1QjdIYmFxSlBDbzg4aHFJM0pzM3c&usp=drive_web#gid=0’);

    // Set Query for Data Source

    query.setQuery(‘SELECT B, C, D, E‘);

    // Send the query with callback function

    query.send(handleQueryResponse);

    }

    // Handle Query errors

        function handleQueryResponse(response) {

            if (response.isError()) {

                alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

                return;

            }

            // Draw Chart

            var data = response.getDataTable();

            var container = document.getElementById(‘schedule’);

            var chart = new google.visualization.Timeline(container));

    chart.draw(data);

} < /script>

On Tuesday, 26 November 2013 17:26:25 UTC+13, Matt Lightbourn wrote:

[Matt Lightbourn]

dude, that is absolutely fantastic! thank you so much for helping with this, awesome! I have digested every bit of it, it's amazing how just a few mistakes can make all the difference to it working or not, like capital G :)  I'm still learning this stuff, getting my confidence up doing this.

Interestingly, I see that the SELECT statement had B,C,D,E which meant rows by customer so have now swapped to C,B,D,E to get rows by consultant.  Now that has made me look for a  category picker and so, I will probably work with adding it using something like this thing below, only just thought of it.

// Define a category picker control for the Gender column
  var categoryPicker = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'control2',
    'options': {
      'filterColumnLabel': 'Transpose View',
      'ui': {
      'labelStacking': 'vertical',
        'allowTyping': false,
        'allowMultiple': false
      }
    }
  });

bind(categoryPicker, bla....

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/gzA99pXx__A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

--
Matt Lightbourn
+64 (0)27 506 2874

Sent from my Dog

[Matt Lightbourn]

Hi again,

I tried including a table using the same data (http://jsfiddle.net/asgallant/UUmQh/) and it plays fine in Google Code Playground but not anywhere else.  Also, wondering how you get this code into Google Site as HTML box doesn't like it and neither does Google Gadget Editor as it returns

Error parsing module spec:
line 11, column 155

      // Set Query for Data Source

XML parse error:
EntityRef: expecting ';'

At the moment, the script I have is saved as an HTML file which displays the gant chart perfectly but not the other bits, I'm sure it's something silly.  Here's the bits I added which were different to yours below.  We had the chart.draw already, I added the table.draw and an additional google.load to bring it back. Thanks, Matt

    var container = document.getElementById('schedule');
    var chart = new google.visualization.Timeline(container);
    chart.draw(view);

    var table = new google.visualization.Table(document.getElementById('table'));
    table.draw(view, null);
}

google.load('visualization', '1', {packages:['timeline'], callback: drawChart});
google.load('visualization', '1', {packages:['table'], callback: drawchart});
</script>
<div id="schedule" style="width: 1000px; height: 600px;"></div>
<div id="table" style="width: 700px; height: 600px;"></div>

[asgallant]

You only want to call google.load once, but you can load multiple packages with one call, like this:

google.load('visualization', '1', {packages:['timeline', 'table'], callback: drawChart});

To transfer the jsfiddle code, you need to add a script tag to your HTML, before the chart code:

<script type='text/javascript' src="http://www.google.com/jsapi"></script>

jsfiddle loads that via the "Resources" option, which is why you don't see the script tag.

As far as using the API on Google Sites, I can't help you, as I've never used them.  There should be help available for getting pages up and running on GS.

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

--
Matt Lightbourn
+64 (0)27 506 2874

Sent from my Dog

[Matt Lightbourn]

yet again, a star! thank you very much!

To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

[Julius Müller]

Hi,

I am new to JavaScript.

I tried to use your code (http://jsfiddle.net/UUmQh) as a baseline for what I am trying to do (http://jsfiddle.net/UUmQh/25/)

But it seems not to work.

I am trying to get this spreadsheet into a Timeline Chart with the twist that each header row is a timeslot

https://docs.google.com/spreadsheets/d/1TzZNxvzOaLIayLhpdyZqzX0sJslJRnCnygugWj_-400/edit#gid=0

[Julius Müller]

I mean http://jsfiddle.net/UUmQh/26/ or http://jsfiddle.net/qwsazvdr/

[laks...@asod.in]

This is my coding. But i can't get any ouput. Is the snippet correct??

<html>

  <head>

    <!--Load the AJAX API-->

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

    <script type="text/javascript">

    function drawSheetName() {

var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);

query.send(handleQueryResponse);

    }

    function handleQueryResponse(response) {

      if (response.isError()) {

        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

        return;

      }

      var data = response.getDataTable();

      var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));

      chart.draw(data, { height: 400 });

    }

    </script>

  </head>

  <body>

    <!--Div that will hold the pie chart-->

    <div id="chart_div"></div>

  </body>

</html>