Do diff charts work with google sheets as a source?

84 views
Skip to first unread message

Uwe Dornbusch

unread,
Nov 21, 2017, 1:54:17 PM11/21/17
to Google Visualization API
I have successfully created a diff scatter chart following the examples in the guides from data which is hardcoded into the function (as in the google guides). However, replacing the data with calls to google sheets will only draw the two data charts but not the diff chart.
The code is below (apologies, i am not a programmer so this might be a bit more messy than usual) and the sheets is open to test.I have tried it with separate draw functions (some of the commented out lines) or with all in one function which does not make a difference in the hardcoded data but also has not impact here.

I am wondering what i have missed or whether diff charts simply do not work with external data sources.

Many thanks, Uwe

<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      //This is written in 'classic' style as most options are not yet implemented in the 'material' style 
      //Load the Visualization API and the corechart package.
      google.charts.load('current', {'packages':['corechart', 'table'], 'language': 'de'});// the language setting to DE is a simple way to get at 24 hour time format for the x axis :-)
       // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChartoriginal);
      //google.charts.setOnLoadCallback(drawChartnew);
     //google.charts.setOnLoadCallback(drawChartDiff);

function drawChartoriginal() {
//the next line is the link to the Google Sheets which is the following URL and which needs to be subtly changed
//in the format at the end of the link: https://docs.google.com/spreadsheets/d/1xzoNFEuwHilog2zNjuKuilUXbHKCFWNHGZsS2zwEojs/edit#gid=678810481.
    var queryoriginal = new google.visualization.Query('http://docs.google.com/spreadsheet/tq?key=135RNRlBakQ5aXqsABZpmLE9dVWjt9tZiWdCwD3e9RS4&gid=161902447');
    queryoriginal.setQuery('SELECT A, B');//this selects the columns from the spreadsheet
    queryoriginal.send(function (responseoriginal) {
        if (responseoriginal.isError()) {
            alert('Error in query: ' + responseoriginal.getMessage() + ' ' + responseoriginal.getDetailedMessage());
            return;
            }
        var dataoriginal = responseoriginal.getDataTable();
        var chartoriginal = new google.visualization.ScatterChart(document.getElementById('chartoriginal_div'));
        chartoriginal.draw(dataoriginal);
            });
   
        //function drawChartnew() {
        var querynew = new google.visualization.Query('http://docs.google.com/spreadsheet/tq?key=135RNRlBakQ5aXqsABZpmLE9dVWjt9tZiWdCwD3e9RS4&gid=161902447');
    querynew.setQuery('SELECT A, C');//this selects the columns from the spreadsheet
    querynew.send(function (responsenew) {
        if (responsenew.isError()) {
            alert('Error in query: ' + responsenew.getMessage() + ' ' + responsenew.getDetailedMessage());
            return;
        }
       
        var datanew = responsenew.getDataTable();
        var chartnew = new google.visualization.ScatterChart(document.getElementById('chartnew_div'));
        
    chartnew.draw(datanew);
            });
            
   
   
    //function drawChartDiff() {
    var chartDiff = new google.visualization.ScatterChart(document.getElementById('chartdiff_div'));
    var diffData = chartDiff.computeDiff(dataoriginal, datanew);
    chartDiff.draw(diffData);
    //};
    };
 </script>
  </head>
  <body>
    <!--Div that will hold the pie chart-->
  
   <div id="chartoriginal_div"></div>
  
   Data:
   <div id="chartnew_div"></div>
   differencegraph
   <div id="chartdiff_div"></div>
  
  </body>
</html>
 

Daniel LaLiberte

unread,
Nov 21, 2017, 2:21:06 PM11/21/17
to Google Visualization API
Since your two requests for data come back asynchronously, and not necessarily in the order you expect, you have to do a bit of work to keep track of when each dataset comes back, and only proceed to draw your diff chart when both are available.  Something like this:

// global vars to keep track of which data has come back.
var oldData = null;
var newData = null;
      
google.charts.setOnLoadCallback(drawChartoriginal);
google.charts.setOnLoadCallback(drawChartnew);

function drawChartOriginal() {

  ... 
  oldData = dataoriginal;
  if (newData) drawChartDiff();
}

function drawChartNew() {

  ...
  newData = datanew;
  if (oldData) drawChartDiff();
}



--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/4cca24e9-0c85-461a-b260-ab61cdc65df8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--

Uwe Dornbusch

unread,
Nov 21, 2017, 4:51:40 PM11/21/17
to Google Visualization API
Thanks Daniel!
Ok, I get that the data may not come at the same time (as a non-programmer I would have thought that at least once the function has completed the data would be 'there' :-) ).
Having put your suggestions into the code, the diff chart still does not draw.
Does this suggest the data only 'arrives' after the functions have run? Refrechi8ng the html page certainly takes a few seconds for the first two graphs to appear but whether this delay is in getting the data (the spreadsheet is updating itself) or in drawing it i don't know.
Is is possible to delay execution of the darwChartDiff()?

Thanks again!



Daniel LaLiberte

unread,
Nov 21, 2017, 4:57:40 PM11/21/17
to Google Visualization API
Certainly you can delay the drawing of the diff chart further, just by wrapping the draw call with a setTimeout.  That shouldn't be necessary, however, if you are waiting for both the old and new data to arrive before proceeding to draw the diff chart.   Can you share your latest code?

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/77b0b63e-76a3-4ce1-9e96-d3f1bdcf8487%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Uwe Dornbusch

unread,
Nov 21, 2017, 5:13:35 PM11/21/17
to Google Visualization API
Here goes.


<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      //This is written in 'classic' style as most options are not yet implemented in the 'material' style 
      //Load the Visualization API and the corechart package.
      google.charts.load('current', {'packages':['corechart', 'table'], 'language': 'de'});// the language setting to DE is a simple way to get at 24 hour time format for the x axis :-)
       //DanielLaLiberte

       // global vars to keep track of which data has come back.
          var oldData = null;
          var newData = null;
//DanielLaLiberte      
// Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChartoriginal);
      google.charts.setOnLoadCallback(drawChartnew);
     //google.charts.setOnLoadCallback(drawChartDiff);

function drawChartoriginal() {
//the next line is the link to the Google Sheets which is the following URL and which needs to be subtly changed
//in the format at the end of the link: https://docs.google.com/spreadsheets/d/1xzoNFEuwHilog2zNjuKuilUXbHKCFWNHGZsS2zwEojs/edit#gid=678810481.
   
    var queryoriginal = new google.visualization.Query('http://docs.google.com/spreadsheet/tq?key=135RNRlBakQ5aXqsABZpmLE9dVWjt9tZiWdCwD3e9RS4&gid=161902447');
    queryoriginal.setQuery('SELECT A, B');//this selects the columns from the spreadsheet
    queryoriginal.send(function (responseoriginal) {
        if (responseoriginal.isError()) {
            alert('Error in query: ' + responseoriginal.getMessage() + ' ' + responseoriginal.getDetailedMessage());
            return;
            }
        var dataoriginal = responseoriginal.getDataTable();
        var chartoriginal = new google.visualization.ScatterChart(document.getElementById('chartoriginal_div'));
        chartoriginal.draw(dataoriginal);
           
            //DanielLaLiberte

            oldData = dataoriginal;
            if (newData) drawChartDiff();
            //DanielLaLiberte
           
            })};

   
        function drawChartnew() {
        var querynew = new google.visualization.Query('http://docs.google.com/spreadsheet/tq?key=135RNRlBakQ5aXqsABZpmLE9dVWjt9tZiWdCwD3e9RS4&gid=161902447');
    querynew.setQuery('SELECT A, C');//this selects the columns from the spreadsheet
    querynew.send(function (responsenew) {
        if (responsenew.isError()) {
            alert('Error in query: ' + responsenew.getMessage() + ' ' + responsenew.getDetailedMessage());
            return;
        }
       
        var datanew = responsenew.getDataTable();
        var chartnew = new google.visualization.ScatterChart(document.getElementById('chartnew_div'));
       
    chartnew.draw(datanew);
   
            });
            //DanielLaLiberte
             newData = datanew;
             if (oldData && newData) drawChartDiff();
           //DanielLaLiberte

        }
   
    function drawChartDiff() {
    var chartDiff = new google.visualization.ScatterChart(document.getElementById('chartdiff_div'));
    var diffData = chartDiff.computeDiff(dataoriginal, datanew);
    chartDiff.draw(diffData);
    //};
    };
 </script>
  </head>
  <body>
    <!--Div that will hold the pie chart-->
  
   <div id="chartoriginal_div"></div>
   <div id="table_div"></div>

Daniel LaLiberte

unread,
Nov 21, 2017, 9:55:49 PM11/21/17
to Google Visualization API
Your indenting is a bit screwy.  Would be good to move the code into a js file, and use an editor that knows javascript to format it better for you.

In any case, it looks like after you draw the new data chart, you end that function before setting up the newData:

    chartnew.draw(datanew);
    
            });
        newData = datanew;

but you need that newData assignment to be inside the function.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/0bbc0a9e-519f-4919-94dd-6dd855082c1d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Uwe Dornbusch

unread,
Nov 22, 2017, 7:01:47 AM11/22/17
to Google Visualization API
Apologies again for the code - this started out just modifying something copied and pasted and I didn't think this would be a bit longer drawn out :-)
Below the code which I think addresses your point, ie
"newData = datanew;
      if (oldData) drawChartDiff();"
now sits above the chartnew.draw(newData) / the chartoriginal.draw(oldData).
The result is curious in that it takes now about 5 seconds but then only draws the second graph.?
Thanks again, Uwe


<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      //This is written in 'classic' style as most options are not yet implemented in the 'material' style
      //Load the Visualization API and the corechart package.
      google.charts.load('current', {'packages':['corechart', 'table'], 'language': 'de'});// the language setting to DE is a simple way to get at 24 hour time format for the x axis :-)
       //DanielLaLiberte

       // global vars to keep track of which data has come back.
          var oldData = null;
          var newData = null;
//DanielLaLiberte     
// Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChartoriginal);
      google.charts.setOnLoadCallback(drawChartnew);

     google.charts.setOnLoadCallback(drawChartDiff);

function drawChartoriginal() {
//the next line is the link to the Google Sheets which is the following URL and which needs to be subtly changed
//in the format at the end of the link: https://docs.google.com/spreadsheets/d/1xzoNFEuwHilog2zNjuKuilUXbHKCFWNHGZsS2zwEojs/edit#gid=678810481.
  
    var queryoriginal = new google.visualization.Query('http://docs.google.com/spreadsheet/tq?key=135RNRlBakQ5aXqsABZpmLE9dVWjt9tZiWdCwD3e9RS4&gid=161902447');
    queryoriginal.setQuery('SELECT A, B');//this selects the columns from the spreadsheet
    queryoriginal.send(function (responseoriginal) {
        if (responseoriginal.isError()) {
            alert('Error in query: ' + responseoriginal.getMessage() + ' ' + responseoriginal.getDetailedMessage());
            return;
        }
        var dataoriginal = responseoriginal.getDataTable();
        var chartoriginal = new google.visualization.ScatterChart(document.getElementById('chartoriginal_div'));
        //DanielLaLiberte
            oldData = dataoriginal;
            if (newData) drawChartDiff();
            //DanielLaLiberte
        chartoriginal.draw(oldData);
        });

           
    var querynew = new google.visualization.Query('http://docs.google.com/spreadsheet/tq?key=135RNRlBakQ5aXqsABZpmLE9dVWjt9tZiWdCwD3e9RS4&gid=161902447');
    querynew.setQuery('SELECT A, C');//this selects the columns from the spreadsheet
    querynew.send(function (responsenew) {
        if (responsenew.isError()) {
            alert('Error in query: ' + responsenew.getMessage() + ' ' + responsenew.getDetailedMessage());
            return;
        }
        var datanew = responsenew.getDataTable();
        var chartnew = new google.visualization.ScatterChart(document.getElementById('chartnew_div'));
       //DanielLaLiberte
             newData = datanew;
             if (oldData) drawChartDiff();
            //DanielLaLiberte
        chartnew.draw(newData);
    });
};

  
    function drawChartDiff() {
    var chartDiff = new google.visualization.ScatterChart(document.getElementById('chartdiff_div'));
    var diffData = chartDiff.computeDiff(dataoriginal, datanew);
    chartDiff.draw(diffData);
    //};
    };
 </script>
  </head>
  <body>
    <!--Div that will hold the charts-->
  Old data plot
   <div id="chartoriginal_div"></div>
  New Data:

Daniel LaLiberte

unread,
Nov 22, 2017, 11:05:02 AM11/22/17
to Google Visualization API
Here is a working example starting from your code:  https://jsfiddle.net/eb8c1vcb/

* You were calling the drawChartDiff function from the top level again.  It is necessary to wait until both the oldData and newData are available.
* You lost the drawChartnew function.
* Your diff chart needed to reference the oldData and newData in the global vars.


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/82eb0869-bf65-4b42-8208-0e4c813c935c%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Uwe Dornbusch

unread,
Nov 22, 2017, 4:56:52 PM11/22/17
to Google Visualization API
Brilliant, thanks Daniel!!
'Just' my inexperience. The next thing to work out is how this works with dates as google charts does not seem to treat this like e.g. Excel in that there is always the number in the background so the sheet needs to have a number but the axis needs to be the date. Trivial i guess :-)

Thanks again, Uwe
PS: thanks in particular for the fiddle setup as I tried to do this as well and failed :-(.

Daniel LaLiberte

unread,
Nov 22, 2017, 5:01:34 PM11/22/17
to Google Visualization API
Ya, jsfiddle can be tricky at first.  

The main trick fetching data with dates from spreadsheets is that you have to format the dates in the spreadsheet so it knows they are dates and can send the values through as dates.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/4435c723-e089-44ba-81ee-0f3777e65329%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages